Variable in SQL statement

[fugtruck]

I am passing a URL like /file.asp?state=NY

In file.asp, I have:

****
TableStuff = objConnection.Execute("SELECT * FROM table1 WHERE col1 = " + Request.QueryString('state') + ";")
****
But when I run the script I get, "No value given for one or more required parameters".

Now, if I have the following:

****
TableStuff = objConnection.Execute("SELECT * FROM table1 WHERE col1 = "NY";)
****
I get the proper results. So my question is, how do I pass a query string as a variable into an sql statement?

[ChrisBrown]

Make sure that you write your statement properly. It should look like:

TableStuff = objConnection.Execute("SELECT * FROM table1 WHERE col1 = '" & Request.QueryString('state') & "';")

or simply pass the value that you are getting from the string to a variable first and do this to simplify your SQL statement:

state=Request.QueryString("state")
TableStuff = objConnection.Execute("SELECT * FROM table1 WHERE col1 = '" & state & "';")

[ChrisBrown]

I thought that you had to use "&"'s, since the VBScript variable was inside a function, and you wanted to pass the value of that into the string that you are going to execute.

[Ribeyed]

Hi,


I sometimes see it coded as follows:

state=Request.QueryString("state")
TableStuff = objConnection.Execute("SELECT * FROM table1 WHERE col1 = ::state::;")

Can anyone verify for me as to when this is actually valid and when it is not?


gave your sql staement a try and i got an error at the ":"

also "state" is showing up as a reserved word in Dreamweaver so i suggest changing state to maybe thestate to eliminate that.

Tested this against SQL 2000 on Windows XP Pro, IIS V5.1
and IE6.

[Nicodemas]

"State" is not an Access reserved word, just a little tidbit of info

am passing a URL like /file.asp?state=NY

In file.asp, I have:

****
TableStuff = objConnection.Execute("SELECT * FROM table1 WHERE col1 = " + Request.QueryString('state' ) + ";")


Doesn't it matter than this person is using single quotes in the Querystring call?? I just tested and got an error.