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:

<% 
'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.QueryString("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.QueryString("overall_qual") &"' " _
& " 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.QueryString("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.QueryString("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.QueryString("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.QueryString("existing_certs") &"' " _
& " ORDER BY user_id ASC"

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("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

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:

 SQL = SQL & " existing_certs LIKE %" & Request("existing_certs") &"%"
 

Jared