ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties Example (VB)

This example uses the ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction properties to execute a stored procedure.

'BeginActiveConnectionVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub ActiveConnectionX()

    'recordset, command and connection variables
   Dim Cnxn As ADODB.Connection
   Dim cmdByRoyalty As ADODB.Command
   Dim prmByRoyalty As ADODB.Parameter
   Dim rstByRoyalty As ADODB.Recordset
   Dim rstAuthors As ADODB.Recordset
   Dim strCnxn As String
   Dim strSQLAuthors As String
   Dim strSQLByRoyalty As String
    'record variables
   Dim intRoyalty As Integer
   Dim strAuthorID As String

   ' Define a command object for a stored procedure
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
   
   Set cmdByRoyalty = New ADODB.Command
   Set cmdByRoyalty.ActiveConnection = Cnxn
   ' Set the criteria
   strSQLByRoyalty = "byroyalty"
   cmdByRoyalty.CommandText = strSQLByRoyalty
   cmdByRoyalty.CommandType = adCmdStoredProc
   cmdByRoyalty.CommandTimeout = 15
      
   ' Define the stored procedure's input parameter
   intRoyalty = Trim(InputBox("Enter royalty:"))
   Set prmByRoyalty = New ADODB.Parameter
   prmByRoyalty.Type = adInteger
   prmByRoyalty.Size = 3
   prmByRoyalty.Direction = adParamInput
   prmByRoyalty.Value = intRoyalty
   
   cmdByRoyalty.Parameters.Append prmByRoyalty
  
   ' Create a recordset by executing the command.
   Set rstByRoyalty = cmdByRoyalty.Execute()
      
   ' Open the Authors Table to get author names for display
   Set rstAuthors = New ADODB.Recordset
   strSQLAuthors = "Authors"
   
   'rstAuthors.Open strSQLAuthors, strCnxn, , , adCmdTable
   rstAuthors.Open SQLAuthors, strCnx, adOpenForwardOnly, adLockReadOnly, adCmdTable
   'the above two lines of code are identical as the default values for
   'CursorType and LockType arguments match those shown
   
   ' Print the recordset and add author names from Table
   Debug.Print "Authors with " & intRoyalty & _
      " percent royalty"
      
    Do Until rstByRoyalty.EOF
       strAuthorID = rstByRoyalty!au_id
       Debug.Print , rstByRoyalty!au_id & ", ";
       rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
       Debug.Print rstAuthors!au_fname & " " & _
          rstAuthors!au_lname
       rstByRoyalty.MoveNext
    Loop

   rstByRoyalty.Close
   rstAuthors.Close
   Cnxn.Close
   
    ' clean up
   Set Cnxn = Nothing
   Set rstAuthors = Nothing
   Set rstByRoyalty = Nothing
 
End Sub
'EndActiveConnectionVB

ActiveConnection, CommandText, CommandTimeout, CommandType, Size, and Direction Properties Example (VB)

See Also