Link to home
Start Free TrialLog in
Avatar of LTY83
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?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!
Avatar of k-do
k-do
Flag of United States of America image

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
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
Avatar of LTY83
LTY83

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?
ASKER CERTIFIED SOLUTION
Avatar of k-do
k-do
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LTY83

ASKER

Thanks so much for a timely solution!