Creating Your Own Parameters

It's quite simple to create your own parameters on-the-fly in code using ADO. To create the parameter for a given query, call the CreateParameter method from the Command object. The benefit in this case is that you can specify a SQL statement in code and create the parameters for that statement when the code is run. Here's an example of creating parameters using the Command object:

Public Function GetPriceByCustomParameter(strName As String) As

Double

' Define Variables

Dim cmd As New ADODB.Command

Dim rs As New ADODB.Recordset

' Setup the Command object With cmd

' Set the connection

.ActiveConnection = CurrentProject.Connection

.CommandText = "SELECT [Prices].* FROM [Prices] WHERE [Prices].[ItemName]=[strItemName]" .CommandType = adCmdUnknown

' Create the parameter and set the value .Parameters.Append cmd.CreateParameter("[strItemName]", adParamlnput, 100)

.Parameters("[strItemName]") = strName End With

adVarChar,

' 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" GetPriceByCustomParameter = 0

Else

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

Set rs = Nothing

Set cmd = Nothing

End Function

0 0

Post a comment