10 Replies Latest reply: Nov 9, 2011 1:30 PM by bregent RSS

    Multiple select List

    Crystal_Rouse Community Member

      I have a web form that I need to allow the users to select multiple items from a list.  I have 'googled' and can not find an answer.

      The query has 2 parameters. 

       

      <%

      Dim HoursWorked_PP__varTI

      HoursWorked_PP__varTI = "%"

      If (Request.QueryString("TI") <> "") Then

        HoursWorked_PP__varTI = Request.QueryString("TI")

      End If

      %>

      <%

      Dim HoursWorked_PP__varPP

      HoursWorked_PP__varPP = "%"

      If (Request.QueryString("PP") <> "") Then

        HoursWorked_PP__varPP = Request.QueryString("PP")

      End If

      %>

       

      <%
      Dim HoursWorked_PP
      Dim HoursWorked_PP_cmd
      Dim HoursWorked_PP_numRows

      Set HoursWorked_PP_cmd = Server.CreateObject ("ADODB.Command")
      HoursWorked_PP_cmd.ActiveConnection = MM_Connection_STRING
      HoursWorked_PP_cmd.CommandText = "SELECT * FROM dbo.vw_HoursWorked WHERE COLUMN_NAME = ? and PP = ?

      HoursWorked_PP_cmd.Prepared = true
      HoursWorked_PP_cmd.Parameters.Append HoursWorked_PP_cmd.CreateParameter("param1", 200, 1, 255, HoursWorked_PP__varTI) ' adVarChar
      HoursWorked_PP_cmd.Parameters.Append HoursWorked_PP_cmd.CreateParameter("param2", 200, 1, 255, HoursWorked_PP__varPP) ' adVarChar

      Set HoursWorked_PP = HoursWorked_PP_cmd.Execute
      HoursWorked_PP_numRows = 0
      %>

       

      From my searches, I have tried:

      <%
      WorkedDate = Request.form("WorkedPP") ' Convert the info from the form to a string

      MakeDate = Replace(WorkedDate, ", ", "' OR WorkedPP = '") 'Replace comma-space to a single quote - OR WorkedPP = -singlequote
      %>


      <%
      Dim HoursWorked_PP
      Dim HoursWorked_PP_cmd
      Dim HoursWorked_PP_numRows

      Set HoursWorked_PP_cmd = Server.CreateObject ("ADODB.Command")
      HoursWorked_PP_cmd.ActiveConnection = MM_Connection_STRING
      HoursWorked_PP_cmd.CommandText = "SELECT * FROM dbo.vw_HoursWorked_PP_Sum WHERE COLUMN_NAME = ? and PP = '"&MakeDate&"' "
      HoursWorked_PP_cmd.Prepared = true
      HoursWorked_PP_cmd.Parameters.Append HoursWorked_PP_cmd.CreateParameter("param1", 200, 1, 255, HoursWorked_PP__varTI) ' adVarChar
      HoursWorked_PP_cmd.Parameters.Append HoursWorked_PP_cmd.CreateParameter("param2", 200, 1, 255, HoursWorked_PP__varPP) ' adVarChar

      Set HoursWorked_PP = HoursWorked_PP_cmd.Execute
      HoursWorked_PP_numRows = 0
      %>

       

       

      Any help or a point in the right direction?  I'm not sure but I can't find a solution to allow multiple select lists.  If I change the query to use IN, then the URL string still tried to use 'AND' so the results are null.

       

      Thank You for any help!

      Crystal

        • 1. Re: Multiple select List
          bregent CommunityMVP

          Please tell us more about the nature of the data in the select list. Which of the two parameters in your query expects more than one value? Do you really have a column in your database called COLUMN_NAME?

          • 2. Re: Multiple select List
            Crystal_Rouse Community Member

            No, I don't have a column called COLUMN_NAME (that is just a placeholder for the real name).

            The parameter that expects more than one value is the second one 'PP'.

             

            The original recordset says:  and PP = ?

            I changed it to:  and PP = '"&MakeDate&"' "

            this still does not work.

             

            I tried using 'IN' in the SQL query but it doesn't work either.

             

            Crystal

            • 3. Re: Multiple select List
              bregent CommunityMVP

              >No, I don't have a column called COLUMN_NAME

              >(that is just a placeholder for the real name).

               

              OK, it's difficult to troubleshoot scripts when the code posted is not exactly the way it is written.

               

              >The parameter that expects more than one value is the second one 'PP'.

               

              What is the datatype of PP?

               

              >I changed it to:  and PP = '"&MakeDate&"' "

               

              Your attempt to change MakeDate using Replace() won't work as written as you are inserting the string value ' OR WorkedPP = '. And why are you putting ampersands around MakeDate? Is PP a date datatype?

               

              >I tried using 'IN' in the SQL query but it doesn't work either.

               

              That's your best bet. The IN predicate expects a comma seperated list of quoted values. But a select list only returns a comma separated list of values, so you need to modify it to place single quotes around each value in the list.

              • 4. Re: Multiple select List
                Crystal_Rouse Community Member

                The datatype of the variable PP is text. 

                I reall don't know what I'm doing - found some by googling and tried it.

                 

                To change the original recordset:

                SELECT *

                FROM dbo.vw_HoursWorked_PP_Sum

                WHERE COLUMN_NAME = varTI and PP = varPP

                ORDER BY EmployeeName, COLUMN_ID

                 

                to use the IN clause

                 

                SELECT *

                FROM dbo.vw_HoursWorked_PP_Sum

                WHERE COLUMN_NAME = varTI and PP IN ('varPP')

                ORDER BY EmployeeName, COLUMN_ID

                 

                still does not work.  Not sure what I'm doing wrong.

                 

                Thanks for your help!

                • 5. Re: Multiple select List
                  bregent CommunityMVP

                  WHERE COLUMN_NAME = varTI and PP IN ('varPP')

                   

                  No, what I meant was that you need to parse varPP and wrap quotes around all of the values inside it. You also need to put parens around the whole thing. So this:

                   

                  "option1, option2, option3" that comes from the select list would be converted to this:

                   

                  " ('option1' , 'option2', 'option3') "

                   

                  I've posted code here a few years ago to accomplish this, but I don't have the routine handy. See if you can search for it.

                  • 6. Re: Multiple select List
                    Crystal_Rouse Community Member

                    OK, Thank You.  I will search for it.  I tried searching for multiple select lists and couldn't find anything.  I'll keep trying...

                    • 7. Re: Multiple select List
                      bregent CommunityMVP

                      OK, I found it here:

                       

                      http://forums.adobe.com/message/2352656#2352656

                       

                      In this example, we changed the values from the checkboxes to have single quotes around them already, making it unnecessary to parse and embed the single quotes in a function. You should be able to do the same with your select list.

                      • 8. Re: Multiple select List
                        Crystal_Rouse Community Member

                        THANKS!!

                        • 9. Re: Multiple select List
                          Crystal_Rouse Community Member

                          I finally figured this out and thought I would post some code for other users that may need to do this.  I could not find any directions on how to use a mutli-select list.

                           

                           

                           

                          <%

                           

                          Dim HoursWorked_PP__varPP

                           

                          Dim HoursWorked_PP__varPP2

                           

                          HoursWorked_PP__varPP = "*"

                           

                          If (Request.QueryString("PP")  <> "") Then

                           

                            HoursWorked_PP__varPP = Request.QueryString("PP")

                           

                            Dim tokens

                           

                            tokens = split(HoursWorked_PP__varPP, ",")

                           

                             

                           

                            For Each x in tokens

                           

                                          HoursWorked_PP__varPP2 = HoursWorked_PP__varPP2 & "'" & Trim(x) & "',"                                                 

                           

                            Next 

                           

                            HoursWorked_PP__varPP2 = Left(HoursWorked_PP__varPP2,Len(HoursWorked_PP__varPP2)-1) 'Trim off the last comma

                           

                          '  Response.Write(HoursWorked_PP__varPP2)

                           

                          End If

                           

                          %>

                           

                          <%

                           

                          Dim HoursWorked_PP

                           

                          Dim HoursWorked_PP_cmd

                           

                          Dim HoursWorked_PP_numRows

                           

                           

                           

                          Set HoursWorked_PP_cmd = Server.CreateObject ("ADODB.Command")

                           

                          HoursWorked_PP_cmd.ActiveConnection = MM_ConnectionString_STRING

                           

                          HoursWorked_PP_cmd.CommandText = "SELECT * FROM vw_HoursWorked_PP_Sum WHERE COLUMN_NAME = ? AND PP IN (" & HoursWorked_PP__varPP2 & ")"

                           

                          HoursWorked_PP_cmd.Prepared = true

                           

                          HoursWorked_PP_cmd.Parameters.Append HoursWorked_PP_cmd.CreateParameter("param1", 200, 1, 255, HoursWorked_PP__varTI) ' adVarChar

                           

                          'HoursWorked_PP_cmd.Parameters.Append HoursWorked_PP_cmd.CreateParameter("param2", 200, 1, 255, HoursWorked_PP__varPP2) ' adVarChar

                           

                           

                           

                          Set HoursWorked_PP = HoursWorked_PP_cmd.Execute

                           

                           

                           

                          HoursWorked_PP_numRows = 0

                           

                           

                          • 10. Re: Multiple select List
                            bregent CommunityMVP

                            That will certainly work fine. But another method is the one I already mentioned - just put single quotes around each data value in the form. Then you don't need to split the fields and loop through the array.