3

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


%>
Rupesh Yadav
  • 12,096
  • 4
  • 53
  • 70
My Alter Ego
  • 3,392
  • 3
  • 18
  • 9

4 Answers4

4

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

egrunin
  • 24,650
  • 8
  • 50
  • 93
Bork Blatt
  • 3,308
  • 2
  • 19
  • 17
3

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!

Chris Nielsen
  • 14,731
  • 7
  • 48
  • 54
  • Yip. I managed to get that right as well. But I'd really like to get the named aspect working, as it'll be harder to keep track of parameter order once the queries or statements get more involved. – My Alter Ego Jul 08 '09 at 06:59
  • 1
    After trying a few variations of this, I wasn't able to come up with a solution either. I suspect that using named parameters is only going to work if you are calling a stored procedure, not sending up an SQL command. – Chris Nielsen Jul 14 '09 at 20:11
3
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
user2316116
  • 6,726
  • 1
  • 21
  • 35
Joost Moesker
  • 661
  • 3
  • 7
0

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
Chris Farmer
  • 24,974
  • 34
  • 121
  • 164
  • It works fine with a question mark, so the parameter can be in the select list. The problem is the same if I move the parameter to where clause. The point of the query is to have the shortest example I could come up with for others to tinker with. – My Alter Ego Jul 08 '09 at 06:57