Command Object Parameters

A more interesting use for the Command object is shown in the next example, in which the text of the SQL command has been modified to make it parameterized. This makes it possible to reuse the command, passing in a different value for the parameter each time. Because the Prepared property on the Command object is set equal 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. Thus, commands should be prepared only if they will be used more than once.

'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 since 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
        
    ' ...then 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

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.