Command Object Parameters

 

The previous topic discussed Creating and Executing a Simple Command. A more interesting use for the Command object is shown in the next example, in which the SQL command has been parameterized. This modification makes it possible to reuse the command, passing in a different value for the parameter each time. Because the Prepared Property property on the Command object is set to true, ADO will require the provider to compile the command specified in CommandText before executing it for the first time. It also will retain the compiled command in memory. This slows the execution of the command slightly the first time it is executed because of the overhead required to prepare it, but results in a performance gain each time the command is called thereafter. Therefore, commands should be prepared only if they will be used more than one time.

'BeginManualParamCmd
    On Error GoTo ErrHandler:

    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objParm1 As New ADODB.Parameter
    Dim objRs As New ADODB.Recordset

    ' Set the CommandText as a parameterized SQL query.
    objCmd.CommandText = "SELECT OrderID, OrderDate, " & _
                         "RequiredDate, ShippedDate " & _
                         "FROM Orders " & _
                         "WHERE CustomerID = ? " & _
                         "ORDER BY OrderID"
    objCmd.CommandType = adCmdText

    ' Prepare command because we will be executing it more than once.
    objCmd.Prepared = True

    ' Create new parameter for CustomerID. Initial value is ALFKI.
    Set objParm1 = objCmd.CreateParameter("CustId", adChar, _
                    adParamInput, 5, "ALFKI")
    objCmd.Parameters.Append objParm1

    ' Connect to the data source.
    Set objConn = GetNewConnection
    objCmd.ActiveConnection = objConn

    ' Execute once and display.
    Set objRs = objCmd.Execute

    Debug.Print objParm1.Value
    Do While Not objRs.EOF
        Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
                    objRs(2) & vbTab & objRs(3)
        objRs.MoveNext
    Loop

    ' .Set new param value, re-execute command, and display.
    objCmd("CustId") = "CACTU"
    Set objRs = objCmd.Execute

    Debug.Print objParm1.Value
    Do While Not objRs.EOF
        Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
                    objRs(2) & vbTab & objRs(3)
        objRs.MoveNext
    Loop

    'clean up
    objRs.Close
    objConn.Close
    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Set objParm1 = Nothing
    Exit Sub

ErrHandler:
    'clean up
    If objRs.State = adStateOpen Then
        objRs.Close
    End If

    If objConn.State = adStateOpen Then
        objConn.Close
    End If

    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Set objParm1 = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
'EndManualParamCmd


'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
    Dim oCn As New ADODB.Connection
    Dim sCnStr As String

    sCnStr = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _
             "Integrated Security='SSPI';Initial Catalog='Northwind';"
    oCn.Open sCnStr

    If oCn.State = adStateOpen Then
        Set GetNewConnection = oCn
    End If

End Function
'EndNewConnection

Not all providers support prepared commands. If the provider does not support command preparation, it might return an error as soon as this property is set to True. If it does not return an error, it ignores the request to prepare the command and sets the Prepared property to false.

Show: