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.

'BeginActiveCommandVB
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
'EndActiveCommandVB

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.

'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

End Sub
'EndActiveCommandPrintVB

ActiveCommand Property Example (VB)

See Also