LTY83
asked on
Sending option value with dependent listboxes
This doesn't seem like a difficult problem, but I've tried everything I know and it's not working right. I have three dropdown boxes on the first page, all populated from the database. The second and third dropdowns are dependent on the first. That part is all working correctly.
The problem I have is sending the value for "salesperson" to the second page. I've tried request.querystring and request.form but I either get nothing or I get the entire select value string returned, not just the salesperson parameter. ("location.href='dropdown. asp?salesp erson=<%=r s("SalesPs n")%>'" )
Any idea what I'm doing wrong?
My code looks like this:
**************dropdown.asp ********** ******
<%response.buffer=true%>
<%
Dim oConn, dbPath, myconn
'dbPath = Server.MapPath("db3.mdb")
Set oConn=Server.CreateObject( "ADODB.Con nection")
myconn = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=myserver;" _
& "DATABASE=mydb;" _
& "UID=userid;" _
& "PWD=password;" _
& "OPTION=" & 21 + 2 + 8 + 32 + 2048 + 16384
oConn.Open myconn
set rs = Server.CreateObject("ADODB .Recordset ")
sql = "SELECT distinct SalesPsn FROM invoice_copy order by SalesPsn"
rs.Open sql, oConn
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
</head>
<script language="Javascript">
function doSel(obj)
{
for (i = 0; i < obj.length; i++)
if (obj[i].selected == true)
eval(obj[i].value);
}
</script>
<body>
<form action="dropdown-results.a sp" method="post">
<table border="0" width="100%">
<tr>
<td colspan="2">
Salesperson:
<select name="salesperson" onchange="doSel(this)">
<option value="">Please select</option>
<%Do while not(rs.eof)%>
<option value="location.href='drop down.asp?s alesperson =<%=rs("Sa lesPsn")%> '" <%if request.querystring("sales person")=r s("SalesPs n") then response.write "selected" end if%>><%=rs("SalesPsn")%></ option>
<%
rs.movenext
loop
%>
</select>
</td>
</tr>
</table>
<table border="0" width="100%">
<br><br>
<tr>
<td colspan="2" >Optional -- Narrow salesperson data by customer and/or year</td>
</tr>
<tr><td>Customer: <select name="co" onchange="doSel(this)">
<option value="">Please select</option>
<%if request.querystring("sales person") <> "" then
set rs1 = Server.CreateObject("ADODB .Recordset ")
sql = "SELECT distinct CoName FROM invoice_copy WHERE SalesPsn = '"& request.querystring("sales person") &"'"
rs1.Open sql, oConn%>
<%Do while not(rs1.eof)%>
<option value="<%=rs1("CoName")%>" <%if request.querystring("sales person")=r s1("CoName ") then response.write "selected" end if%>><%=rs1("CoName")%> </option>
<%
rs1.movenext
loop%>
</select>
<%rs1.Close()
Set rs1 = Nothing %>
<% end if %>
</td>
<td>Year: <select name="year" onchange="doSel(this)">
<option value="">Please select</option>
<%if request.querystring("sales person") <> "" then
set rs2 = Server.CreateObject("ADODB .Recordset ")
sql = "SELECT distinct DtOrdYear FROM invoice_copy WHERE SalesPsn = '"& request.querystring("sales person") &"'"
rs2.Open sql, oConn
%>
<%Do while not(rs2.eof)%>
<option value="<%=rs2("DtOrdYear") %>" <%if request.querystring("sales person")=r s2("DtOrdY ear") then response.write "selected" end if%>><%=rs2("DtOrdYear")%> </option>
<%
rs2.movenext
loop%>
</select>
<%rs2.Close()
Set rs2 = Nothing %>
</td>
<%end if%>
</tr>
</table>
<br><br>
<table border="0" width="100%">
<tr>
<td colspan="2" align="center"><input type="submit" value="Search"></td></tr>
</table>
</form>
<%
rs.Close()
Set rs = Nothing
%>
</body>
</html>
**************dropdown-res ults.asp** ********** ****
<html>
<head>
<% Dim oConn,myconn, mysalesperson, myco, myyear
Set oConn=Server.CreateObject( "ADODB.Con nection")
myconn = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=myserver;" _
& "DATABASE=mydb;" _
& "UID=userid;" _
& "PWD=password;" _
& "OPTION=" & 21 + 2 + 8 + 32 + 2048 + 16384
oConn.Open myconn
'mysalesperson=request.que rystring(" salesperso n") 'returns nothing
'mysalesperson=request.for m("salespe rson") 'returns error "SQLBindParameter not used for all parameters"
mysalesperson = request.querystring("sales person")
myco = request.form("co")
myyear = request.form("year")
%>
<!-- select salesperson records based on mysalesperson, myco, and myyear -->
<%
myquery = "Select * from invoice_copy where SalesPsn = '" & mysalesperson & "'"
if myco <> "" then
myquery = myquery & " and CoName = '" & myco & "'"
end if
if myyear <> "" then
myquery = myquery & " and DtOrdYear = '" & myyear & "'"
end if
set rsAns = Server.CreateObject("ADODB .Recordset ")
rsAns.Open myquery,oConn
%>
</head>
<% if rsAns.RecordCount = 0 then %>
<table cellspacing="0" class="titleCellFont" cellpadding="4" border="0" align="center" width = "100%">
<tr><td>No records matching: <strong><%= mysalesperson %> : <%=myco%>, <%= myyear %></strong></td></tr>
</table>
<% else %>
<table cellspacing="0" class="hdrFont" cellpadding="4" border="0" align="center" width = "100%">
<tr><td>Records for: </strong><%= mysalesperson %>
<%if myco <> "" then%> :
<%=myco%>
<% end if %>
<% if myyear <> "" then %> ,
<%=myyear %>
<% end if %>
</strong></td></tr>
</table>
<br>
<table cellspacing="0" cellpadding="4" border="0" align="center" width = "100%">
<tr>
<td>Salesperson</td>
<td>Company</td>
<td>Net Amt</td>
<td>Order Year</td>
</tr>
<tr><td colspan="4">
<hr>
</td>
</tr>
<%
while not rsAns.eof
%>
<tr>
<td><%= rsAns("SalesPsn") %></td>
<td><%= rsAns("CoName") %></td>
<td><%= (rsAns("netAmt")) %></td>
<td><%= rsAns("DtOrdYear") %></td>
</tr></table>
<% rsAns.movenext
wend
end if
rsAns.close
set rsAns=nothing
oConn.close
set oConn=nothing
%>
</body>
</html>
Thanks!
The problem I have is sending the value for "salesperson" to the second page. I've tried request.querystring and request.form but I either get nothing or I get the entire select value string returned, not just the salesperson parameter. ("location.href='dropdown.
Any idea what I'm doing wrong?
My code looks like this:
**************dropdown.asp
<%response.buffer=true%>
<%
Dim oConn, dbPath, myconn
'dbPath = Server.MapPath("db3.mdb")
Set oConn=Server.CreateObject(
myconn = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=myserver;" _
& "DATABASE=mydb;" _
& "UID=userid;" _
& "PWD=password;" _
& "OPTION=" & 21 + 2 + 8 + 32 + 2048 + 16384
oConn.Open myconn
set rs = Server.CreateObject("ADODB
sql = "SELECT distinct SalesPsn FROM invoice_copy order by SalesPsn"
rs.Open sql, oConn
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
</head>
<script language="Javascript">
function doSel(obj)
{
for (i = 0; i < obj.length; i++)
if (obj[i].selected == true)
eval(obj[i].value);
}
</script>
<body>
<form action="dropdown-results.a
<table border="0" width="100%">
<tr>
<td colspan="2">
Salesperson:
<select name="salesperson" onchange="doSel(this)">
<option value="">Please select</option>
<%Do while not(rs.eof)%>
<option value="location.href='drop
<%
rs.movenext
loop
%>
</select>
</td>
</tr>
</table>
<table border="0" width="100%">
<br><br>
<tr>
<td colspan="2" >Optional -- Narrow salesperson data by customer and/or year</td>
</tr>
<tr><td>Customer: <select name="co" onchange="doSel(this)">
<option value="">Please select</option>
<%if request.querystring("sales
set rs1 = Server.CreateObject("ADODB
sql = "SELECT distinct CoName FROM invoice_copy WHERE SalesPsn = '"& request.querystring("sales
rs1.Open sql, oConn%>
<%Do while not(rs1.eof)%>
<option value="<%=rs1("CoName")%>"
<%
rs1.movenext
loop%>
</select>
<%rs1.Close()
Set rs1 = Nothing %>
<% end if %>
</td>
<td>Year: <select name="year" onchange="doSel(this)">
<option value="">Please select</option>
<%if request.querystring("sales
set rs2 = Server.CreateObject("ADODB
sql = "SELECT distinct DtOrdYear FROM invoice_copy WHERE SalesPsn = '"& request.querystring("sales
rs2.Open sql, oConn
%>
<%Do while not(rs2.eof)%>
<option value="<%=rs2("DtOrdYear")
<%
rs2.movenext
loop%>
</select>
<%rs2.Close()
Set rs2 = Nothing %>
</td>
<%end if%>
</tr>
</table>
<br><br>
<table border="0" width="100%">
<tr>
<td colspan="2" align="center"><input type="submit" value="Search"></td></tr>
</table>
</form>
<%
rs.Close()
Set rs = Nothing
%>
</body>
</html>
**************dropdown-res
<html>
<head>
<% Dim oConn,myconn, mysalesperson, myco, myyear
Set oConn=Server.CreateObject(
myconn = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=myserver;" _
& "DATABASE=mydb;" _
& "UID=userid;" _
& "PWD=password;" _
& "OPTION=" & 21 + 2 + 8 + 32 + 2048 + 16384
oConn.Open myconn
'mysalesperson=request.que
'mysalesperson=request.for
mysalesperson = request.querystring("sales
myco = request.form("co")
myyear = request.form("year")
%>
<!-- select salesperson records based on mysalesperson, myco, and myyear -->
<%
myquery = "Select * from invoice_copy where SalesPsn = '" & mysalesperson & "'"
if myco <> "" then
myquery = myquery & " and CoName = '" & myco & "'"
end if
if myyear <> "" then
myquery = myquery & " and DtOrdYear = '" & myyear & "'"
end if
set rsAns = Server.CreateObject("ADODB
rsAns.Open myquery,oConn
%>
</head>
<% if rsAns.RecordCount = 0 then %>
<table cellspacing="0" class="titleCellFont" cellpadding="4" border="0" align="center" width = "100%">
<tr><td>No records matching: <strong><%= mysalesperson %> : <%=myco%>, <%= myyear %></strong></td></tr>
</table>
<% else %>
<table cellspacing="0" class="hdrFont" cellpadding="4" border="0" align="center" width = "100%">
<tr><td>Records for: </strong><%= mysalesperson %>
<%if myco <> "" then%> :
<%=myco%>
<% end if %>
<% if myyear <> "" then %> ,
<%=myyear %>
<% end if %>
</strong></td></tr>
</table>
<br>
<table cellspacing="0" cellpadding="4" border="0" align="center" width = "100%">
<tr>
<td>Salesperson</td>
<td>Company</td>
<td>Net Amt</td>
<td>Order Year</td>
</tr>
<tr><td colspan="4">
<hr>
</td>
</tr>
<%
while not rsAns.eof
%>
<tr>
<td><%= rsAns("SalesPsn") %></td>
<td><%= rsAns("CoName") %></td>
<td><%= (rsAns("netAmt")) %></td>
<td><%= rsAns("DtOrdYear") %></td>
</tr></table>
<% rsAns.movenext
wend
end if
rsAns.close
set rsAns=nothing
oConn.close
set oConn=nothing
%>
</body>
</html>
Thanks!
Also, since your form method is POST, the correct way to retrieve the values is Request.Form. (If the method is GET, then you would use Request.Querystring.)
I might be missing the point, but is there any reason to include onchange="doSel(this)" within the customer and year select tags? It seems that you only need the drop down lists to refresh when you change the salesperson and that this code is not needed within these two tags.
Keith
I might be missing the point, but is there any reason to include onchange="doSel(this)" within the customer and year select tags? It seems that you only need the drop down lists to refresh when you change the salesperson and that this code is not needed within these two tags.
Keith
ASKER
No reason for the onchange function in the customer and year select tags. I took that out in the page I'm looking at now, but I copied this code from the original.
Sorry I don't know much about javascript - what would be the correct syntax within the javascript function?
Sorry I don't know much about javascript - what would be the correct syntax within the javascript function?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much for a timely solution!
<option value="location.href='drop
... it appears that the value for the salesperson is location.href='dropdown.as
Hope this helps,
Keith