Specifying Command Parameters

Instead of specifying the Command object's parameters in the SQL statement, you can also set them using the Command object's Parameter object.

For example, the following function updates the prices of all products of a given category by calling an SQL Server stored procedure, which returns a count of the products that were updated.

Public Function UpdatePrices( _ strCategory As String, _ curNewPrice As Currency) As Integer

Dim cmd As New ADODB.Command Dim RA As Long

'Build the Command object With cmd

'Set the connection

.ActiveConnection = CurrentProject.Connection

'Set other properties .CommandText = "qry├╝pdatePrices" .CommandType = adCmdTable

To be able to refer to the parameters by name (for providers that support it), you must first refresh the Parameters collection; otherwise you'll need to refer to them by ordinal position.

.Parameters.Refresh

.Parameters("Category") = strCategory

.Parameters("Price") = curNewPrice

'If we were specifying parameters for a

'stored procedure, we would use the

'predicate.

'.Parameters("@Category") = strCategory

'.Parameters("@Price") = curNewPrice

Execute the query

.Execute RA

End

With

Debug.Print RA & " products were updated."

├╝pdatePrices = RA

Set cmd.ActiveConnection = Nothing

Set cmd = Nothing

End

Function

0 0

Post a comment