• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

Selecting Multiple items from a list box

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
rashida
Asked:
rashida
1 Solution
 
freshmeatCommented:
wow, good question, sit with u together, waiting for the answer...
ZZzz....
:)
0
 
bagi112599Commented:
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
 
pure032398Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
pure032398Commented:
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
 
jbathijaCommented:
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
 
rashidaAuthor Commented:
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
 
robbertCommented:
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
 
pure032398Commented:
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
 
Steve SiricaSenior Web Application EngineerCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now