命令对象参数
上一主题介绍了创建和执行简单命令。 下一个示例展示了 Command 对象更有趣的用法,其中 SQL 命令已参数化。 通过此修改,可以重复使用该命令,每次为参数传入不同的值。 由于 Command 对象上的 Prepared 属性设置为 true,因此 ADO 要求提供程序在首次执行命令 CommandText 中指定的命令之前对其进行编译。 它还会在内存中保留已编译的命令。 在第一次执行命令时,由于准备命令需要开销,会使命令的执行速度略微减慢,但此后每次调用该命令时,性能都会有所提升。 因此,只有当命令将被多次使用时,才应准备它们。
'BeginManualParamCmd
Public Sub ManualParamCmd
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 because 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
' .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
End Sub
'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String
sCnStr = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _
"Integrated Security='SSPI';Initial Catalog='Northwind';"
oCn.Open sCnStr
If oCn.State = adStateOpen Then
Set GetNewConnection = oCn
End If
End Function
'EndNewConnection
并非所有提供程序都支持准备好的命令。 如果提供程序不支持命令准备,只要此属性设置为 True,它就可能返回一个错误。 如果它没有返回错误,表明它忽略准备命令的请求,并将 Prepared 属性设置为 false。