Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sending option value with dependent listboxes

Posted on 2004-10-22
5
Medium Priority
?
279 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
Comment
Question by:LTY83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:k-do
ID: 12381628
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
ID: 12381721
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
ID: 12381786
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:
k-do earned 800 total points
ID: 12381877
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
ID: 12382009
Thanks so much for a timely solution!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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