SQL query to search from multiple input boxes

[meldrape]

Greetings,

I have a search page with three search boxes: city, state, country. I'm in a quandry because I don't know how to write the sql query, or do I need multiple queries, because the user does not necessarily have to fill in all three search boxes. Here's what I have:

sql = "select * from tbl_class_data where (Class_Name LIKE N'%" & city & "%' OR Meeting_Church_City LIKE N'%" & city & "%') OR (meeting_church_state='" & state & "') OR (country='" & country & "') AND class_type=1 ORDER BY Meeting_Church_City"

This works great if all three boxes are filled in, but craps out if they leave one or two blank, doesn't return anything. How do I handle this? I've tried to find the answer in the posts but to no avail. Thanks in advance.

 

[Chopstik]

You could create an if-then statement in ASP using VBScript that would recognise whether the variable is there and, if it is, then make that one of the parameters of the SQL query.

Example:

strSQL = "SELECT * FROM table WHERE "
if len(city) then
strTemp = "CityName like '%" & city & "%'"
end if

if len(state) then
if len(strTemp) then
strTemp = strTemp & " AND StateName like '%" & state & "%'"
else strTemp = StateName like '%" & state & "%'"
end if
end if

if len(country) then
if len(strTemp) then
strTemp = strTemp & " AND CountryName like '%" & country & "%'"
else strTemp = " AND CountryName like '%" & country & "%'"
end if
end if

strSQL = strSQL & strTemp

if right(strSQL, 6) = "WHERE " then
strSQL = left(strSQL, 50 (or whatever the number is))
end if

You can modify this to fit your needs, but that is the general idea. HTH
Insanity is merely a state of mind while crazy people have a mind of their own.

 

[meldrape]

I believe that will work nicely. Thanks very much for your input!