Must declare the variable...

Discussion in 'ASP General' started by Ron Hinds, Jun 16, 2010.

  1. Ron Hinds

    Ron Hinds Guest

    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, Jun 16, 2010
    #1
    1. Advertising

  2. Ron Hinds

    Ron Hinds Guest

    "Bob Barrows" <> wrote in message
    news:hvb6f5$p7d$-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=@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, Jun 16, 2010
    #2
    1. Advertising

  3. Ron Hinds

    Bob Barrows Guest

    Ron Hinds wrote:
    > "Bob Barrows" <> wrote in message
    > news:hvb6f5$p7d$-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
     
    Bob Barrows, Jun 16, 2010
    #3
  4. Ron Hinds

    Ron Hinds Guest

    "Bob Barrows" <> wrote in message
    news:hvb7jt$426$-september.org...
    > Ron Hinds wrote:
    >> "Bob Barrows" <> wrote in message
    >> news:hvb6f5$p7d$-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?
     
    Ron Hinds, Jun 16, 2010
    #4
  5. Ron Hinds

    Bob Barrows Guest

    Ron Hinds wrote:
    > "Bob Barrows" <> wrote in message
    > news:hvb7jt$426$-september.org...
    >> Ron Hinds wrote:
    >>> "Bob Barrows" <> wrote in message
    >>> news:hvb6f5$p7d$-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_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, Jun 16, 2010
    #5
  6. Ron Hinds

    Ron Hinds Guest

    "Bob Barrows" <> wrote in message
    news:hvbced$g02$-september.org...
    > Ron Hinds wrote:
    >> "Bob Barrows" <> wrote in message
    >> news:hvb7jt$426$-september.org...
    >>> Ron Hinds wrote:
    >>>> "Bob Barrows" <> wrote in message
    >>>> news:hvb6f5$p7d$-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_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, Jun 17, 2010
    #6
  7. Ron Hinds

    Bob Barrows Guest

    Ron Hinds wrote:
    > "Bob Barrows" <> wrote in message
    > news:hvbced$g02$-september.org...
    >> Ron Hinds wrote:
    >>> "Bob Barrows" <> wrote in message
    >>> news:hvb7jt$426$-september.org...
    >>>> Ron Hinds wrote:
    >>>>> "Bob Barrows" <> wrote in message
    >>>>> news:hvb6f5$p7d$-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_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
     
    Bob Barrows, Jun 17, 2010
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Boris Zakharin

    Error: Must Declare Variable

    Boris Zakharin, Jul 21, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    628
    Boris Zakharin
    Jul 22, 2003
  2. Jiggaz
    Replies:
    6
    Views:
    32,220
    Raghudubba
    Nov 5, 2009
  3. =?Utf-8?B?S2VuIEFkZW5pamk=?=

    Must declare the scalar variable '@FirstName'

    =?Utf-8?B?S2VuIEFkZW5pamk=?=, Apr 26, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    80,359
    leojose
    Aug 17, 2007
  4. Oded Dror

    Must Declare the Scalar Variable error

    Oded Dror, May 28, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    19,053
    Oded Dror
    May 28, 2006
  5. Replies:
    0
    Views:
    2,489
Loading...

Share This Page