SQL SELECT statement problem

mturner · #1 (**permalink**) 02-05-04, 00:31

I am using the following statement in ASP and trying to use the variable PLANT in a select statement connecting to an Access Database.

rs.Open "SELECT * FROM plant_list WHERE grp_name = "plant" and available = True ORDER BY name", conn

The statement as above generates the following:

"Microsoft VBScript compilation error '800a0401'

Expected end of statement "

I know this is because of the quotations around the variable plant. I have tried other ways, such as, (plant), ('plant'), ""plant"", and more. Can't seem to find the right syntax.

Any help is appreciated.

rokslide · #2 (**permalink**) 02-05-04, 00:40

okie your line is....

Code:

rs.Open "SELECT * FROM plant_list WHERE grp_name = "plant" and available = True ORDER BY name", conn

try

Code:

rs.Open "SELECT * FROM plant_list WHERE grp_name = '" & plant & "' and available = True ORDER BY name", conn

or

Code:

rs.Open "SELECT * FROM plant_list WHERE grp_name = """ & plant & """ and available = True ORDER BY name", conn

The first one should be better then the second though....

mturner · #3 (**permalink**) 02-05-04, 00:55

Thank you Rokslide!

The first one worked. I had tried that syntax with the DOUBLE quote, but not adding the single quotes too.

Again, thanks!

While you are here...

What can I do to get this statement to realize that 8" should be listed before 10" in a sort? It thinks 10 is first because the number 1 < 8

Don't know if there is a simple answer for this. But if you have one, thanks.

rokslide · #4 (**permalink**) 02-05-04, 01:03

Assuming you are using MS SQL as your back end database, use the Convert or Cast statement to change it to an int.

Currently it thinks it is a character value, that's why you get the strange sort order.

Internet Services

Web Development

Digital Marketing

Consumer Tech