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?salesperson=<%=rs("SalesPsn")%>'" )

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.Connection")
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.asp" 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='dropdown.asp?salesperson=<%=rs("SalesPsn")%>'" <%if request.querystring("salesperson")=rs("SalesPsn") 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("salesperson") <> "" then

set rs1 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT distinct CoName FROM invoice_copy WHERE SalesPsn = '"& request.querystring("salesperson") &"'"
rs1.Open sql, oConn%>

<%Do while not(rs1.eof)%>
<option value="<%=rs1("CoName")%>" <%if request.querystring("salesperson")=rs1("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("salesperson") <> "" then
 set rs2 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT distinct DtOrdYear FROM invoice_copy WHERE SalesPsn = '"& request.querystring("salesperson") &"'"
rs2.Open sql, oConn
%>

<%Do while not(rs2.eof)%>
<option value="<%=rs2("DtOrdYear")%>" <%if request.querystring("salesperson")=rs2("DtOrdYear") 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-results.asp****************
<html>
<head>
<% Dim oConn,myconn, mysalesperson, myco, myyear
Set oConn=Server.CreateObject("ADODB.Connection")
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.querystring("salesperson") 'returns nothing
 'mysalesperson=request.form("salesperson") 'returns error "SQLBindParameter not used for all parameters"


   mysalesperson = request.querystring("salesperson")
   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!
LVL 4
LTY83Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

k-doCommented:
The value you are looking for is not going to transfer to your next page based on the code in the line:

<option value="location.href='dropdown.asp?salesperson=<%=rs("SalesPsn")%>'" <%if request.querystring("salesperson")=rs("SalesPsn") then response.write "selected" end if%>><%=rs("SalesPsn")%></option>

... it appears that the value for the salesperson is location.href='dropdown.asp?salesperson=<%=rs("SalesPsn")%>'.  Your best bet would probably be to make the value simply equal to rs("SalesPsn"), then modify your javascript code and put the part that refreshes the page (with the updated drop downs boxes) in the javascript function that is called.

Hope this helps,

Keith
0
k-doCommented:
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
0
LTY83Author Commented:
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?
0
k-doCommented:
Here's a new function that can be used:

function doSel(obj)
{
  for (i = 0; i < obj.length; i++)
     if (obj[i].selected == true)
       eval("location.href='dropdown.asp?salesperson=" + obj[i].value + "'");
}

Then you would modify the line which creates the salesperson option to this:

<option value="<%=rs("SalesPsn")%>" <%if request.querystring("salesperson")=rs("SalesPsn") then response.write "selected" end if%>><%=rs("SalesPsn")%></option>

Keith
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LTY83Author Commented:
Thanks so much for a timely solution!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.