Help with dynamic drop down lists

MarkGT · April 28th, 2008, 07:51 PM

On my asp page I have 3 forms, each with two drop down lists. Of those two ddls, on each form, one is dependent on the other for population.

I got the dynamic population effect to work, but the problem is the page refreshes after each selection. This clears the information from previous ddl selections. How can I save the ddl selections even after the page refreshes (up to three times) so I can use them to create sql queries after all selections have been made?

I realize the way this works isn't ideal, but I'd like to use it, since it's fairly simple so far. There is very little data being moved around (and always will be), so the page reloads are very quick.

om_prakash · April 29th, 2008, 04:07 AM

Hi,

Can you paste the code which is being used?

Om Prakash

MarkGT · April 29th, 2008, 07:36 AM

yes, here is the code

Code:

<%@ Language = VBscript %>
<% Option Explicit %>

<% Response.Buffer = True %>


<html>
<head>

<SCRIPT language=JavaScript>

function reload(form){
var val=form.xclass.options[form.xclass.options.selectedIndex].value;
self.location='mll.asp?xclass=' + val ;
}

function areload(form){
var aval=form.axclass.options[form.axclass.options.selectedIndex].value;
self.location='mll.asp?axclass=' + aval ;
}

function breload(form){
var bval=form.bxclass.options[form.bxclass.options.selectedIndex].value;
self.location='mll.asp?bxclass=' + bval ;
}

</script>
</head>
<body>
The idea behind this is to allow the user to be able to select three different classes<br>
and then click a "submit" button to run hardcoded sql statements to compare statistics<br> between the three groups. 
<%

'=========================
'first set of ddls
'=========================

Dim objconn,objRS,strSQL,xclass
xclass=Request.QueryString("xclass")
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("Series6.mdb")
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT distinct class from s6test"
objRS.Open strSQL, objconn
Response.Write "<form method=post name=f1 action=dll.asp target="_self"><select name=xclass onchange='reload(this.form)'><option value=''>Class</option>"
Do While Not objRS.EOF 
Response.Write "<option value=" & objRs("class") & ">" & objRs("class") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"
Response.Write "&nbsp;&nbsp;"

If len(xclass) > 1 Then
strSQL = "SELECT distinct classmd FROM s6test where class='" & xclass & "';"
objRS.Open strSQL, objconn
Response.write "<select name=select2><option value=''>Month/Day</option>"    
Do While Not objRS.EOF 
Response.Write "<option value=" & objRs("classmd") & ">" & objRs("classmd") & "</option>"
objRS.MoveNext
Loop
Response.Write "</form>"
response.write "</select>"
objRs.Close
objconn.Close
end if
Response.write "<br><br>" 

'====================
'second set of ddls
'====================

Dim aobjconn,aobjRS,astrSQL,axclass
axclass=Request.QueryString("axclass")
Set aobjconn = Server.CreateObject("ADODB.Connection")
aobjconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("Series6.mdb")
aobjconn.Open
Set aobjRs = Server.CreateObject("ADODB.Recordset")
astrSQL = "SELECT distinct class from s6test"
aobjRS.Open astrSQL, aobjconn
Response.Write "<form method=post name=f2 action=''><select name=axclass onchange='areload(this.form)'><option value=''>Class</option>"
Do While Not aobjRS.EOF 
Response.Write "<option value=" & aobjRs("class") & ">" & aobjRs("class") & "</option>"
aobjRS.MoveNext
Loop
aobjRs.Close
Response.Write "</select>"
Response.Write "&nbsp;&nbsp;"

If len(axclass) > 1 Then
astrSQL = "SELECT distinct classmd FROM s6test where class='" & axclass & "';"
aobjRS.Open astrSQL, aobjconn
Response.write "<select name=select3><option value=''>Month/Day</option>"    
Do While Not aobjRS.EOF 
Response.Write "<option value=" & aobjRs("classmd") & ">" & aobjRs("classmd") & "</option>"
aobjRS.MoveNext
Loop
Response.Write "</form>"
response.write "</select>"
aobjRs.Close
aobjconn.Close
end if 
response.write "<br><br>"

'====================
'Third set of ddls
'====================

Dim bobjconn,bobjRS,bstrSQL,bxclass
bxclass=Request.QueryString("bxclass")
Set bobjconn = Server.CreateObject("ADODB.Connection")
bobjconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("Series6.mdb")
bobjconn.Open
Set bobjRs = Server.CreateObject("ADODB.Recordset")
bstrSQL = "SELECT distinct class from s6test"
bobjRS.Open bstrSQL, bobjconn
Response.Write "<form method=post name=f2 action=''><select name=bxclass onchange='breload(this.form)'><option value=''>Class</option>"
Do While Not bobjRS.EOF 
Response.Write "<option value=" & bobjRs("class") & ">" & bobjRs("class") & "</option>"
bobjRS.MoveNext
Loop
bobjRs.Close
Response.Write "</select>"
Response.Write "&nbsp;&nbsp;"

If len(bxclass) > 1 Then
bstrSQL = "SELECT distinct classmd FROM s6test where class='" & bxclass & "';"
bobjRS.Open bstrSQL, bobjconn
Response.write "<select name=select3><option value=''>Month/Day</option>"    
Do While Not bobjRS.EOF 
Response.Write "<option value=" & bobjRs("classmd") & ">" & bobjRs("classmd") & "</option>"
bobjRS.MoveNext
Loop
Response.Write "</form>"
response.write "</select>"
bobjRs.Close
bobjconn.Close
end if 
%>

</body>
</html>

mat41 · April 29th, 2008, 06:05 PM

Its not a bad thing re-submitting the page to achieve your objective. However why use multiple forms??? there is no need.
I would:

> get rid of multiple forms, use only one.
> when you call your onChange function submit the form!!
> you should check if your record set is empty when you create your select lists, this is just good code practice.

Anyhow here is how I would code up your first select list includig the code to ensure the previous selection remains selected:
NOTE - The below method will only work for you if each time you call the onChange function the form is submitted

strSQL = "SELECT distinct class from s6test;"
objRS.Open strSQL, objconn
   if not objRS.eof then %>
      <select name="xclass" onchange="reload(this.form)">
       <option value="">Class</option>
<% Do While Not objRS.EOF %>
         <option value="<%= objRs("class") %>" <% if trim(request.form("xclass")) <> "" then
                                                     if trim(request.form("xclass")) = objRs("class") then
                                                        response.write " selected "
                                                     end if
                                                  end if %>><%= objRs("class") %></option>
<% objRS.MoveNext
      Loop
      objRs.Close %>
      </select>
<% else %>
      <i>No records found</i>
<% end if %>

Wind is your friend
Matt
www.elitemarquees.com.au