ASP Classic VBscript Parameterized SQL Query?

Question

First post but thank you for all the help I've gotten from this site so far.

I'm trying to parameterize an SQL query:

query_url = Request.QueryString("ID")

Set rs = Server.CreateObject("ADODB.Recordset")

Set cmd = server.createobject("ADODB.Command")

cmd.ActiveConnection = Internet_String
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT NAME FROM OWNER.TABLE WHERE ID = " + "?" + ""

Set param = cmd.CreateParameter(, , ,200 , Replace(query_url, "'", "''"))

cmd.Parameters.Append param

Set rs = cmd.Execute()

So if I use (no parameters):

SELECT NAME FROM OWNER.TABLE WHERE ID = " + Replace(query_url, "'", "''") + ""

It works fine, so I know my DB connection and query_url are working. Is something wrong with my SQL statement in the parameterized query? I've tried it so many different ways.

When I run my parameterized query in Dreamweaver the page will not load anytime, just spins infinitely, I'm assuming it's not getting a response back from the DB.

Thanks!

EDIT

Alright thanks for the help so far, I'm getting closer. The page loads now but the fields are still blank, heres what I've got so far:

Set rs = Server.CreateObject("ADODB.Recordset")

Set cmd = server.createobject("ADODB.Command")

cmd.ActiveConnection = internet_string    
cmd.CommandType = adCmdText

cmd.CommandText = "SELECT NAME FROM OWNER.TABLE WHERE ID = @param"

Set param = cmd.CreateParameter("@param", , ,200 , query_url)

cmd.Parameters.Append param

response.Write(param)

Set rs = cmd.Execute()

Here's how I'm referencing the data:

<strong>Name: <%=(rs.Fields.Item("NAME").Value)%></strong>

Any ideas?

Not sure about classic asp and vbscript, but in other languages you don't need quotes when you send query parameters. In any event, use a hard coded value for your paramter as a troubleshooting method. — Dan Bracuk
– Dan Bracuk, Commented Jul 22, 2013 at 15:01
Don't substitute apostrophes in the parameter value (i.e., use cmd.CreateParameter(, , ,200 , query_url). Since you're using parameters, all the work of escaping special characters is done behind the scenes. — Cheran Shunmugavel
– Cheran Shunmugavel, Commented Jul 23, 2013 at 6:41

Alex K. · Accepted Answer · 2013-07-22 15:01:05Z

1

Use a named placeholder;

cmd.CommandText = "SELECT NAME FROM OWNER.TABLE WHERE ID = @ID"

Then provide its value

Set param = cmd.CreateParameter("@ID", , ,200, Replace(query_url, "'", "''"))

FYI you do not need to escape ' in an parameterized query

answered Jul 22, 2013 at 15:01

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2607172 Over a year ago

Thanks for your answer. Now I'm receiving a blank page, which is better than timing out, but the name field still isn't populating. The page im working with is a dwt (dreamweaver template file) perhaps this has something to do with why the fields won't populate? Sorry if this just went way outside the scope of my original question.

Collectives™ on Stack Overflow

ASP Classic VBscript Parameterized SQL Query?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related