Prev: mail attachment program using mailmessage
Next: Hotfix compatibility issue
From: Ron Hinds on 16 Jun 2010 14:16
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=(a)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.


From: Bob Barrows on 16 Jun 2010 14:51
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=(a)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)




--
HTH,
Bob Barrows


From: Ron Hinds on 16 Jun 2010 14:56
"Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
news:hvb6f5$p7d$1(a)news.eternal-september.org...
> 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=(a)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?


From: Bob Barrows on 16 Jun 2010 15:10
Ron Hinds wrote:
> "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
> news:hvb6f5$p7d$1(a)news.eternal-september.org...
>> 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


From: Ron Hinds on 16 Jun 2010 16:02

"Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
news:hvb7jt$426$1(a)news.eternal-september.org...
> Ron Hinds wrote:
>> "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
>> news:hvb6f5$p7d$1(a)news.eternal-september.org...
>>> 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_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?


 |  Next  |  Last
Pages: 1 2
Prev: mail attachment program using mailmessage
Next: Hotfix compatibility issue