Step 2: Create a Command (ADO Tutorial) Sidebar
Commands can be parameterized. A parameterized command consists of command text that can be modified with a user-specified value each time the command is invoked.
A placeholder ('?') indicates the part of the command text that will be modified. Each placeholder in the command text will be replaced by the value of the corresponding Parameter object in the Parameters collection when the command is executed.
There are two ways to create a Parameter object and append it to the Command object Parameters collection. The first way is to create a Parameter object, set its Name, Type, Direction, Size, and Value properties individually, then append it to the Parameters collection. The second way is to create and set properties of the Parameter object with the Command object CreateParameter method, then append the newly created Parameter object, all in one statement.
Finally, call the Command.Execute or Recordset.Open methods to substitute the parameter for the placeholder and obtain a Recordset. Change the Parameter object Value property to another name. Then, call the Execute method or the Recordset.Close and Open methods again to obtain a new Recordset for another author.
Optimize the performance of the parameterized command with the Prepared property. Note that the Execute method cannot specify the Recordset object CursorType or LockType properties.
Here is a brief example:
Public Sub main()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim strCmd As String
strCmd = "SELECT * FROM Authors WHERE au_lname = ?"
cmd.CommandText = strCmd
cmd.Parameters.Append _
cmd.CreateParameter("last name", adVarChar, adParamInput, 40, "")
cnn.Open "dsn=Pubs;uid=sa;pwd=;"
cmd.ActiveConnection = cnn
cmd.Parameters("last name") = "Ringer"
DisplayRst cmd.Execute
cmd.Parameters("last name") = "Karsen"
DisplayRst cmd.Execute
cnn.Close
End Sub
Private Sub DisplayRst(rst As ADODB.Recordset)
If rst.EOF = True Then
Debug.Print "No recordset returned for Name = '"; _
rst.ActiveCommand.Parameters(0); "'"
End If
While rst.EOF = False
Debug.Print "Name = '"; rst!au_fname; " "; rst!au_lname; "'"
rst.MoveNext
Wend
End Sub
Return to Step 2: Create a Command