Multiple selections in drop down list (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

RRogers123 -> Multiple selections in drop down list (3/8/2002 19:54:45)

I've seen some posts on this but I can't get it to work. I have a drop down list populated using DRW and multiple selections are allowed. If multiple items are selected, the query returns no records because no records match the whole string. How do you break it up into individual items in the SQL query? Thanks.

 



Spooky -> RE: Multiple selections in drop down list (3/8/2002 23:17:21)

The trick is to use "IN" as a query.
It does really demand specific column values, as you cant do a LIKE IN()

So ideally the values will be short text or numeric.

The numeric syntax is "....where column IN (1,2,3,4,5) "
Text : "....where column IN ('a','b','c','d') "

The DRW syntax would then be :
"....where column IN (::Input::)"

Now, the problem lies in passing text. Normally a multiple text box with text values will pass a comma delimited string.
While this is great for numbers, it is not good for text.

So what we need to do, is fool the DRW by enclosing our text VALUES in quotes.

<option value=" ' Text ' ">Option Text</option>

When a user selects multiple text items, they are passed to the DRW with quotes, all ready for the IN query.


§þððk¥
Database / DRW Q & A
VP-ASP Shopping cart
Spooky Login



RRogers123 -> RE: Multiple selections in drop down list (3/9/2002 9:23:18)

Thanks for your reply, but I still don't quite get it. My query looks like this:
SELECT * FROM Databasename WHERE (Week = '::Week::' AND Grade = '::Grade::' AND CourseNo = '::CourseNo::')
The week and the grade are static drop down lists from which only one item is selected. The CourseNo is also a dropdown list box populated using DRW with grade in the query. Multiple items can be selected for CourseNo. After the user selects the week and grade, the CourseNo listbox is filled with the appropriate values. The CourseNo are actually numbers but are defined as text in the Access database. Does IN replace the = operator? How do I modify the SQL? Thanks for your help.




Spooky -> RE: Multiple selections in drop down list (3/9/2002 21:35:48)

Reading between the lines ....

"....CourseNo IN (::CourseNo::))"

You would need to ensure, if its a text field, that the DRW is 'tricked' as I suggested using quotes in the list values.


§þððk¥
Database / DRW Q & A
VP-ASP Shopping cart
Spooky Login



RRogers123 -> RE: Multiple selections in drop down list (3/10/2002 12:38:40)

Thanks. I got it to work.




R Clark -> Spooky?: Multiple selections in drop down list (4/26/2005 12:16:10)

Spooky,
I've got the scenario in the above post working for my page (thanks). I'd just like to do one more thing and that is retain the selections after submitting the query.
Typically I would use something like... 
<option  value="'<%=FP_FieldHTML(fp_rs,"ata")%>'" <%if request.form("atadd") = fp_fieldhtml(fp_rs,"ata") then response.write "selected"%>>ATA <%=FP_FieldHTML(fp_rs,"ata")%></option>

The single quotes around the 'value' in the line of code are additional for the scenario this post originally addressed, so I don't know if that is affecting the statement and its ability to retain the selections (in a single select dd I don't have the single quotes and it would retain the selection) or...
maybe you can't retain multiple selections (easily anyway) in a 'select multiple' situation?

I sure hope I'm making sense!
many thanks!
Randy



BeTheBall -> RE: Spooky?: Multiple selections in drop down list (4/26/2005 12:35:51)

See if this works:
<option value="'<%=FP_FieldHTML(fp_rs,"ata")%>'" <%if Instr(request.form("atadd"), fp_fieldhtml(fp_rs,"ata")) then response.write " selected"%>>ATA <%=FP_FieldHTML(fp_rs,"ata")%></option>[/code]



R Clark -> RE: Spooky?: Multiple selections in drop down list (4/26/2005 12:45:08)

Works perfectly! [image]http://www.frontpagewebmaster.com/image/s2.gif[/image]

To educate myself (because I'm very much about learning this, not just copying and pasting your wisdom), whats the instr and the comma and the space before selected doing? (I think I caught everything you did)

again! many thanks!
Randy



rdouglass -> RE: Spooky?: Multiple selections in drop down list (4/26/2005 14:19:18)

quote:

whats the instr and the comma and the space before selected doing?


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctinstr.asp



BeTheBall -> RE: Spooky?: Multiple selections in drop down list (4/26/2005 15:23:54)

Instr is a function.  The first value after the function is the string to be searched.  The value after the comma is the value you are looking for.  It is a true/false type argument.  For example:

Instr("Hello World","Hello") would be true.

The space in front of selected is necessary because without it ASP will write out optionselected instead of option selected.

Is that any clearer?



R Clark -> RE: Spooky?: Multiple selections in drop down list (4/26/2005 15:35:56)

yes, Thank you both!



rdouglass -> RE: Spooky?: Multiple selections in drop down list (4/26/2005 16:07:42)

quote:

Instr("Hello World","Hello") would be true.


Actually, I believe it would return 1.  Instr returns an integer based on the position in the string where the searched-for-text is found first.  If not found, it returns a 0. So this:

Instr("Hello World","o")

would return 5 and never 8.



BeTheBall -> RE: Spooky?: Multiple selections in drop down list (4/26/2005 16:58:43)

quote:

ORIGINAL: rdouglass

quote:

Instr("Hello World","Hello") would be true.


Actually, I believe it would return 1.  Instr returns an integer based on the position in the string where the searched-for-text is found first.  If not found, it returns a 0. So this:

Instr("Hello World","o")

would return 5 and never 8.


I stand corrected.  rdouglass' analysis is 100% accurate.  I guess my point was that if you for example use this:

<%
If Instr("Hello World","Hello") Then
Response.write("True")
Else
Response.Write("False")
End If
%>

It will write "True"

On the other hand, this:

<%
If Instr("Hello World","Jello") Then
Response.write("True")
Else
Response.Write("False")
End If
%>

Will write "False"



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625