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("Category") = strCategory

.Parameters("Price") = curNewPrice

'If we were specifying parameters for a

'stored procedure, we would use the


'.Parameters("@Category") = strCategory

'.Parameters("@Price") = curNewPrice

Execute the query

.Execute RA



Debug.Print RA & " products were updated."

├╝pdatePrices = RA

Set cmd.ActiveConnection = Nothing

Set cmd = Nothing



0 0

Post a comment