Must declare the variable...

[Ron Hinds]

Trying to do the following Parameterized Query in Classic ASP, I get the
error:

Must declare the variable @Pagetype

Here is the code:

Set oCommLocal = Server.CreateObject("ADODB.Command")
oCommLocal.ActiveConnection = oConn
oCommLocal.CommandType = adCmdText
oCommLocal.CommandText = "SELECT SS FROM WPS WHERE
ValidationID='@ValidationID' AND type=@Pagetype and pagename='@pagename'"
Set oParamLocal = oCommLocal.CreateParameter("@ValidationID", adVarChar,
adParamInput, 50, ValidationID)
oCommLocal.Parameters.Append oParamLocal
Set oParamLocal = oCommLocal.CreateParameter("@Pagetype", adInteger,
adParamInput, 4, PageType)
oCommLocal.Parameters.Append oParamLocal
Set oParamLocal = oCommLocal.CreateParameter("@pagename", adVarChar,
adParamInput, 50, pagename)
oCommLocal.Parameters.Append oParamLocal

Set oRS = oCommLocal.Execute()

Even if I change the order of the parameters (they are named parameters so
order should not matter, right?) I get the same message.

 

[Ron Hinds]

You should use the Set keyword here, given that oConn contains a
reference to an opened connection object rather than a connection
string.
If the latter, you should rewrite this to use an explicit connection
object, in order to avoid issues brought on by lack of connection
pooling.


I've never even tried to use named parameters in ad hoc statements like
this. Use ? parameter tokens instead:

oCommLocal.CommandText = _
" ... ValidationID = ? and type = ? and pagename= ?"

And don't even bother with the explicit parameter objects. I only use
explicit parameters if I'm dealing with a stored procedure that returns
data via return or output parameters. Use a variant array instead:

arParms = Array(ValidationID, PageType, pagename)
Set oRS = oCommLocal.Execute(,arParms)

The reason I was using explicit parameter objects is to thwart SQL Injection
attacks. Will this method accomplish the same thing?

 

[Bob Barrows]

The reason I was using explicit parameter objects is to thwart SQL
Injection attacks. Will this method accomplish the same thing?

Absolutely. It's the use of parameters that thwarts SQL Injection.
Unnamed parameters are still parameters. Behind the scenes, ADO is
creating those parameter objects, using the values in that variant
array.

 

[Ron Hinds]

Absolutely. It's the use of parameters that thwarts SQL Injection.
Unnamed parameters are still parameters. Behind the scenes, ADO is
creating those parameter objects, using the values in that variant
array.

Thanks Bob, that worked. But now, when I try to reuse the Command object,
I'm getting an error
Multiple-step_OLE_DB_operation_generated_errors._Check_each_OLE_DB_status_value__if_available._No_work_was_done.

All I've done is change the CommandText - this next one only has one
parameter so I tried just sending that one parameter like so:

oCommLocal.Execute(, ValidationID)

Should I be setting it to Nothing and start over between uses?

 

[Bob Barrows]

Thanks Bob, that worked. But now, when I try to reuse the Command
object, I'm getting an error
Multiple-step_OLE_DB_operation_generated_errors._Check_each_OLE_DB_statu
s_value__if_available._No_work_was_done.

All I've done is change the CommandText - this next one only has one
parameter so I tried just sending that one parameter like so:

oCommLocal.Execute(, ValidationID)

Should I be setting it to Nothing and start over between uses?

Have you closed the recordset before trying to reuse the connection?
Only one open cursor is allowed at a time.

 

[Ron Hinds]

Have you closed the recordset before trying to reuse the connection?
Only one open cursor is allowed at a time.

Yes.

 

[Bob Barrows]

Yes.

Well, I guess you can try setting Prepared to false, but I would not
waste a lot of time getting to the bottom of this:
the simplest thing to do is re-instantiate the Command. There really is
no advantage to re-using the Command, beyond saving a couple lines of
code.