Declare parameterized query in markup

Question

I'm trying to convert a SQL Server 2008 R2 Express query in ASP-Classic that is not parameterized so I did the following (see code below) but I keep getting an error saying:

Object doesn't support this property or method: 'Parameters'

Can someone please tell me what am I doing wrong!

Thanks!

<% ' OPEN DATABASE dim objConn, objRS, objTRS, objUnit dim strConnection set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "Driver={SQL Server};Server=MSSQLSrv;Database=DbTest;UID=blablabala;PWD=blablabala" objConn.Open strConnection set objRS = Server.CreateObject("ADODB.Recordset") set objRS.ActiveConnection = objConn strQuery = "SELECT USERNAME,PASSWORD from CUSTOMERS where EMAIL=?" objRS.Parameters(0) = Request.QueryString("email") objRS.Open strQuery %>

gina · Accepted Answer · 2012-08-20 20:45:52Z

If you are trying make it a parameter query, then you need to create the command object and parameters first. For example:

' create your command object Const adCmdText = &H0001 Set objCmd = Server.CreateObject("ADODB.Command") objCmd.ActiveConnection = YourConnectionString objCmd.CommandType = adCmdText ' Evaluate as textual definition, not stored procedure 'now create query and add parameters strQuery = "SELECT USERNAME,PASSWORD from CUSTOMERS where EMAIL=?" objCmd.CommandText=strQuery objCmd.Parameters.Append = objCmd.CreateParameter("ParameterName", ParameterType, adParamInput, parameterSize, ParameterValue) SET objRS = objCmd.execute(strSQL) Set objCmd=Nothing

PatFromCanada · Accepted Answer · 2012-08-20 20:55:24Z

The parameters collection is on the command object not the recordset.

Here is some VB6 code that works, the parameter name is not important as parameter order is all that counts.

 Dim rst As Recordset Dim cmd As ADODB.Command Set cmd = New Command With cmd .CommandText = "SELECT USERNAME,PASSWORD from CUSTOMERS where EMAIL=?" ' this proc also returns factor info .CommandType = adCmdText Set .ActiveConnection = objconn .Parameters.Append .CreateParameter("@Email", adVarChar, adParamInput, 50, "Email") Set rst = cmd.Execute End With

compcobalt · Accepted Answer · 2012-08-21 14:01:11Z

You can do something like this:

 ' OPEN DATABASE dim objConn,objRS,objTRS,objUnit Const adCmdText = &H0001 Set objConn = Server.CreateObject("ADODB.Command") objConn.ActiveConnection = "Driver={SQL Server};Server=MSSQLSrv;Database=DbTest;UID=blablabala;PWD=blablabala" objConn.CommandType = adCmdText strQuery = "SELECT USERNAME,PASSWORD from CUSTOMERS where EMAIL=?" objConn.CommandText=strQuery objConn.Parameters(0) = Request.QueryString("email") SET objRS = objConn.execute(strQuery)

Collectives™ on Stack Overflow

Declare parameterized query in markup

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related