Refresh Method Example (VB)

This example demonstrates using the Refresh method to refresh the Parameters collection for a stored procedure Command object.
'BeginRefreshVB
Public Sub RefreshX()

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection strings
    
    ' connection and recordset variables
   Dim Cnxn As ADODB.Connection
   Dim cmdByRoyalty As ADODB.Command
   Dim rstByRoyalty As ADODB.Recordset
   Dim rstAuthors As ADODB.Recordset
   Dim strCnxn As String
   Dim strSQLAuthors 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 a command object for a stored procedure
   ' with one parameter
   Set cmdByRoyalty = New ADODB.Command
   Set cmdByRoyalty.ActiveConnection = Cnxn
   cmdByRoyalty.CommandText = "byroyalty"
   cmdByRoyalty.CommandType = adCmdStoredProc
   cmdByRoyalty.Parameters.Refresh
   
   ' Get paramater value, execute the command
   ' and store the results in a recordset
   intRoyalty = Trim(InputBox("Enter royalty:"))
   cmdByRoyalty.Parameters(1) = intRoyalty
   Set rstByRoyalty = cmdByRoyalty.Execute()
      
   ' Open the Authors table to get author names for display
   Set rstAuthors = New ADODB.Recordset
   strSQLAuthors = "Authors"
   rstAuthors.Open strSQLAuthors, Cnxn, adOpenForwardOnly, adLockPessimistic, adCmdTable
   
   ' Print current data in the recordset
   ' and add author names
   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 rstByRoyalty = Nothing
   Set rstAuthors = Nothing
   Set Cnxn = Nothing
   
End Sub
'EndRefreshVB
Refresh Method Example (VB)

See Also
