Multiple select List

[Crystal_Rouse]

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

[bregent]

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?

[Crystal_Rouse]

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

[bregent]

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

[Crystal_Rouse]

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!

[bregent · Accepted solution]

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.

[Crystal_Rouse]

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

[bregent]

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.

[Crystal_Rouse]

THANKS!!

[Crystal_Rouse]

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

[bregent]

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.