------------------------------------------------------
- Information
- Originally this was posted as a response to a users
- question.
- Updated on June 16, 2004:
- - Corrected issues with array generation from recordset
- - Corrected functions to work with FireFox/Mozilla
- Updated on August 23, 2004
- - Modified to use a simple Array generation technique
- Updated on March 21, 2006
- - Modified the 2 dropdown paper example to be a full working sample
------------------------------------------------------
This FAQ covers a subject that is requested fairly often. Due to the frequency of requests and thegreat length I went to a couple years ago in one such post, I decided I would modify the content of that post and add it to the FAQs to cover the subject. This post will actually cover both the page reload dropdowns and the non-page reload dropdowns.
When it comes to chaining select boxes there are generally two methods,
1) Dropdowns With Form Submission
Set up the onChange event in the first select to submit the form back to the same page, then in the page do a check like this:
If Request.Form("selFirstSelect") <> "" Then
'select statement based on value in first
Else
'select statement based on value of second
end if
Then you can execute it into a Recordset or open a recordset with your sql statement and populate your boxes exactly like before (making sure that if the first one was set you make the value from the previous page selected, etc):
Code:
'pretend we have a recordset named rs
Response.Write "<select name=""selFirstSelect"" onChange=""myFormName.action='nameOfThisPage.asp';myFormName.submit();"">"
rs.moveFirst
Do Until rs.EOF
Response.Write "<option value='" & rs("whatever") & "'"
If Request.Form("selFirstSelect") = rs("whatever") Then Response.Write " selected"
Response.Write "> " & rs("whateverElse") & "</option>"
rs.MoveNext
Loop
Response.Write "</select>"
The first time the page loads it will come up with the first option in the select box (because there is nothing in the Request.Form collection named selFirstSelect). When the users chooses an option the browser will change the action of the form to the page "nameOfThisPage.asp" and submit the form back to itself. On the second load the script will go into the if statement, filter the recordset used for the second select statement, generate the first select from the first recordset (as it did before) selecting the option that matches the one from the previous page.
With three options that filter themselves dependant on the value of the previous one you would want to set up the queries like this:
Code:
If Request.Form("selSecondSelect") <> "" Then
sql_query3 = "Select...Where service = '" & Request.Form("selSecondSelect") & "'" 'assuming service was a string
sql_query2 = "Select...Where id = " & Request.Form("selFirstSelect") 'assuming id was a number
ElseIf Request.Form("selSelectFirst") <> "" Then
sql_query3 = "Select..." 'select based on id
sql_query2 = "Select...Where id = " & Request.Form("selFirstSelect") 'assuming id was a number
Else
sql_query3 = "Select *..."
sql_query2 = "select *..."
End If
sql_query1 = "select *..." 'always select everything for the first one
Ok, so that should be enough to get started if you choose the resubmission option.
2) Dropdowns without Form Submission
Ok, the other method is to use javascript arrays to hold the values. Then when a value is selected from the first select the onChange event calls a function that empties the remaining select boxes then repopulates them from arrays based on the first select box. The difficulties with this method are, of course, browser compatibility and all the javascript necessary.
Static Example
The first example is a static example to give you a feel for how the page will be output to the browser. This example only has two select boxes so what I will do is go through the static and dynamic versions of two select boxes then try to edit the static one for three select boxes and hopefully that should be enough to guide you on how to do it dynamically.
Here is the static example:
Code:
<html>
<head>
<title> Shift Selection </title>
<script language="JavaScript">
<!--
var shifts;
function init(){
//to initialize them all to the same times
shifts = new Array(,new Array("monday 1","monday 2","monday 3"), new Array("tuesday 4","tuesday 5","tuesday 6"))
//change day function
function changeDay(elem){
var j, day;
var shiftText;
if(elem.selectedIndex == 0) //if they selected our pretty [select a day] stmt
return false; //do nothing and leave quietly
//Clear the second drop down of all but top [select a shift]
for (i = document.frmFormName.workday.options.length - 1; i >= 1; i--) document.frmFormName.workday.options[i] = null;
document.frmFormName.workday.selectedIndex = 0;
//grab day from select box
day = elem.selectedIndex;
for(j=0;j<shifts[day].length;j++){
document.frmFormName.workday.options[j] = new Option(shifts[day][j],shifts[day][j]);
}
}
//-->
</script>
</head>
<body onLoad="init();">
<form method=POST action="wherever.html" name="frmFormName">
<select name="weekday" onChange="changeDay(this);">
<option>[Select a day]</option>
<option>Monday</option>
<option>Tuesday</option>
</select>
<select name="workday">
<option>[Select a Shift]</option>
</select>
</form>
</body>
</html>
Converting the static example to Database Driven:
Rather then use the code above I am going to use a different setof exmaple data as well as an example I created in a post on August 23rd.
For our example assume you have a table (or associated tables) that have data for cases of paper. We will have multiple types of paper and then multiple grades of paper for each type. Assume our data comes back from the database in a recordset like:
Code:
Paper.paper_type PaperGrades.paper_grade
Printer Standard 8.5x11
Printer Glossy Photo Paper 5x7
Printer Ultra-Super-Fine 8.5x11
Printer Ultra-Glossy Photo Paper 5x7
LooseLeaf Yellow Standard Rule 8.5x11
LooseLeaf Yellow Legal Rule 8.5x11
LooseLeaf White Legal Rule 8.5x11
Photocopy Standard 8.5x11
Photocopy Super recycled 8.5x11
Photocopy Barely Better Then Toilet Paper 8.5x11
Photocopy Thicker Then Cardcoard 8.5x11
So what we will want to do is loop through this recordset to build the data array then loop through it again to build the first select box (technically we could do it at the same time to save processing power, but that would add confusion to the code).
Lets build the array, we will assume our recordset is called rsPaper:
Code:
<script language="javascript">
var dataArray = new Array(<%
If Not rsPaper.EOF Then rsPaper.MoveFirst
Dim last_item
Do Until rsPaper.EOF
'check if the first column item is equal to it's value
' from the previous loop, if not start a new array
If last_item <> rsPaper("paper_type") Then
'if there was a last item, finish the previous array
If len(last_item) > 0 Then Response.Write ")"
'start a new array
Response.Write ",new Array("
'record last item
last_item = rsPaper("paper_type")
End If
'output the second column value preceded by a comma
Response.Write "," & rsPaper("paper_grade")
'queue to next record
rsPaper.MoveNext
Loop
'finish open array if there was a last item
If len(last_item) > 0 Then Response.Write ")"
%>);
Now that we have our array built we need a function to change options that will be called from our first dropdown:
Code:
function changeOptions(elemOne,elemTwo){
var i;
//clear out previous options leaving the top generic text
for(i = elemTwo.options.length; i >= 1; i--)
elemTwo.options[i] = null;
elemTwo.selectedIndex = 0;
//if the selected index in the first one is 0 escape out
if(elemOne.selectedIndex == 0)
return false;
//populate second one
for(i=1;i<dataArray.length;i++){
elemTwo.options[i] = new Option(dataArray[elemOne.selectedIndex][i],elemOne.selectedIndex][i]);
}
}
</script>
Now this isn't instantly scalable to a three tier select set, but it is close.
taking this one step further and adding id/value pairs like you wanted to do means that we have to add an additional level of depth to our array or use easily plit strings for our array values.
To use the additional array level will require only minimal changes, so I will make them and highlight them with comments:
Code:
<script language="javascript">
var dataArray = new Array(<%
If Not rsPaper.EOF Then rsPaper.MoveFirst
Dim last_item
Do Until rsPaper.EOF
'check if the first column item is equal to it's value
' from the previous loop, if not start a new array
If last_item <> rsPaper("paper_type") Then
'if there was a last item, finish the previous array
If len(last_item) > 0 Then Response.Write ")"
'start a new array
Response.Write ",new Array("
'record last item
last_item = rsPaper("paper_type")
End If
'output the second column value preceded by a comma
Response.Write ",[highlight]new Array(" & rsPaper("grade_id") & "," & rsPaper("paper_grade") & ")"[/highlight] 'Change [0]
'queue to next record
rsPaper.MoveNext
Loop
'finish open array if there was a last item
If len(last_item) > 0 Then Response.Write ")"
%>);
function changeOptions(elemOne,elemTwo){
var i;
//clear out previous options leaving the top generic text
for(i = elemTwo.options.length; i >= 1; i--)
elemTwo.options[i] = null;
elemTwo.selectedIndex = 0;
//if the selected index in the first one is 0 escape out
if(elemOne.selectedIndex == 0)
return false;
//populate second one
for(i=1;i<dataArray.length;i++){
elemTwo.options[i] = new [highlight]Option(dataArray[elemOne.selectedIndex][i][1],elemOne.selectedIndex][i][0]);[/highlight] 'Change [1]
}
}
</script>
Change [0]: Instead of adding just a single value for each garde we now add a two element array of id, value
Change [1]: We still use the value for the text of each new option, but now we use the id for the value
Building the dropdown should be fairly simple as long as you remember that we planned ahead to add a generic [Select an Item] text option to the top of each dropdown. Thecompleted code for this page would look something like:
Code:
<%
Option Explicit
'bunch of ADO code here to make a connection, retrieve data, etc
Dim rsPaper, ado_conn
Set ado_conn = Server.CreateObject("ADODB.Connection")
ado_conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("PaperDemo.mdb") & ";" & _
"Persist Security Info=False"
Dim str_sql
str_sql = "SELECT paper_type, grade_id, paper_grade " & _
"FROM Paper INNER JOIN PaperGrade ON Paper.paper_id = PaperGrade.paper_id " & _
"ORDER BY paper_type, paper_grade"
Set rsPaper = ado_conn.Execute(str_sql)
%>
<html>
<head>
<script language="javascript">
var dataArray = new Array(null<%
If Not rsPaper.EOF Then rsPaper.MoveFirst
Dim last_item
Do Until rsPaper.EOF
'check if the first column item is equal to it's value
' from the previous loop, if not start a new array
If last_item <> rsPaper("paper_type") Then
'if there was a last item, finish the previous array
If len(last_item) > 0 Then Response.Write ")"
'start a new array
Response.Write ",new Array(null"
'record last item
last_item = rsPaper("paper_type")
End If
'output the second column value preceded by a comma
Response.Write ",new Array('" & rsPaper("grade_id") & "','" & rsPaper("paper_grade") & "')"
'queue to next record
rsPaper.MoveNext
Loop
'finish open array if there was a last item
If len(last_item) > 0 Then Response.Write ")"
%>);
function changeOptions(elemOne,elemTwo){
var i;
//clear out previous options leaving the top generic text
for(i = elemTwo.options.length; i >= 1; i--)
elemTwo.options[i] = null;
elemTwo.selectedIndex = 0;
//if the selected index in the first one is 0 escape out
if(elemOne.selectedIndex == 0)
return false;
//populate second one
for(i=1;i<dataArray.length;i++){
elemTwo.options[i] = new Option(dataArray[elemOne.selectedIndex][i][1],dataArray[elemOne.selectedIndex][i][0]);
}
}
</script>
</head>
<body>
<form method="POST" action="whatever.asp">
<select name="selType" onChange="changeOptions(this,document.getElementsByName('selGrade')[0])">
<option value="">[ Select Type ]</option>
<%
'the only way the RS could be BOF at this point is if it was empty
If Not rsPaper.BOF Then rsPaper.MoveFirst
Dim last_paper
Do Until rsPaper.EOF
If rsPaper("paper_type") <> last_paper Then
Response.Write "<option>" & rsPaper("paper_type") & "</option>"
last_paper = rsPaper("paper_type")
End If
rsPaper.MoveNext
Loop
%>
</select>
<select name="selGrade">
<option value="">[ Select a Grade ]</option>
</select>
<input type="submit">
</form>
</body>
</html>
[link http://www.tiernok.com/tt/PaperDemo.zip]Download the sample ASP and MDB file here (ADO portion included)[/link]
Triple+ Chained Dropdown
Now that shows us how it will work with 2 select boxes, but let us add a third or fourth.
For this example I am using the imaginary fields id, service, and area. The user will first pick an id, then a service name, then an area that service is available.
Create your third array level similar to how the second array is nested in the first one above, probably will want it to look like this(pseudo code again):
Code:
var dataArray = new Array(
<%
' this is not real code, just a bare minimum for creating code later
Dim last_id. last_service
Do Until EOF
if last_id is not = id from recordset
if last service was not empty
output a ) to end last service array
if last id was not empty
output a ) to end last id
start a new array for this id: output new Array(
end if
if last_service is not = service from recordset
if last service was not empty
output a ) to end last service
start a new array for this set of areas: output new Array(
end if
output , and area from recordset
MoveNext
Loop
check if last_service <> ""
output a ) to close outstanding areas array
check if last_id <> ""
output a ) to end last services array
%>);
Then I would just go ahead and build a second function to handle assigning values to the second select box.
The first function will need to clear both boxes, as a change to the first box (id) will need a change to the second box(service) and if there were already values in the last box (area) then we need to get rid of them because service has changed.
As with the earlier example the complicated portion is in the javascript array. Once this is completed you will be reusing the clearCombo function and similar functions to add options to your select boxes. Your third select will look almost exactly like the second one, except of course for it's name and the fact that the second will have the onChange event ot populate the third.
I hope this has proven useful,
Tarwn
