Refresh Method Example

You are here: ADO Component Reference > ADO Collections > ADO Collections Methods > ADO Collections Refresh Method > Refresh Method Example

Refresh Method Example

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

Public Sub RefreshX()

Dim cnn1 As ADODB.Connection

Dim cmdByRoyalty As ADODB.Command

Dim rstByRoyalty As ADODB.Recordset

Dim rstAuthors As ADODB.Recordset

Dim intRoyalty As Integer

Dim strAuthorID As String

Dim strCnn As String

' Open connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

' Open a command object for a stored procedure

' with one parameter.

Set cmdByRoyalty = New ADODB.Command

Set cmdByRoyalty.ActiveConnection = cnn1

cmdByRoyalty.CommandText = "byroyalty"

cmdByRoyalty.CommandType = adCmdStoredProc

cmdByRoyalty.Parameters.Refresh

' Get paramater value and execute the command,

' storing 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

rstAuthors.Open "authors", cnn1, , , adCmdTable

' Print current data in the recordset, adding

' author names from Authors table.

Debug.Print "Authors with " & intRoyalty & " percent royalty"

Do While Not 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

rstByRoyalty.Close

rstAuthors.Close

cnn1.Close

End Sub