#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    2
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    Las Vegas
    Posts
    12
    Rep Power
    0
    I think you should send the option values the fieldnames from your tables:

    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>
    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.

    Code:
    sqlquery = "SELECT CompanyName FROM Vendors WITH (NOLOCK) WHERE " & _
    Request.Form("searchfield") & "='Yes' ORDER BY CompanyName ASC"
    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.
    Last edited by chichian; February 10th, 2004 at 10:13 PM.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    2
    Rep Power
    0

    Thanks!


    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>

IMN logo majestic logo threadwatch logo seochat tools logo