• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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!
0
LTY83
Asked:
LTY83
  • 3
  • 2
1 Solution
 
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
 
LTY83Author Commented:
Thanks so much for a timely solution!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now