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

Hii,
 Use
strOpenPO= "SELECT Count(PONUMBER) AS OpenPos FROM MyTable WHERE SUPPLIER in (" & SSupp & ")"

since ur below query expects SUPPLIER=VALUE
and you need 'in' or 'exists' calause so that it can retrieve all other values in the list
(
strOpenPO= "SELECT Count(PONUMBER) AS OpenPos FROM MyTable WHERE SUPPLIER ='" &SSupp& "'")


Hope this will help you





Cheers :)

vinod

Hi,

Not clear on what you're trying to do here.

Are you trying to post to another page and count PO's for muliple supplier's?

Hii Rstelma!!

Suppose A,B,C are list of suppliers in ur listbox

Check the query (when u select only one supplier)
SELECT Count(PONUMBER) AS OpenPos FROM MyTable WHERE SUPPLIER ='A'

and
when u r selecting multiple values,ur queriy will be
SELECT Count(PONUMBER) AS OpenPos FROM MyTable WHERE SUPPLIER ='A,B,C'
offcourse above query wont result wht u want.

so u can use below one

SELECT Count(PONUMBER) AS OpenPos FROM MyTable WHERE SUPPLIER in(A,B,C)

Hope this will help you


now hope u got the clue.


Cheers :)

vinod

Yes, it works finally...

But I have been experiencing different problem. There is a special characters (eg &, #, ?) in the SSupp variables(eg Tech&Lease). How can I get these values without error.

Thanks,

Can you post the errors you are getting and the offending code?

Cheers,

Chris

Here is the error:
------------------------------------
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'SUPPLIER IN ()'.
------------------------------------
I am getting this error when I select the value(s) with special characters(e.g. Tech&Lease).

Please post the query you are trying to run

"SELECT Count(PONUMBER) AS OpenPos FROM [qry PO_data calculated fields final] WHERE SUPPLIER IN (" &SSupp& ")"

If you are using an in clause on a string field, you need to surround the criteria in quotes, so your query should look something like:
If you are just searching for one supplier, you could just use: