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