ActiveCommand Property Example (VB)
This example demonstrates the ActiveCommand property.
A subroutine is given a Recordset object whose ActiveCommand property is used to display the command text and parameter that created the Recordset.
Public Sub Main()
ActiveCommandX
End Sub
Public Sub ActiveCommandX()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim strPrompt As String, strName As String
strPrompt = "Enter an author's name (e.g., Ringer): "
strName = Trim(InputBox(strPrompt, "ActiveCommandX Example"))
cmd.CommandText = "SELECT * FROM Authors WHERE au_lname = ?"
cmd.Parameters.Append _
cmd.CreateParameter("LastName", adChar, adParamInput, 20, strName)
cnn.Open "DSN=Pubs;Provider=MSDASQL; uid=sa; pwd=;"
cmd.ActiveConnection = cnn
Set rst = cmd.Execute(, , adCmdText)
ActiveCommandXprint rst
rst.Close
cnn.Close
End Sub
The ActiveCommandXprint routine is given only a Recordset object, yet it must print the command text and parameter that created the Recordset. This can be done because the Recordset object's ActiveCommand property yields the associated Command object.
The Command object's CommandText property yields the parameterized command that created the Recordset. The Command object's Parameters collection yields the value that was substituted for the command's parameter placeholder ("?").
Finally, an error message or the author's name and ID are printed.
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
End Sub