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 retrieves the price of a specified Item by calling a Select query in the current database.

Public Function GetPrice(strName As String) As Double

' Define Variables

Dim cmd As New ADODB.Command

Dim rs As Recordset

' Build the Command object With cmd

' Set the connection

.ActiveConnection = CurrentProject.Connection

' Set other properties .CommandText = "qryGetPrice" .CommandType = adCmdTable

' To be able to refer to parameters by name, ' you must refresh the parameters collection .Parameters.Refresh

' Supply the parameter for the query .Parameters("[strItemName]") = strName End With

' Execute the Query and return the price Set rs = cmd.Execute

' Set the Price If rs.RecordCount < 1 Then

MsgBox "There was no record for the Item Specified" GetPrice = 0

Else

GetPrice = rs("Price").Value End If

' Clean up

Set rs = Nothing

Set cmd = Nothing

End Function

0 0

Post a comment