setting a parameter to the result of a control

[mshbhwn98]

I have a stored procedure in SQL server 2008 and I currently am executing it from excel. The stored procedure takes 1 parameter which I have set to whatever is in cell C2 say. For example this parameter might be a number. How do I set the parameter to the result of a control?

Many thanks in advance

[Bob Phillips]

What exactly is your question, how to pass a parameter to a stored procedure, or how to pick up a value in C2?

How are you calling the SP?

[mshbhwn98]

Sorry I guess I didn't make it clear enough. I currently have written a macro in vba which calls the stored procedure and uses whatever is in cell c2 as the parameter. This part works fine.

What I don't know how to do is add a control and set the parameter to whatever the control is set to. So if the control is a combobox with options 1,2,3 etc I want the parameter that is passed through the stored procedure to be 2 if i set the control to 2 or 3 if I change it to 3.

Let me know if this is still not clear.

Thank you

[Bob Phillips]

Not totally no.

Do you want the param value if the combobox has 3 items, 2 if it has 2 items, etc., or is it dependent on the value(s) selected.

What sort of combobox?

[mshbhwn98]

Right. Here is my code. I am pretty new to vba which I guess is why we are having problems. You will see below that the parameter for the stored procedure @AnalysisIDs is set in C12. I said C2 in the previous examples because it was just an example. I know I could use data validation on a list in C12 so you can choose from a list but I am trying to learn new things so I would like to know how to, (if possible), add a control; either activeX or form control to do the same thing. This is with the view that I will then take a working example and try using other controls to learn how to incorporate them.

[VBA]Option Explicit
Sub commandbutton1_click()
Application.ScreenUpdating = False

Binder_Pricing_Locations

Application.ScreenUpdating = True
End Sub

Public Function IsEmptyRecordset(rs As Recordset) As Boolean
IsEmptyRecordset = ((rs.BOF = True) And (rs.EOF = True))
End Function

Public Sub Binder_Pricing_Locations()

'Set variables
Dim cmd As ADODB.Command
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim myPath
Dim fld
Dim i As Integer
Dim str As String

On Error GoTo errlbl

'Open database connection
Set conn = New ADODB.Connection
conn.ConnectionString = "DSN=PC_Tool_Coding"
'Here's where the connection is opened.
conn.Open

'This can be very handy to help debug!
Debug.Print conn.ConnectionString

Set rs = New ADODB.Recordset

Set cmd = New ADODB.Command
cmd.CommandText = "Binder_Pricing_Locations"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn

'Set parameters
'AnalysisIDs
Set prm = cmd.CreateParameter("@AnalysisIDs", adVarChar, adParamInput, 200)
cmd.Parameters.Append prm
cmd.Parameters("@AnalysisIDs").Value = Sheet10.Range("C12").Value

'Execute the Stored Procedure
Set rs = cmd.Execute
'Populate the sheet with the data from the recordset
Sheet7.Range("C2").CopyFromRecordset rs

'Cleanup
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

exitlbl:
Debug.Print "Error: " & Err.Number
If Err.Number = 0 Then
MsgBox "Done", vbOKOnly, "All Done."
End If
Exit Sub

'Error box with message
errlbl:
MsgBox "Error #: " & Err.Number & ", Description: " & Err.Description, vbCritical, "Error"
Exit Sub
'Resume exitlbl

End Sub[/VBA]

[mshbhwn98]

i guess all im asking is how do I change this bit

cmd.Parameters("@AnalysisIDs").Value =Sheet10.Range("C12").Value

to

cmd.Parameters("@AnalysisIDs").Value = Whatever is selected in the combobox

[snb]

[VBA]cmd.Parameters("@AnalysisIDs").Value =combobox1.value[/VBA]

[mshbhwn98]

thanks