How to open ADO recordset with two or more parameters?

[mcaron]

How is it possible to open a ADO.Recordset with two or more parameters. Heres my example:

objCmd.CommandText = "SELECT tblAthlètes.AthlètesId, tblAthlètes.Prénom_Ath, tblAthlètes.Nom_Ath" _
& " From tblAthlètes" _
& " WHERE (((tblAthlètes.Prénom_Ath)= ?) AND ((tblAthlètes.Nom_Ath)= ?))"
objCmd.CommandType = adCmdText
objCmd.Parameters(0).Value = strCriteria1
objCmd.Parameters(1).Value = strCriteria2



Using this code gives this error on the line objCmd.Parameters(0).Value... : "Run-Time error.
The provider cannot derive Parameter info and SetParameterInfo has not been called". What is the reason for this message and how to resolve this problem? All I want to do is replace the two quotation marks with some criterias.

Can anyone help me please. Thanks for your time.

[atumuluri]

Hi,
Here u can do by declaring Parametes like this:
------------------------------------------
dim p1 as new adodb.parameter
dim p2 as new adodb.parameter
dim rs as new adodb.recordset

with p1
.direction = adparaminput
.type = adnumeric 'declare as per your case
end with

with p1
.direction = adparaminput
.type = advarchar 'declare as per your case
.size = 6
end with

with objcmd
.commandtext = strsql 'here enter your sql statement
.commandtype = adcmdtext
.activeconnection = cn 'it is your connection
.parameters.append p1
.parameters.append p2
end with

set rs = objcmd.execute

--------------------------------
Ashok

[Raghuveer]

Here is the simple example on how to create parameter queries on ADO's


Dim MyCmd1 as new ADODB.Command
Dim MyRec1 as new ADODB.Recordset

If MyRec1.State = adStateOpen then
MyRec1.Close
End If

strSQL = " SELECT COMPANY_INFO.COMPANY_NAME,COMPANY_INFO.COMPANY_ID, ASSET_INFO.ASSET_ID, WHERE BUILDING.COMPANY_ID = SITE.COMPANY_ID (COMPANY_INFO.COMPANY_ID = ?) AND (ASSET_INFO.LOAD_DATE is NOT null) AND (ASSET_INFO.LOAD_DATE >= ?) OR (ASSET_INFO.LOAD_DATE is null AND (ASSET_INFO.CREATE_DATE >= ?)) ORDER BY DEPARTMENT.DEPT_ID, ASSET_INFO.ASSET_ID”

MyCmd1.CommandText = strSQL
set MyCmd1.ActiveConnection = myConn
MyCmd1.Parameters.Append MyCmd1.CreateParameter("Comp_ID", adVariant, adParamInput)
MyCmd1.Parameters(0).Value = Comp_ID

MyCmd1.Parameters.Append MyCmd1.CreateParameter("L_Date", adDate, adParamInput)
MyCmd1.Parameters(1).Value = AssetLoadDate

MyCmd1.Parameters.Append MyCmd1.CreateParameter("C_Date", adDate, adParamInput)
MyCmd1.Parameters(2).Value = AssetCreateDate
MyRec1.Open MyCmd1, , adOpenDynamic, adLockReadOnly, adCmdText