Append and CreateParameter Methods Example (VB)

This example uses the Append and CreateParameter methods to execute a stored procedure with an input parameter.
'BeginAppendVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
    
Public Sub AppendX()

    '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

   ' Open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
      
   ' Open command object with one parameter
   Set cmdByRoyalty = New ADODB.Command
   cmdByRoyalty.CommandText = "byroyalty"
   cmdByRoyalty.CommandType = adCmdStoredProc
   
   ' Get parameter value and append parameter
   intRoyalty = Trim(InputBox("Enter royalty:"))
   Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", adInteger, adParamInput)
   cmdByRoyalty.Parameters.Append prmByRoyalty
   prmByRoyalty.Value = intRoyalty

   ' Create recordset by executing the command
   Set cmdByRoyalty.ActiveConnection = Cnxn
   Set rstByRoyalty = cmdByRoyalty.Execute
   
   ' Open the Authors Table to get author names for display
   ' and set cursor client-side
   Set rstAuthors = New ADODB.Recordset
   strSQLAuthors = "Authors"
   rstAuthors.Open strSQLAuthors, Cnxn, adUseClient, adLockOptimistic, adCmdTable
   
   ' Print recordset adding author names from Authors 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

    ' clean up
   rstByRoyalty.Close
   rstAuthors.Close
   Cnxn.Close
   Set Cnxn = Nothing
   Set rstAuthors = Nothing
   Set rstByRoyalty = Nothing


End Sub
'EndAppendVB
Append and CreateParameter Methods Example (VB)

See Also
