Solved

Sending option value with dependent listboxes

Posted on 2004-10-22
265 Views
Last Modified: 2008-02-26
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
Question by:LTY83
    5 Comments
     
    LVL 3

    Expert Comment

    by:k-do
    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
     
    LVL 3

    Expert Comment

    by:k-do
    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
     
    LVL 4

    Author Comment

    by:LTY83
    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
     
    LVL 3

    Accepted Solution

    by:
    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
     
    LVL 4

    Author Comment

    by:LTY83
    Thanks so much for a timely solution!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    Suggested Solutions

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    This video discusses moving either the default database or any database to a new volume.

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now