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