Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Multiple select search results

  • 01-02-2013 9:40am
    #1
    Registered Users, Registered Users 2 Posts: 44


    Hi,

    I have a multiple select drop down menu that Im running a search from. If I select 1 item from the dropdown and hit submit, it brings back all the items associated with my selection, however if i select more than one item, it brings back no results. Anyone an idea what i should to fix this issue? here is my code, Im using asp vbscript:( and reading from a sql server db


    page 1
    [HTML]<form name="form1" method="get" action="searchres.asp">
    <table width="50%" border="0">
    <tr>
    <td width="23%"> </td>
    <td width="77%"> </td>
    </tr>
    <tr>
    <td>Category</td>
    <td><select name="CatID" size="10" multiple id="CatID">
    <%
    While (NOT rsCategory.EOF)
    %>
    <option value="<%=(rsCategory.Fields.Item("CatID").Value)%>"><%=(rsCategory.Fields.Item("Category").Value)%></option>
    <%
    rsCategory.MoveNext()
    Wend
    If (rsCategory.CursorType > 0) Then
    rsCategory.MoveFirst
    Else
    rsCategory.Requery
    End If
    %>[/HTML]


    ===============
    and results page

    [HTML]<%

    Dim rsSearchResults__MMColParam1
    rsSearchResults__MMColParam1 = "%"
    If (Request.QueryString("CatID") <> "") Then
    rsSearchResults__MMColParam1 = Request.QueryString("Catid")
    End If
    %>


    <%
    Dim rsSearchResults
    Dim rsSearchResults_cmd
    Dim rsSearchResults_numRows

    Set rsSearchResults_cmd = Server.CreateObject ("ADODB.Command")
    rsSearchResults_cmd.ActiveConnection = MM_DBCCONNECTION_STRING
    rsSearchResults_cmd.CommandText = "SELECT * FROM dbo.qryALLClients where CatID LIKE ? order by contact_name ASC"
    rsSearchResults_cmd.Prepared = true
    rsSearchResults_cmd.Parameters.Append rsSearchResults_cmd.CreateParameter("param1", 201, 1, 50, rsSearchResults__MMColParam1) ' adLongVarChar


    Set rsSearchResults = rsSearchResults_cmd.Execute
    rsSearchResults_numRows = 0
    %>


    <%
    Dim Repeat1__numRows
    Dim Repeat1__index

    Repeat1__numRows = 2000
    Repeat1__index = 0
    rsSearchResults_numRows = rsSearchResults_numRows + Repeat1__numRows
    %>[/HTML]


Comments

  • Registered Users, Registered Users 2 Posts: 7,501 ✭✭✭BrokenArrows


    I think its your select query. I dont do asp vbscript so you may need to fill in the blanks.
    SELECT * FROM dbo.qryALLClients where CatID LIKE ? order by contact_name ASC
    

    I assume the ? mark gets replaced with the selections you choose on the previous page.

    So when you choose one option it becomes something like:
    SELECT * FROM dbo.qryALLClients where CatID LIKE "choiceone" order by contact_name ASC
    

    When you choose more than one option it becomes:
    SELECT * FROM dbo.qryALLClients where CatID LIKE "choiceonechoice2" order by contact_name ASC
    

    You need it to be:
    SELECT * FROM dbo.qryALLClients where CatID LIKE "choice one" or CatID like "choice two" order by contact_name ASC
    

    Or a cleaner way might be:
    SELECT * FROM dbo.qryALLClients where CatID in ("choice one", "choice two") order by contact_name ASC
    


  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    The above but a few pointers.

    In general using Select * is not a good idea. Better to just select the columns you need.

    Using OR can be performance intensive on your db. Depending on what you are doing you may better off writing single SQL statements and using UNION.


  • Registered Users, Registered Users 2 Posts: 44 MikeFantana


    Hi All,

    OK I have tried all your suggestions but its still not working for me :(

    Here is my sad code again. Any other suggestions?????

    <%

    Dim rsSearchResults__MMColParam1
    rsSearchResults__MMColParam1 = "%"
    If (Request.QueryString("CatID") <> "") Then
    rsSearchResults__MMColParam1 = Request.QueryString("Catid")
    End If
    %>


    <%
    Dim rsSearchResults
    Dim rsSearchResults_cmd
    Dim rsSearchResults_numRows

    Set rsSearchResults_cmd = Server.CreateObject ("ADODB.Command")
    rsSearchResults_cmd.ActiveConnection = MM_DB_STRING
    rsSearchResults_cmd.CommandText = "SELECT * FROM dbo.qryALLClients where CatID LIKE ? order by contact_name ASC"
    rsSearchResults_cmd.Prepared = true
    rsSearchResults_cmd.Parameters.Append rsSearchResults_cmd.CreateParameter("param1", 201, 1, 50, rsSearchResults__MMColParam1) ' adLongVarChar


    Set rsSearchResults = rsSearchResults_cmd.Execute
    rsSearchResults_numRows = 0
    %>

    =============



    declare @P1 int
    set @P1=1
    exec sp_prepare @P1 output, N'@P1 text', N'SELECT * FROM dbo.qryALLClients where CatID LIKE @P1 order by contact_name ASC', 1
    select @P1

    exec sp_execute 1, '%'

    ==========


Advertisement