Refresh Method Example (VB)

This example demonstrates using the Refresh method to refresh the Parameters collection for a stored procedure Command object.

Option Explicit

'BeginRefreshVB
Public Sub Main()
    On Error GoTo ErrorHandler

    '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
    Dim strRoyalty As String
    
    ' Open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    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
    strRoyalty = InputBox("Enter royalty:")
    If strRoyalty = "" Then
        Err.Raise 1, , "You either didn't enter royalty or canceled the input box. Exit the application"
    End If
    
    intRoyalty = Trim(strRoyalty)
    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
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rstByRoyalty Is Nothing Then
        If rstByRoyalty.State = adStateOpen Then rstByRoyalty.Close
    End If
    Set rstByRoyalty = Nothing
    
    If Not rstAuthors Is Nothing Then
        If rstAuthors.State = adStateOpen Then rstAuthors.Close
    End If
    Set rstAuthors = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndRefreshVB

See Also

Command Object | Parameters Collection | Refresh Method