Passing Parameters to a Named Command
Just as the result of the command is passed out as an out variable of the named command, parameters for a parameterized command can been passed in as in variables to the named command.
The following code example tries to retrieve all the orders placed by the customer whose CustomerID is "ALKFI" from the Northwind database. The value of CustomerID is supplied at the time when the named command is called.
Const DS = "MySqlServer"
Const DB = "Northwind"
Const DP = "SQLOLEDB"
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command
CommandText = "SELECT OrderID, OrderDate, " & _
"RequiredDate, ShippedDate " & _
"FROM Orders " & _
"WHERE CustomerID = ? " & _
"ORDER BY OrderID"
ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & DB & _
";Integrated Security=SSPI;"
' Connect to the data source.
objConn.Open ConnectionString
' Set a named command.
objComm.CommandText = CommandText
objComm.CommandType = adCmdText
objComm.Name = "GetOrdersOf"
Set objComm.ActiveConnection = objConn
' Call the named command, passing a CustomerID value
' as the input parameter.
' "ALFKI" is the required input parameter,
' objRs is the resultant output variable.
objConn.GetOrdersOf "ALKFI", objRs
' Display the result.
Debug.Print "All orders by ALFKI:"
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 objComm = Nothing
Notice that all the input parameters must precede any output variable and the data types of parameters must match or can be converted to those of the corresponding fields. The following statement-
objConn.GetOrdersOf 12345, objRs
-will result in an error of mismatched data types, because the required input parameter is of a String type, not of an Integer type.
The following call-
objConn.GetOrdersOf "12345", objRs
-is valid, but will yield an empty result set because no such records exist in the database.