ActiveCommand 属性示例 (VB)
此示例演示 ActiveCommand 属性。
为子例程提供了一个 Recordset 对象，该对象的 ActiveCommand 属性用于显示创建 Recordset 的命令文本和参数。
'BeginActiveCommandVB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
'recordset and connection variables
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
'record variables
Dim strPrompt As String
Dim strName As String
Set Cnxn = New ADODB.Connection
Set cmd = New ADODB.Command
strPrompt = "Enter an author's name (e.g., Ringer): "
strName = Trim(InputBox(strPrompt, "ActiveCommandX Example"))
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
'create SQL command string
cmd.CommandText = "SELECT * FROM Authors WHERE au_lname = ?"
cmd.Parameters.Append cmd.CreateParameter("LastName", adChar, adParamInput, 20, strName)
Cnxn.Open strCnxn
cmd.ActiveConnection = Cnxn
'create the recordset by executing command string
Set rst = cmd.Execute(, , adCmdText)
'see the results
Call ActiveCommandXprint(rst)
' clean up
Cnxn.Close
Set rst = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
End If
Set rst = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndActiveCommandVB
仅为 ActiveCommandXprint 例程提供一个 Recordset 对象，但它必须输出创建 Recordset 的命令文本和参数。 之所以可以这样做，是因为 Recordset 对象的 ActiveCommand 属性会生成关联的 Command 对象。
Command 对象的 CommandText 属性生成创建 Recordset 的参数化命令。 Command 对象的 Parameters 集合生成替换命令参数占位符（“?”）的值。
最后，将生成一条错误消息或作者的姓名和 ID。
'BeginActiveCommandPrintVB
Public Sub ActiveCommandXprint(rstp As ADODB.Recordset)
Dim strName As String
strName = rstp.ActiveCommand.Parameters.Item("LastName").Value
Debug.Print "Command text = '"; rstp.ActiveCommand.CommandText; "'"
Debug.Print "Parameter = '"; strName; "'"
If rstp.BOF = True Then
Debug.Print "Name = '"; strName; "', not found."
Else
Debug.Print "Name = '"; rstp!au_fname; " "; rstp!au_lname; _
"', author ID = '"; rstp!au_id; "'"
End If
rstp.Close
Set rstp = Nothing
End Sub
'EndActiveCommandPrintVB