Passing parameters from ADODC to Stored Procedure? 2

[Sheffield]

Greetings,

My Problem: I don't know all of the syntax involved in passing a parameter (TextBox.Text) from an ADODC to a Stored Procedure. Here is what I have so far:

Private Sub cmdSearch_Click()
    With Adodc1
        .CommandType = adCmdStoredProc
        .RecordSource = "sp_myData;1"
        .Refresh
    End With
End Sub

Can anyone fill in what I'm missing? Many thanks,

 

[BigDaddy]

The following is how I used parameters collected from text boxes and passed into an ADODB.Parameters collection.

Option Explicit

Dim strCnn As String
Dim adoConn As ADODB.Connection
Dim adoParams As ADODB.Parameters
Dim adoCommand As ADODB.Command


Private Sub cmdGetVoterDist_Click()
    Dim adoRecSet As ADODB.Recordset

    Set adoRecSet = GetVoterDist(txtGetVoterParam(0), _
                                 txtGetVoterParam(1), _
                                 txtGetVoterParam(2), _
                                 txtGetVoterParam(3), _
                                 txtGetVoterParam(4))
    If Not adoRecSet Is Nothing Then
        If adoRecSet.RecordCount > 0 Then
            txtWard = adoRecSet.Fields("Ward").Value
            txtPrecinct = adoRecSet.Fields("Precinct").Value
            txtSubPrecinct = adoRecSet.Fields("Subprecinct").Value
            txtStateHouse = adoRecSet.Fields("State_House").Value
            txtStateSenate = adoRecSet.Fields("State_Senate").Value
        Else
            txtWard = ""
            txtPrecinct = ""
            txtSubPrecinct = ""
            txtStateHouse = ""
            txtStateSenate = ""
        End If
    Else
        txtWard = ""
        txtPrecinct = ""
        txtSubPrecinct = ""
        txtStateHouse = ""
        txtStateSenate = ""
    End If
    
    Set adoRecSet = Nothing
End Sub


Public Function GetVoterDist(ByVal StNumber As Integer, _
                             ByVal StDirPfx As String, _
                             ByVal StName As String, _
                             ByVal StNameSfx As String, _
                             ByVal StDirSfx) As ADODB.Recordset
On Error GoTo Err
  Dim adoVoterDist As ADODB.Recordset
  Dim adoCmd As ADODB.Command
  Dim adoParam As ADODB.Parameter
  Set adoCmd = New ADODB.Command
  With adoCmd
    .CommandType = adCmdStoredProc
    .CommandText = "GetVoterDistricts"
    
    'Build parameters collection
    Set adoParam = .CreateParameter(Name:="RETURN_VALUE", _
                    Type:=adInteger, _
                    Direction:=adParamReturnValue)
    .Parameters.Append adoParam
    
    Set adoParam = .CreateParameter(Name:="@Street_Number", _
                    Type:=adInteger, _
                    Direction:=adParamInput, _
                    Value:=StNumber)
    .Parameters.Append adoParam
    
    Set adoParam = .CreateParameter(Name:="@Street_Direction_Prefix", _
                    Type:=adVarChar, _
                    Direction:=adParamInput, _
                    Size:=2, _
                    Value:=StDirPfx)
    .Parameters.Append adoParam
    
    Set adoParam = .CreateParameter(Name:="@Street_Name", _
                    Type:=adVarChar, _
                    Direction:=adParamInput, _
                    Size:=30, _
                    Value:=StName)
    .Parameters.Append adoParam
  
  
    Set adoParam = .CreateParameter(Name:="@Street_Name_Suffix", _
                    Type:=adVarChar, _
                    Direction:=adParamInput, _
                    Size:=4, _
                    Value:=StNameSfx)
    .Parameters.Append adoParam
    
    Set adoParam = .CreateParameter(Name:="@Street_Direction_Suffix", _
                    Type:=adVarChar, _
                    Direction:=adParamInput, _
                    Size:=2, _
                    Value:=StDirSfx)
    .Parameters.Append adoParam
    
  End With
  Set adoVoterDist = New ADODB.Recordset
  adoVoterDist.CursorLocation = adUseClient
      
  ' Define a command object for a stored procedure.
  Set adoConn = New ADODB.Connection
  strCnn = "Provider=SQLOLEDB.1;=;" & _
           "Persist Security Info=True;" & _
           "User ID=;" & _
           "Password=;" & _
           "Initial Catalog=AAUIS;=;" & _
           "Data Source=SQL;=;" & _
           "Connect Timeout=15"
  adoConn.Open strCnn
  
  adoCmd.ActiveConnection = adoConn
  Call adoVoterDist.Open(Source:=adoCmd)
  
  Set GetVoterDist = adoVoterDist
  Exit Function
Err:
  MsgBox Err.Description & vbCrLf & Err.Number
End Function

 

[Sheffield]

BigDaddy,

Much to digest, but I greatly appreciate your help

 

[sheykc]

Try this:

.RecordSource = "sp_myData " & textbox.text

 

[BigDaddy]

Oops, I didn't see that he was using a data control. Good catch. In my zeal to help, I forgot to keep it simple.

 

[Sheffield]

The code below (and above)...

.RecordSource = "sp_myData" & Textbox.text

...isn't working and gives me the following error dialog box:

[Microsoft][ODBC SQL Server Driver]Syntax Error or Access Violation

Any thoughts as to why this is the case? Or, better yet, is it really possible to pass a parameter from an ADO Data Control to a SQL Server Stored Procedure?

Also, can someone tell me what the "1" is for? (example at the top) Its found at the end of each stored procedure in the ADO Data Control's RecordSource property.

Essentially, I'm attempting to populate a DataGrid and would like to be able to alter the data within the grid. Perhaps I'm not bright enough, but although I can populate the Grid with a DataEnvironment Designer, I can't alter the data once its there. Hence, my attempts to use the ADO Data Control.Any ideas?

Your help is greatly appreciated

 

[sheykc]

The problem I see is that you need a space between the sp_myData and the end quotes--like this:

.RecordSource = "sp_myData " & Textbox.text


If you were to display the .RecordSource in the watch window the way you had it before it would have been trying to execute the stored procedure like this:

sp_myDataTextboxValue

When what you need is

sp_myData TextboxValue

Make sense?

Shey

 

[Sheffield]

What you are saying does make sense, but it doesn't seem to work...at least not in my program.

Here is the exact code I'm trying to use (unsuccessfully). I don't know the purpose of the '1', below, but it seems to be a default value within the control. The program fails when the 1 is removed.

Private Sub cmdSearch_Click()
With Adodc1
.CommandType = adCmdStoredProc
.RecordSource = "sp_myData;1 " & Text1.text
.Refresh
End With
End Sub

My stored procedure is looking for an INT, but the Text1.text property simply gets added to the "sp_myData" string. Is there a work-around?

Still confused....

 

[sheykc]

I created a quick form with a textbox, search button, adodc control and a DataGrid control that handles OLE DB.

ADODC Control property changes:
CommandType property = 4 - adCmdStoredProc
ConnectionString = database driver info
Recordsource = hnsp_SheyTemp 11521 (I just put in a default integer value, that I knew existed in the table).

The code in my search button is:

Private Sub cmdSearch_Click()
With Adodc1
.CommandType = adCmdStoredProc
.RecordSource = "hnsp_SheyTemp " & CInt(Text1.Text)
.Refresh
End With
End Sub

This works fine. I can change the integer value in the textbox to whatever I need, and the grid updates every time.

 

[Sheffield]

Excellent!

Many thanks for all your help