MDAC 2.5 SDK - ADO


 

ActiveCommand Property Example (VB)

See Also

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