|
Subject:
|
passing values from multiple list box
|
|
Posted By:
|
tulincim
|
Post Date:
|
9/12/2005 5:34:25 PM
|
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
|
|
Reply By:
|
vinod_yadav1919
|
Reply Date:
|
9/19/2005 4:42:39 AM
|
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
|
|
Reply By:
|
rstelma
|
Reply Date:
|
9/19/2005 3:21:53 PM
|
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?
|
|
Reply By:
|
vinod_yadav1919
|
Reply Date:
|
9/21/2005 1:52:28 AM
|
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
|
|
Reply By:
|
tulincim
|
Reply Date:
|
9/28/2005 3:59:28 PM
|
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,
|
|
Reply By:
|
ChrisScott
|
Reply Date:
|
9/29/2005 3:24:22 AM
|
Can you post the errors you are getting and the offending code?
Cheers,
Chris
|
|
Reply By:
|
tulincim
|
Reply Date:
|
9/29/2005 8:13:17 AM
|
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).
|
|
Reply By:
|
ChrisScott
|
Reply Date:
|
9/29/2005 8:30:28 AM
|
Please post the query you are trying to run
|
|
Reply By:
|
tulincim
|
Reply Date:
|
9/29/2005 8:58:27 AM
|
"SELECT Count(PONUMBER) AS OpenPos FROM [qry PO_data calculated fields final] WHERE SUPPLIER IN (" &SSupp& ")"
|
|
Reply By:
|
ChrisScott
|
Reply Date:
|
9/29/2005 9:06:26 AM
|
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:
SELECT Count(PONUMBER) AS OpenPos FROM [qry PO_data calculated fields final] WHERE SUPPLIER IN ('Tech&Lease');
If you are just searching for one supplier, you could just use:
SELECT Count(PONUMBER) AS OpenPos FROM [qry PO_data calculated fields final] WHERE SUPPLIER = 'Tech&Lease';
|
|
Reply By:
|
tulincim
|
Reply Date:
|
9/29/2005 10:05:16 AM
|
Here is the problem:
There is no problem with the following query:
"SELECT Count(PONUMBER) AS OpenPos FROM [qry PO_data calculated fields final] WHERE SUPPLIER IN (" &MSupp& ")"
My problem is that I can't pass value(s) with special characters from JavaScript function to asp page using <%SSupp= Request("Supp")%>.
Let me give a example:<option value="<%Tech&Lease%>"><%Tech&Lease%></option>
When I try to pass value from javascript to asp page using
<%SSupp= Request("Supp")%>
I get only SSupp=Tech
I can't get & symbol.
My question is that how can i pass value(s) with special characters from javascript to asp page.
Thanks,
|
|
Reply By:
|
rstelma
|
Reply Date:
|
9/29/2005 10:26:48 AM
|
When you right the value to the asp page use <% Response.Write Server.URLencode(var) %>. The ampersand is cutting the string.
|
|
Reply By:
|
tulincim
|
Reply Date:
|
9/29/2005 10:42:58 AM
|
Nope, it doesn't work...
|
|
Reply By:
|
tulincim
|
Reply Date:
|
9/29/2005 1:54:14 PM
|
Here is the my problem:
<select NAME="Supplier">
<option value="Tech&Lease">Tech&Lease</option>
</select>
<%SSupp= QueryString("Supplier")%>
I can only pull Tech part. How can I handle the "&" character using QueryString.
Thanks
|
|
Reply By:
|
vinod_yadav1919
|
Reply Date:
|
9/30/2005 12:27:40 AM
|
Hiii tulincim!!
Here is the solution
****test.html******
<FORM NAME=MYFORM ACTION="TEST1.ASP">
<select NAME="Supplier">
<option value="Tech&Lease">Tech&Lease</option>
</select>
<INPUT TYPE=SUBMIT NAME=A VALUE=SUB>
</FORM>
**********TEST1.ASP********
<%
myvalue=request.querystring("Supplier")
response.write(myvalue)
%>
Which gives the result "Tech&Lease" in output/value
Hope this will help you :)
try this link
http://n.1asphost.com/vinodvinod/test.html
Cheers :)
vinod
|
|
Reply By:
|
ChrisScott
|
Reply Date:
|
9/30/2005 3:09:10 AM
|
Hi tulincim,
Vinod's suggestion of submittng a form rather than navigating with javascript is a nice solution - let the browser take care of the encoding.
If you really want to do it with javascript, you can use encodeURIComponent() or encodeURI() methods.
Cheers,
Chris
|
|
Reply By:
|
tulincim
|
Reply Date:
|
10/3/2005 11:26:25 AM
|
When I used "encodeURIComponent" or "encodeURI()", it gives the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'encodeURIComponent'
It doesn't work...
Thanks,
|
|
Reply By:
|
ChrisScott
|
Reply Date:
|
10/4/2005 2:35:40 AM
|
Those methods are javascript, you can use them client-side when building a querystring.
Cheers,
Chris
|
|
Reply By:
|
rstelma
|
Reply Date:
|
10/4/2005 11:43:36 AM
|
Hi All,
Tulin presents a very good case for using "IDs" to retrieve data. In my experience you're just asking for trouble retrieving data through varchar values. If the database IDs were written to the form there wouldn't have been any problem with the original code. Only integers would have been passed and there wouldn't have been all this trouble with URL encoding.
However, the following code does work. You can have ASP handle the URLencoding. There is a comma added to the javascript function, strSel += sel[item].value + ",\n"; to insure that the SPLIT function on post would find the end of each entry.
CREATE TABLE [Supplier] (
[SuppID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[SuppName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Supptext] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SuppName SuppText
Red & Lease This is Red & Lease.
Tech & Lease Of course, you already know this is Tech & Lease.
richie&Lease And, yeah, this is richie&Lease.
bill&Lease You can tell, bill&Lease.
<html>
<head>
<title>Select Test</title>
<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='select.asp?Supplier=' + strSel;
}
</script>
</head>
<body leftmargin="0" topmargin="0">
<br><br>
<form Name="myform" method="post">
<select NAME="Supplier" SIZE=4 MULTIPLE >
<option value="<% Response.Write Server.URLencode("Red & Lease") %>">Red & Lease</option>
<option value="<% Response.Write Server.URLencode("Tech & Lease") %>">Tech & Lease</option>
<option value="<% Response.Write Server.URLencode("richie&Lease") %>">richie&Lease</option>
<option value="<% Response.Write Server.URLencode("bill&Lease") %>">bill&Lease</option>
</select>
<br>
<INPUT TYPE="BUTTON" NAME="open" VALUE="SUBMIT" ONCLICK="outputSelected(this.form.Supplier.options)">
<br>
<%
supplier = Trim(Request("Supplier"))
If Len(supplier) > 0 Then
thevalues = split(supplier, ",")
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Application("conn")
For i = 0 to ubound(thevalues)
SQL = "Select * from Supplier where SuppName = '" & thevalues(i) & "';"
Set oRS = oConn.Execute(SQL)
If not oRS.EOF Then
Response.Write "<br><b>Supplier:</b> " & oRS("SuppName") & " - - - - " & oRS("Supptext") & "<br>"
End If
Next
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
End If
%>
</body>
</html>
|
|
Reply By:
|
Redden
|
Reply Date:
|
10/5/2005 1:52:23 PM
|
I haven't been using Java or html select options so the code may not be exactly correct but isn't is possible to use a different option text and option value in an html select element?
to use your example:
<select name="Supplier">
<option value="TechLease">Tech&Lease</option>
</select>
basically show the '&' to the user when he makes the selection but leave it out of the query string. your asp.net program can add it in later.
If you need to, you can use a substitute character instead of leaving it out and then have your asp.net code replace it. This way you can get the option values from a database without knowing all the possible values at design time.
Retrieving an item from the database to create the java code would simply be the reverse. Programmatically substitute the '&' with the substitute character when creating the option tag's value item.
This should solve your problem.
|
|
Reply By:
|
rstelma
|
Reply Date:
|
10/5/2005 3:28:50 PM
|
Hi,
You make a very good case for using an identity column. However, this code works like a charm. It seems that it does work using ASP to encode the URL but for some reason once you encode the text it doesn't add the comma delimiter on the post so I just added it to the javascript: strSel += sel[item].value + ",\n";
Never have I seen so many posts to such a simple problem. Tulin, you are a lucky man.
CREATE TABLE [Supplier] (
[SuppID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[SuppName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Supptext] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
<html>
<head>
<title>Select Test</title>
<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='select.asp?Supplier=' + strSel;
}
</script>
</head>
<body leftmargin="0" topmargin="0">
<br><br>
<form Name="myform" method="post">
<select NAME="Supplier" SIZE=4 MULTIPLE >
<option value="<% Response.Write Server.URLencode("Red & Lease") %>">Red & Lease</option>
<option value="<% Response.Write Server.URLencode("Tech & Lease") %>">Tech & Lease</option>
<option value="<% Response.Write Server.URLencode("richie&Lease") %>">richie&Lease</option>
<option value="<% Response.Write Server.URLencode("bill&Lease") %>">bill&Lease</option>
</select>
<br>
<INPUT TYPE="BUTTON" NAME="open" VALUE="SUBMIT" ONCLICK="outputSelected(this.form.Supplier.options)">
<br>
<%
supplier = Trim(Request("Supplier"))
If Len(supplier) > 0 Then
thevalues = split(supplier, ",")
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Application("conn")
For i = 0 to ubound(thevalues)
SQL = "Select * from Supplier where SuppName = '" & thevalues(i) & "';"
Set oRS = oConn.Execute(SQL)
If not oRS.EOF Then
Response.Write "<br><b>Supplier:</b> " & oRS("SuppName") & " - - - - " & oRS("Supptext") & "<br>"
End If
Next
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
End If
%>
</body>
</html>
|
|
Reply By:
|
rstelma
|
Reply Date:
|
10/5/2005 3:33:09 PM
|
Hi Tulin,
I knew this would work but had to write a complete page to figure it out. There's a couple of things. I noticed that if you don't use the double quotes on select values they get cut off if there are spaces in the values. Also, Server.URLencode does work fine except that I had to had a comma to a line in the javascript: strSel += sel[item].value + ",\n"; because with the URL encoding the comma is not added and makes it impossible to SPLIT the string.
I have tried posting the code to the page that works but the post keeps getting rejected, maybe cause its too long.
Thanks
|
|
Reply By:
|
tulincim
|
Reply Date:
|
10/6/2005 1:16:58 PM
|
I really appreciate for all your help. I used Server.URLencode and I solved my problem.
It was really painfull but I learned a lof from you.
Thanks, again...
|
