CODEWITHSUNDEEP
asp-classicparameterizednamed-parameters
My Alter Ego
My Alter Ego

Reputation: 3412

ASP Classic Named Parameter in Paramaterized Query: Must declare the scalar variable

I'm trying to write a parameterized query in ASP Classic, and it's starting to feel like i'm beating my head against a wall. I'm getting the following error:

Must declare the scalar variable "@something".

I would swear that is what the hello line does, but maybe i'm missing something...

<% OPTION EXPLICIT %>
<!-- #include file="../common/adovbs.inc" -->
<%

    Response.Buffer=false

    dim conn,connectionString,cmd,sql,rs,parm

    connectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.\sqlexpress;Initial Catalog=stuff"
    set conn = server.CreateObject("adodb.connection")
    conn.Open(connectionString)

    set cmd = server.CreateObject("adodb.command")
    set cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.CommandText = "select @something"
    cmd.NamedParameters = true
    cmd.Prepared = true
    set parm = cmd.CreateParameter("@something",advarchar,adParamInput,255,"Hello")
    call cmd.Parameters.append(parm)
    set rs = cmd.Execute
    if not rs.eof then
        Response.Write rs(0)
    end if


%>

Upvotes: 3

Views: 17347

Answers (4)

Joost Moesker
Joost Moesker

Reputation: 671

with server.createobject("adodb.command")
  .activeConnection = application("connection_string")
  .commandText = "update sometable set some_col=? where id=?"
  .execute , array(some_value, the_id)
end with

Upvotes: 3

Bork Blatt
Bork Blatt

Reputation: 3368

Here's some sample code from an MSDN Library article on preventing SQL injection attacks. I cannot find the original URL, but googling the title keywords (Preventing SQL Injections in ASP) should get you there quick enough. Hope this real-world example helps.

strCmd = "select title, description from books where author_name = ?"
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = strCmd
objCommand.CommandType = adCmdText
Set param1 = objCommand.CreateParameter ("author", adWChar, adParamInput, 50)
param1.value = strAuthor
objCommand.Parameters.Append param1
Set objRS = objCommand.Execute()

See the following page on MSDN, near the bottom, referring specifically to named parameters.

MSDN example

Upvotes: 4

Chris Farmer
Chris Farmer

Reputation: 25396

I'm not sure what your query is intended to accomplish. I'm also not sure that parameters are allowed in the select list. MSDN used to have (many years ago, probably) a decent article on where parameters were allowed in a query, but I can't seem to find it now.

OTTOMH, your attempts to supply the parameter values to ADO look correct. Does your query execute if you do something like this?

SELECT 1 FROM sometable WHERE somefield = @something

Upvotes: 0

Chris Nielsen
Chris Nielsen

Reputation: 14748

ADO is going to expect question marks instead of actual parameter names in this case. Right now, the SQL "select @something" is not actually parameterized: it sees the "@something" as an (undeclared) SQL variable, not as a parameter. Change your CommandText line to this:

cmd.CommandText = "select ?"

And I think you will get the result you are looking for.

Good luck!

Upvotes: 3

Related Questions