Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Linked List Boxes without Reloading Page

Dynamic List Boxes

Linked List Boxes without Reloading Page

by Tarwn Posted May 27, 2003 (Edited Mar 21, 2006)

------------------------------------------------------
- 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

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Accept Learn more…

Back

Top