Combination Quries

[dalezjc]

I'm building my first query form in ASP and it's going to be a combination search form. My search form has the following fields: lastname, qualification, location, grade, certifications, and eval_type.

I'm a newbie to ASP so this is probably not the way to be doing this, but here's what I have so far:
[code=asp]<%
'Search for lastname only
If overall_qual = "" AND location = "" AND grade = "" AND eval_type = "" Then
SQL = "SELECT user_id, lastname, overall_qual, location, grade, eval_Type " _
& " FROM usertbl " _
& " Where lastname like '" & Request.QuerySt ring("lastname" ) &"' " _
& " ORDER BY user_id ASC"


'Search for overall_qual only
ElseIf lastname = "" AND location = "" AND grade = "" AND eval_type = "" Then
SQL = "SELECT user_id, lastname, overall_qual, location, grade, eval_Type " _
& " FROM usertbl " _
& " Where overall_qual like '" & Request.QuerySt ring("overall_q ual") &"' " _
& " ORDER BY user_id ASC"


'Search for location only
ElseIf lastname = "" AND overall_qual = "" AND grade = "" AND eval_type = "" Then
SQL = "SELECT user_id, lastname, overall_qual, location, grade, eval_Type " _
& " FROM usertbl " _
& " Where location like '" & Request.QuerySt ring("location" ) &"' " _
& " ORDER BY user_id ASC"


'Search for grade only
ElseIf lastname = "" AND overall_qual = "" AND location = "" AND eval_type = "" Then
SQL = "SELECT user_id, lastname, overall_qual, location, grade, eval_Type " _
& " FROM usertbl " _
& " Where grade like '" & Request.QuerySt ring("grade") &"' " _
& " ORDER BY user_id ASC"


'Search for evaluation type only
ElseIf lastname = "" AND overall_qual = "" AND location = "" AND grade = "" Then
SQL = "SELECT user_id, lastname, overall_qual, location, grade, eval_Type " _
& " FROM usertbl " _
& " Where eval_type like '" & Request.QuerySt ring("eval_type ") &"' " _
& " ORDER BY user_id ASC"

'Search for certs only
ElseIf lastname = "" AND overall_qual = "" AND location = "" AND grade = "" AND eval_type = "" Then
SQL = "SELECT user_id, lastname, overall_qual, location, grade, eval_Type, existing_certs " _
& " FROM usertbl " _
& " Where existing_certs '" & Request.QuerySt ring("existing_ certs") &"' " _
& " ORDER BY user_id ASC"[/code]

I have to allow the user to search on just one field by itself, or a combination of fields as well.

The first three if/elseif statements are working, but the fourth on down don't work and I assume it's because I haven't grouped (?) the search terms? Any help on grouping these together would be appreciated.

Thanks,
Dale

[jhardman]

Dale,

Here's one way to simplify:

SQL = "SELECT * FROM usertbl WHERE "
if request("lastName") <> "" then
   SQL = SQL & "lastName LIKE '" & request("lastname") & "' AND "
end if

if request("overall_qual") <> "" then
   SQL = SQL & " overall_qual LIKE '" & Request("overall_qual") &"' AND " 
end if

'etc for the rest of the possibilities
'this will leave your query with a trailing " AND " regardless

SQL = left(SQL, len(sql)-4) 'removes trailing AND and space

SQL = SQL & "ORDER BY user_id ASC"

Let me know if this helps.

Jared

[dalezjc]

Thanks for suggestions Jared. I like your style. Now I'm trying to combine the following queries, but am getting an error message:

if (request("lastn ame") AND request("overal l_qual") AND request("eval_t ype") AND request("existi ng_certs") ) <> "" then
SQL = SQL & " (location LIKE '" & Request("locati on") AND grade LIKE '" & Request("grade" )) &"' AND "
end if

Thanks,
Dale

[dalezjc]

Okay, got it working with the following:

if request("lastname") <> "" AND request("overall_qual") <> ""  AND request("eval_type") <> ""  AND request("existing_certs")  <> "" then
   SQL = SQL & " (location LIKE '" & Request("location") &"') AND (grade LIKE '" & Request("grade") &"') AND " 
end if

Another question though: how do I use wildcards in the query? I've tried this:

if request("existing_certs") <> "" then
   SQL = SQL & " existing_certs LIKE '" & Request("%existing_certs%") &"' AND " 
end if

But having no luck.

[jhardman]

It's been a while since I tried, but if I remember it should be instead of the single quotes:
[code=asp] SQL = SQL & " existing_certs LIKE %" & Request("existi ng_certs") &"%"
[/code]

Jared