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.