将参数传递给命名命令
正如命令的结果作为命名命令的 out 变量传出一样,参数化命令的参数可以作为 in 变量传入命名命令。
下面的代码示例尝试从 Northwind 数据库检索 CustomerID 为“ALKFI”的客户所下的所有订单。 调用命名命令时提供的 CustomerID 值。
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
请注意,所有输入参数必须位于任何输出变量之前,并且参数的数据类型必须匹配或者可转换为相应字段的数据类型。 以下语句
objConn.GetOrdersOf 12345, objRs
将导致数据类型不匹配错误,因为所需的输入参数是字符串类型,而不是整数类型。
以下调用
objConn.GetOrdersOf "12345", objRs
是有效的,但会生成空结果集,因为数据库中不存在此类记录。