Optional Search Parameters

[harrysdad]

I have a search page with 3 drop-down boxes (fed from tables in a database) from which users can pick 3 categories to search on. However, I need to set it up so that users can search on only two of those categories if they so wish.

The page is backed by a sql query of the WHERE ID = 'parameter1' AND Name = 'parameter2' type.

I can't think of a way of setting this page up so that the three search terms can be optional. Can anyone suggest a strategy?

Regards,

Tony

 

[mattygriff]

Make sure each of them has a "Not Selected" value and then check for this when constructing the SQL for your search.

 

[amorous]

Here you go:

Using below method the search works for any combination whether the user selects one or two or all the three parameters

parameter1 = "%"
parameter1 = "%"
parameter1 = "%"


If LEN(TRIM(Request.Form("parameter1"))) > 0 Then
parameter1 = Request.QueryString("parameter1")
End If
If LEN(TRIM(Request.Form("parameter2"))) > 0 Then
parameter2 = Request.QueryString("parameter1")
End If
If LEN(TRIM(Request.Form("parameter3"))) > 0 Then
parameter3 = Request.QueryString("parameter3")
End If

Then use the search query as

where ID ='"&parameter1&"' AND Name='"&parameter2&"' AND somethingelse='"&parameter3&"'

hope that helps

VJ

 

[amorous]

oops that shud be

parameter1="%"
parameter2="%"
parameter3="%"

VJ

 

[harrysdad]

So if I'm understanding you correctly, I would put a value in the lookup table that says "choose one" or something with a value associated with that. I would then need to write some VBScript that builds a sql statement piece by piece according to what had or hadn't been selected. Is that what you mean?

Regards,

Tony