Hello everyone,

I am really having hard time to retrieve data from database using multiple list box. Here is the my simplified version of codes that you may want to take look...

<select NAME="Supplier" SIZE=4 MULTIPLE >
<%While NOT rsMain.EOF%>
<option value=<%=rsMain("Supplier Name")%>><%=rsMain("Supplier Name")%></option>
<%rsMain.MoveNext()
Wend%>
</select>


<INPUT TYPE="BUTTON" NAME="open" VALUE="SUBMIT" ONCLICK="outputSelected(this.form.Supplier.options )">

<SCRIPT LANGUAGE="JavaScript">

   function getSelected(opt) {
      var selected = new Array();
      var index = 0;
      for (var intLoop=0; intLoop < opt.length; intLoop++) {
         if (opt[intLoop].selected) {
            index = selected.length;
            selected[index] = new Object;
            selected[index].value = opt[intLoop].value;
            selected[index].index = intLoop;
         }
      }
      return selected;
   }

         function outputSelected(opt) {
            var sel = getSelected(opt);
            var strSel = "";
            for (var item in sel)
            //alert (sel[item].value)
               strSel += sel[item].value + "\n";
               alert("Selected Items:\n" + strSel);
            window.location='SupplierFilterTest.asp?Supp=' + strSel;

         }
      </SCRIPT>

<%SSupp= Request("Supp")%>

strOpenPO= "SELECT Count(PONUMBER) AS OpenPos FROM MyTable WHERE SUPPLIER ='" &SSupp& "'"

When I take a look inside SSupp, all variables are appending each other without any space.

When I select only one value from list box, my code is working fine. But whenever I select more than one, I get nothing.

Any help would be appreciated,

Thanks,

-Tulin

this is more a ASP issue, try posting this in the classic ASP forum.

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

Hi Tulin,

If your SUPPLIER field in the db is an integer, you should be able to do this:
HTH,

Chris

My Supplier field is string...

Even though I tried the following query I got nothing.

strOpenPO= "SELECT Count(PONUMBER) AS OpenPos FROM MyTable WHERE SUPPLIER IN('" & SSupp & "');"

It works fine if I select only one value from list box. My problem is that if I select more than one value...

Thanks.

Life would be easier if you stuck to the method of having an integer ID for the supllier in the option value and the name as the text.
If you must stick to a string for both then:

• Add quotes to your ASP otherwise if a supplier has a space your HTML will be invalid
  Code:

  <option value="<%=rsMain("Supplier Name")%>">

• If you post a page with a multiple select the values appear as a comma delimited string in the Request.Form data, e.g: Supplier=Microsoft,IBM,Cisco. For your query to work you need to add single quotes to each supplier. Try this function:
  Code:

  'Turns "Microsoft,IBM,Cisco" into "'Microsoft','IBM','Cisco'"
  Function GetSqlList(Items)
    Dim sResult
    Dim iIndex
    Dim iCount
    Dim arrItems
    arrItems = Split(Items, ",")
    iCount = UBound(arrItems)
    For iIndex = 0 To iCount 
      sResult = sResult & "'" & arrItems(iIndex) & "'"
      If iIndex <> iCount Then
        sResult = sResult & "," 
      End If
    Next
    GetSqlList = sResult
  End Function

--

Joe (Microsoft MVP - XML)