«

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.

"Bob Barrows" <> wrote in message
news:hvb6f5$p7d$...
> Ron Hinds wrote:
>> 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
>
> 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.
>
>> 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.
>
> 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?

Ron Hinds wrote:
> "Bob Barrows" <> wrote in message
> news:hvb6f5$p7d$...
>> Ron Hinds wrote:

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

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.


--
HTH,
Bob Barrows

"Bob Barrows" <> wrote in message
news:hvb7jt$426$...
> Ron Hinds wrote:
>> "Bob Barrows" <> wrote in message
>> news:hvb6f5$p7d$...
>>> Ron Hinds wrote:
>
>>>
>>> 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?
>
> 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_do ne.

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?

Ron Hinds wrote:
> "Bob Barrows" <> wrote in message
> news:hvb7jt$426$...
>> Ron Hinds wrote:
>>> "Bob Barrows" <> wrote in message
>>> news:hvb6f5$p7d$...
>>>> Ron Hinds wrote:
>>
>>>>
>>>> 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?
>>
>> 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_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.

--
HTH,
Bob Barrows

"Bob Barrows" <> wrote in message
news:hvbced$g02$...
> Ron Hinds wrote:
>> "Bob Barrows" <> wrote in message
>> news:hvb7jt$426$...
>>> Ron Hinds wrote:
>>>> "Bob Barrows" <> wrote in message
>>>> news:hvb6f5$p7d$...
>>>>> Ron Hinds wrote:
>>>
>>>>>
>>>>> 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?
>>>
>>> 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_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.

Yes.

Ron Hinds wrote:
> "Bob Barrows" <> wrote in message
> news:hvbced$g02$...
>> Ron Hinds wrote:
>>> "Bob Barrows" <> wrote in message
>>> news:hvb7jt$426$...
>>>> Ron Hinds wrote:
>>>>> "Bob Barrows" <> wrote in message
>>>>> news:hvb6f5$p7d$...
>>>>>> Ron Hinds wrote:
>>>>
>>>>>>
>>>>>> 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?
>>>>
>>>> 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_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.
>
> 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.


--
HTH,
Bob Barrows