Search and Results using multiple columns

I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)

I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.

I'm lost on both the search and the results code.

I'd like to have a drop down for the search like this:

<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>

Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>


Any ideas on how to make this work would be greatly appreciated.

I think you should send the option values the fieldnames from your tables:



So then you can form your sql querystring easily (with just concatenation) in your "vendor_results.asp". The form of the query will be like:

SELECT CompanyName FROM Vendors WITH (NOLOCK) WHERE <searchfield>='Yes' ORDER BY CompanyName ASC

(or <searchfield>=1 if you used boolean). This amounts to the following code for building the query string.



I hope this helps. I am really new to ASP and MS SQL, so good luck. OH YEAH, this works if you want to mutually exclude the options. If you wanted them to be able to have multiple options set to yes (i.e. SBI and WBE set to yes), you'll have to use a different form element and form the query slightly different.

Thanks - this did help - i had to make a few changes but it works now - greatly appreciated! I also had never heard of the (NOLOCK) command so thanks for that as well.

Here is what I did if anyone wants to see it:

for my search page code i used what chichian suggested:

code:

<form name="Search" action="vendor_results.asp" method="post">
<input type=hidden name="validate" value=1>
Show all Vendors who are:
<select name="searchfield">
<option value="SBI">SBI</option>
<option value="SBE">SBE</option>
<option value="MBE">MBE</option>
<option value="WBE">WBE</option>
</select>
<input type="submit" value="Go">
</form>

And for my search results page I did this:

if request("validate")=1 then
strsearchfield=request("searchfield")
strWhere="WHERE " & _
Request.Form("searchfield") & "='Yes' GROUP BY ID, CompanyName, City, State, Zip"

strSQL = "SELECT COUNT(CompanyName) as resultcount FROM Vendors WITH (NOLOCK) WHERE " & _
Request.Form("searchfield") & "='Yes' GROUP BY ID, CompanyName, City, State, Zip"
Set rsCount = conn.Execute(strSQL)
ReportOnError("Error")

End If

' Retrieve the rows from the database for the users choice
'
Server.Scripttimeout = 64000

strSQL = "SELECT ID, CompanyName, City, State, Zip from Vendors " & strWhere
cmdTemp.commandtext = strSQL
set rsSearch = cmdTemp.execute
ReportOnError("Error Selecting")


%>

<%

do while not rsSearch.eof

strCompanyName = rsSearch("CompanyName")

if isnull (strCompanyName) then
strCompanyName = "&nbsp;"
end if


' Display the rows ' %>



Company Name: <%=rsSearch("CompanyName")%><BR>
(<%=rsSearch("City")%>) <BR>

<%=rsSearch("State")%><BR>

<%=rsSearch("Zip")%><BR>

<A HREF="vendor_info.asp?ID=<%=rsSearch("ID")%>">view</a><BR>