Solved

Selecting Multiple items from a list box

Posted on 2000-04-10
9
446 Views
Last Modified: 2008-03-10
I've an ASP page where I'd like the user to select multiple items from a list box. I'm using HTML SELECT ... MULTIPLE> Tag. I'd then like to use an SQL query to select items based on the selected choices (ex. SELECT * WHERE ITEM IN ('..',',..'), etc.
HOW/WHERE SHOULD I STORE THE SELECTED VALUES. I ALSO WANT TO MARK/HIHLIGHT THE SELECTED CHOICES AND ALlOW THE USER TO UN-SELECT AN ITEM.
0
Comment
Question by:rashida
9 Comments
 
LVL 2

Expert Comment

by:freshmeat
ID: 2702643
wow, good question, sit with u together, waiting for the answer...
ZZzz....
:)
0
 
LVL 2

Expert Comment

by:bagi112599
ID: 2702670
rashida,
who is selecting options from select?
user or you(based on other options user specify)?
as per your last question this functionality is in select control itself already.(hold down ctrl key)
0
 
LVL 1

Expert Comment

by:pure032398
ID: 2702680
One sec..

i'm doing it now..

I'm nearly finished the second page...
so bare with me for aronud 10 more mins.

(had to write / test the entire thing out from scratch)...

-PK-
0
 
LVL 1

Expert Comment

by:pure032398
ID: 2702701
Ok here we go..

1) This answer is divided into 2 parts.
   question.asp and answer.asp
   Question.asp = Multi-dropdown
   Answer = SQL intergration

2) This has not been tested for syntax. The functionality looks ok, which was my main aim.

3) The multi-dropdown was hardcoded. It should been database driven, but that's not the point here, and it would have made it harder to understand Answer.asp if you couldn't see some drop down data.


4) The idea / concept was a database of fruits (first thing that came to mind).

5) TAKE NOTE on coding the multi-drop down items that are HIGHLIGHTED - i just told the HTML that they were SELECTED.

Enjoy.

Hope this helps and i'm 200 points richer.
---------------------------------------
Question.asp

<html>
<head>
      <title>MultileBox Test Page</title>
</head>

<body>

Please select 1 / multiple items from the list below, to view their information.<br>
<br>

<form action="Mult_Results.asp" method="POST">
<table border="0">
<tr>
      <td>
            <select name="MDD_Fruits" size="10" multiple>
                  <option value="1" selected>Apples
                  <option value="2">Bannanas
                  <option value="3">Pears
                  <option value="4" selected>Oranges
                  <option value="5" selected>Mangoes
                  <option value="6">Kiwi Fruit
                  <option value="7">Grapes
            </select>
</td>
<td valign="top"><input type="submit" value="go"></td>
</tr>
</table>
</form>


</body>
</html>


------------------------------------
Answer.asp

<% Option Explicit %>
<%
'      File : Mult_Results.asp
'      Date : Tuesday 11th April, 2000
'      Description : lists all the fruits' information, dependant upon their previous selection


'      Variables
'      ^^^^^^^^^
      Dim rsUse
      Dim strSQL
      Dim Item
      Dim boolFirstProduct : boolFirstProduct = True
%>


<html>
<head>
      <title>MultileBox Test Page</title>
</head>

<body>

<%
      
'      Create the SQL query
'      There are two different methods to do this, dependant if there are multiple tables
'      to link or not.

'      Method 1) Multiple SQL tables to link
      strSQL = "SELECT      a.Fruit, a.Price, Season, a.Cals, a.Energy " &_
                              "FROM      P_Fruits a, P_Prices b " &_
                              "WHERE a.FruitID = b.FruitID "
      For Each Item in Request.Form
            strSQL = strSQL & "AND      a.FruitID = "& request.form(Item) & " "
      Next
      
      strSQL = strSQL & "ORDER BY a.Fruit "
      
      

'      Method 2) One SQL Table where data is retrieved from.
      strSQL = "SELECT      a.Fruit, a.Price, Season, a.Cals, a.Energy " &_
                              "FROM      P_Fruits a, P_Prices b "
      For Each Item in Request.Form
            If boolFirstProduct = True Then
                  boolFirstProduct = False
                  strSQL = strSQL & "Where      a.FruitID = "& request.form(Item) & " "
            Else
                  strSQL = strSQL & "AND      a.FruitID = "& request.form(Item) & " "
            End If
      Next
      strSQL = strSQL & "ORDER BY a.Fruit "
      
      
      
      
      Set rsUse = CreateObject("ADODB.Recordset")
      rsUse.Open strSQL, "DSN=MSSQL"

      
      
      
      If rsUse.EOF Then
            response.write "No fruits where found"
      Else
%>            
<table border="0">
<tr>
      <td>Fruit</td>
      <td>Price</td>
      <td>Season</td>
      <td>No. Calories</td>
      <td>No. Energy</td>
</tr>
<%
            Do While NOT rsUse.EOF
%>
<tr>
      <td><%=rsUse("fruit")%></td>
      <td><%=rsUse("price")%></td>
      <td><%=rsUse("season")%></td>
      <td><%=rsUse("cals")%></td>
      <td><%=rsUse("energy")%></td>
</tr>
<%            
                  rsUse.MoveNext
            Loop
%>
</table>


</body>
</html>
<%

'      Clean up the DB Connections
      rsUse.Close
      Set rsUse = Nothing
%>

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Accepted Solution

by:
jbathija earned 200 total points
ID: 2703025
Consider this source code as your htm form page
HTML Page


'multiselect.htm'
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<form action = submit.asp method = post>
<P>
<SELECT id=select1 name=select1 size=2 style="HEIGHT: 134px; WIDTH: 177px" multiple = true>
      <OPTION value = 1>A</OPTION>
      <OPTION value = 2>B</OPTION>
      <OPTION value = 3>C</OPTION>
      <OPTION value = 4>D</OPTION>
      <OPTION value = 5>E</OPTION>
</SELECT></P>
<P><INPUT id=submit1 name=submit1 type=submit value=Submit></P>
</form>

</BODY>
</HTML>


The corresponding ASP Page is

'submit.asp'
<%@Language = VBScript%>
<%Option Explicit%>
<%
Dim item
Dim sSql
sSql = "Select * From TableName where FieldName in ("
if Request.Form("select1") <> "" then
      sSql = sSql &  Request.Form("select1")  & ")"
end if      
Response.Write sSql
%>


Check this code
0
 

Author Comment

by:rashida
ID: 2704169
For Pure & jbathija:

 I used some of that code and here's my problem.

 1. It's not truely 'multi-select'. When I select the second item, the first item is automatically unselected. the value of REQUEST.FORM("ITEM") is always the last item selected. In other words, I always get the last item selected in REQUEST.FORM("ITEM").
How do I fix that?

2. When I hold the CTL button down, then I can select multiple items.  However, the value of REQUEST.FORM("ITEM") IS FIRST VALUE, SECOND VALUE.
I can't access the individual items in FOR NEXT loop. For SQL statement:
 .. WHERE * IN('FIRST VALUE','SECOND VALUE'), I need to be able to access each individual value and put ' around it.

 Please Help!!
0
 
LVL 15

Expert Comment

by:robbert
ID: 2704955
The result of a multiple select box is values, being separated by ", ".

----------------------------------------

strTmp = Request.Form("MultipleSelectBox")
arrTmp = Split(strTmp, ", ")
For i = 0 To UBound(arrTmp) - 1
    Response.Write arrTmp(i)
Next

----------------------------------------

Generating the SQL statement from here:

strTmp = Request.Form("MultipleSelectBox")
arrTmp = Split(strTmp, ", ")

strSQ = "SELECT * FROM myTable"

blnFirstLoop = True

For i = 0 To UBound(arrTmp) - 1
    If blnFirstLoop Then
        strSQ = strSQ & " WHERE"
        blnFirstLoop = False
    Else: strSQ = strSQ & " OR"
    End If

    strSQ = strSQ & " myColumn LIKE '" & arrTmp(i) & "'"
Next
0
 
LVL 1

Expert Comment

by:pure032398
ID: 2709169
Robbert's way is dodgy i think, IF the VALUES contain comma's in them. This could be the case if you have a dynamically constructed Drop Down Box, and the values are not numbers, but text.

*try to make code re-useable / generic, so you code LESS in the long run*  [ROBUST CODE]

His way does what i've done, but in a different method. (a not so SAFE method)


FIRSTLY, please make sure u understand what you are TRYING TO DO, and therefore the SYMANTECTS of it.

1) U *MUST* use the CTRL key to MULT-SELECT.
2) YOU do *NOT* REQUEST.FORM("ITEM"). That *DOES* what u said, lists all the SELECTED items, delimeted by a a COMMA.

read what i did.

you MUST do it THIS WAY

For Each Item in Request.Form

<some stuff>

Next

That way, the code KNOWS what each selection is, comma or not.

It's the CLEANER and BETTER way.
No offense jbathija, but you're wrong mate. Your answer should be rejected.

try doing this

For Each Item in Request.Form
   resposne.write "Selected value is [" & Item & "]<br>"
Next

Trust me. you'll be surprised.

That is the TRICK to MULTI-Drop down And ARRAYS in ASP

the FOR EACH <variableName> IN Request.Form | Request.Session
....
Next

***TRUST ME***

Good Luck, and keep us posted.

-PK-
0
 
LVL 4

Expert Comment

by:Steve Sirica
ID: 2744151
I don't know if you've solved this, but the question was still open and I just figured this out myself.

If you want to use java and not have to resubmit the form here's a good way to get the selected values from a multi-select listbox:

<html>
<body>
<SELECT id=Listbox size=6 multiple>
    <option value="A">A Entry</option>
    <option value="B">B Entry</option>
    <option value="C">C Entry</option>
</select>
<input type="submit" value="Show Vals" id=cmdShow onClick="ShowVals();">

</body>
</html>

<SCRIPT LANGUAGE=javascript>
<!--
function ShowVals()
{
for (i=0; i<Listbox.length; i++)
    {
    if (Listbox.item(i).selected)
        {
        //your selected values are:
        //Listbox.item(i).value
      alert(Listbox.item(i).value);
      }
    }
}
-->
</SCRIPT>

Maybe on a button click you want to retrieve the values.  Just point the buttons "OnClick" event at the function, and I don't know maybe put the value in an array or something.

Hope this helps.
Steve
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now