Using a Session Variable to Populate a SQL Statement

[alex4501]

Hi everyone,

Ok so what I am basically trying to do is this:

sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")&"= 'Yes'"
set liftpassDB = doquery(sql)

I have also tried:

If liftpassDB(Session("QC_GroupType")) = "Yes" Then

But with no joy, does anyone knoe if this is a) possible b) where i am going wrong or c) an alternative solution.

What I basically what to do is look at the database, and extract the records where the "GroupType" is set to "Yes" in the database. In order to show results that are correct for that specific group dynamically.

The GroupType is currently stored in a Session Variable.

Thanks in advance.

Alex

[Old Pedant]

Your SQL looks perfectly reasonable to me, assuming: (1) The value in the session variable really is the name of a field in the table and (2) You really are using a TEXT field of some kind and it really does have some records where the value is 'YES'.

If this is an Access database and if the field in question is a Yes/No field, then the problem is that 'Yes' is never actually stored in such fields. Only from Access-the-program will you ever actually see a value as 'Yes'. From ADO, the field will be a boolean field, having True and False values.

NOTE: True and False are *keywords*. You do *NOT* put apostrophes around them if you use them as such!

If that is the case, just use

sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")&"= True"

or, since comparing to True is really unnecessary,

sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")

But in any case, you maybe should DEBUG to find out if the session value is what you think it is.

So no matter which variant you use, you should be doing

sql = "SELECT * FROM LiftPasses WHERE "&Session("QC_GroupType")&"= 'Yes'" ' or other variant
Response.Write "<hr>DEBUG SQL: " & sql & "<hr>"
...