SQL and variables

[vlitim]

I am passing a string called searchterm(MPBDSUP500MUW) to a page below

SKUNo = Request.QueryString("searchterm&quot
SQL="SELECT * FROM VIPS_Products WHERE SKU='SKUno'AND active=1"
set search=objconn.execute(SQL)

but i always get no results back. If i replace the sql to look like this

SQL="SELECT * FROM VIPS_Products WHERE SKU='MPBDSUP500MUW'AND active=1"

then the correct record is found, can someone please tell me what I am doing wrong!?

cheers!

 

[junior78]

You are not calling the the same variabale as you have declared. You have declared 'SKUNo' but you are trying to call 'SKUno'

I think that is what is wrong anyway

Onward and Upward !


SKUNo = Request.QueryString("searchterm&quot
SQL="SELECT * FROM VIPS_Products WHERE SKU='SKUno'AND active=1"
set search=objconn.execute(SQL)

 

[link9]

You should not use the ' delimeter in your statement.

SQL="SELECT * FROM VIPS_Products WHERE SKU=" & SKUno & " AND active=1"

This will 'dereference' the value of your variable, and should produce the desired results.

hope it helps!
Paul Prewett

 

[vlitim]

If I do this

SQL="SELECT * FROM VIPS_Products WHERE SKU=" & SKUno & " AND active=1"

I get this error??

Invalid column name 'MPBDSUP500MUW'

 

[link9]

do a response.write of the sql statement and post the output here... it's strange that it would give that error since that value is on the right side of the = operator???

 

[vlitim]

without sounding too stupid! how do you do a response.write of the sql statement?

cheers!?

 

[link9]

SQL="SELECT * FROM VIPS_Products WHERE SKU=" & SKUno & " AND active=1"
response.write(SQL)

Just make sure you put the response.write statement before the line where you are getting the error. This will output the value of the string, 'SQL' to the screen so that you can see exactly what's in there.

It's the most effective way (in my humble opinion) to debug asp scripts.

 

[vlitim]

Excellent very useful to know, this is what is outputed, though I can't see what is wrong with it

SELECT * FROM VIPS_Products WHERE SKU=MPBDSUP500MUW AND active=1

 

[hblackorby]

You should put ' around your string. If you don't SQL treats it like a number until it realizes that it isn't.

Try:
SQL="SELECT * FROM VIPS_Products WHERE SKU='" & SKUno & "' AND active=1"

That should output:
SELECT * FROM VIPS_Products WHERE SKU='MPBDSUP500MUW' AND active=1

Also, VBScript is not case sensitive. Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[link9]

Give this a shot --

SQL = "SELECT * FROM VIPS_Products WHERE ((SKU=""" & SKUno & """) AND (active=1))"

That will get your value in quotes (that's good if the value is stored in the db as a string), and we'll just stick in the parentheses for giggles.

let me know how it works

 

[link9]

There we go, then... Harold saves the day.

 

[vlitim]

Excellent, thanks alot everybody using quotes works, I had spent ages trying to figure that out!

Again thanks for all your help!