CODEWITHSUNDEEP
sql-serversql-server-2008asp-classicparameters
msvuze
msvuze

Reputation: 1397

Declare parameterized query in markup

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
%>

Upvotes: 1

Views: 575

Answers (3)

compcobalt
compcobalt

Reputation: 1362

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)

Upvotes: 1

PatFromCanada
PatFromCanada

Reputation: 2788

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

Upvotes: 2

gina
gina

Reputation: 23

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

Upvotes: 2

Related Questions