Hmm, I know I posted a rather long example at one point that was strangely akin to a tutorial, but I can't seem to find it now.
Here is a post concerning the advantages and disadvantages of each method: thread333-440352
And now I will dig through the tutorials I am writing for my site and find the long version of the client-side one
Here is a copy of the old post I luckily saved.
Note: This was originally dealt to handle someone's elses database and 3 dropdowns, but should show you the concepts you need to write it for two dropdowns (which is actually easier). It will show you how the final output should work (static example) and how to write our ASP to create that output.
---------- Tarwn's Lazy Cut n Paste ----------------------
When it comes to chaining select boxes there are generally two methods,
1) 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):
'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 named selFirstSelect). When they choose an option it will change the action of the form to the page "nameOfThisPage.asp" and submit it back to itself. On the second load it 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:
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.
The other method...wait, let me put up a big 'ol two:
2) Ok, the other method is to use javascript arrays to hold the values. Then when a visitor selects from the first select box it calls a function that empties the remaining select boxes then repopulates them from arrays based on the values in the first select box. The difficulties with this method are, of course, browser compatibility and all the javascript necessary. If you want to see an example of a lot of javascript, there is a messy copy (a co-developer's first try with my later changes to reflect changes to the db) here:
go to advanced search (nav in top right), the taxonomy search is three chained select boxes, if you view source you will see a lot of javascript, most of it should be arrays to maintain those drop downs. These arrays were written dynamically from the ASP script. The other fuctions of interest are populateSubject(), populateTopic, and clearCombo functions. If your still interested after seeing that, let me know, I have a much cleaner example (ok, cleaner not clean
) stored locally that could lead you through the process.
Ok, I have three examples here that I can post. The first 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={dayOfWeek: [{day: "monday",theShifts: ["1","2","3"]},{day:"tuesday",theShifts: ["4","5","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 = frmFormName.workday.options.length - 1; i >= 1; i--) frmFormName.workday.options[i] = null;
frmFormName.workday.selectedIndex = 0;
//grab day from select box
day = elem.selectedIndex-1;
for(j=0;j<shifts.dayOfWeek[day].theShifts.length;j++){
document.frmFormName.workday.options[j] = new Option(shifts.dayOfWeek[day].theShifts[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>
Basically the concept here is that we have 6 shifts. Shifts 1-3 only occur on Mondays, 4-6 only occur on Tuesdays.
We set up the array to hold these values like so(pseudo code to help with transition to dynamic):
Code:
var array = {nameOfSection:[
For each day
output {day:"name of day",theShifts:[
for each shiftfor this day
output "shift name",
next
next
Now we use this concept above to make the code dynamic. Basically before we initialize the array we need to do our server-side db connection and queries, then we loop through them to build the array:
Code:
<%
Option Explicit
Dim objRS
'objRS holds a record set that contains records with shift_day, shift_number
' for example
' 1st record: Monday, 1
' 2nd record: Monday, 2
' 3rd record: Monday, 3
' 4th record: Tuesday, 1
' etc
%>
<html>
<head>
<title> Shift Selection </title>
<script language="JavaScript">
<!--
var shifts;
function init(){
//to initialize them all to the same times
shifts={dayOfWeek: [
<%
dim tDay
objRS.MoveFirst
Do Until objRS.EOF
If tDay <> objRS("shift_day") Then 'If not equal to previous, than start a new array element
%>
{day: "<%=objRS("shift_day")%>",theShifts: [
<%
tDay = objRS("shift_day")
End If
%>"<%=objRS("shit_number")%>", <% 'add the shift number to inner array
objRS.MoveNext
If tDay <> objRS("shift_day") Then 'If next not equal to current, end the array element
Response.Write "]},"
End If
Loop
%>
}
//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 = frmFormName.workday.options.length - 1; i >= 1; i--) frmFormName.workday.options[i] = null;
frmFormName.workday.selectedIndex = 0;
//grab day from select box
day = elem.selectedIndex-1;
for(j=0;j<shifts.dayOfWeek[day].theShifts.length-1;j++){
document.frmFormName.workday.options[j] = new Option(shifts.dayOfWeek[day].theShifts[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>
<%
objRS.MoveFirst
tDay = ""
Do Until objRS.EOF
If tDay <> objRS("shift_day") Then
%>
<option><%=objRS("shift_day")%></option>
<%
tDay = objRS("shift_day")
End If
objRS.MoveNext
Loop
%>
</select>
<select name="workday">
<option>[Select a Shift]</option>
</select>
</form>
</body>
</html>
Now that shows us how it will work with 2 select boxes, but what you are looking to do has two minor differences:
1) there are 3 select boxes, and
2) you want to display all the options to begin with and then filter it down
Altering the code to do (2) will probably be more difficult than altering the code to do (1). Here is the alteration for (1):
Create your third array level similar to how the second array is nested in the first one above, probably will want iut to look like this(pseudo code again):
Code:
var array = {nameOfSection:[
For each ID
output {id:"value of ID",{services:[
for each service for this ID
output {"service name",{areas:[
for each area for this service
output "area name"
next
next
next
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 cvhange 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.
Concerning your recordset:
You will want to pull all of the ID's, Services, and Areas out in one recordset so you can preserve the relationships as your outputting them into your javascript array. The easiest way to loop through this and build the array will be to have a tempID and tempService variable.
Then loop through like pseudocode above, here is an example with the db stuff in it, assume our recordset is named rs_all:
Code:
rs_all.MoveFirst
'escaping to javascript to write some variables, usually I would
' response.write these but I wanted to clarify the change from
' ASP to javascript
%>
var tempID, tempService
var myArray = {chainedData:[<% 'back to ASP to handle outputting the recordset
Do Until rs_all.EOF
'if this id is not equal to the last one
If rs_all("ID") <> tempID Then
'if this isn't the first run through
If tempID <> "" Then
'end the previous id block
Response.Write "},"
End If
'Start a new ID block
Response.Write "{id:""" & rs_all("ID") & """,{services:["
End If
'Ok, now we do the same thing for service because the services are block as well instead of just comma-delimited lists in the array
If rs_all("service") <> tempService Then
'if this isn't the first run through
If tempService <> "" Then
'end the previous id block
Response.Write "},"
End If
'Start a new Service block
Response.Write "{serviceName:""" & rs_all("service") & """,{services:["
End If
'Now we just need to output the areas in a comma delimited fashion
'check if this is a new service, if so we don't need a preceding comma
If tempService = rs_all("service") Then
Response.Write ","
End If
'output area
Response.Write """" & rs_all("area") & """"
'set the tempID and tempService variables
tempID = rs_all("id")
tempService = rs_all("service")
'incrememnt recordset
rs_all.MoveNext
Loop
Ok, the above code may not be 100% as I am writing it on the fly, but I hope this is enough to get you started. Please feel free to ask questions if you get stuck or have trouble with the process,
-------------- End Tarwn's Lazy Cutn Paste ----------------
And now I'm off to work. As I mentioned before this is the basis of a tutorial on my site, but unfortunatly it is unfinished right now or I would have directed you to it
-Tarwn
01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101
29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048
ASP is executed server-side and doesn't finish sending the HTML to the browser until it is finished. By the time the browser has finished loading (and can throw an onLoad event client-side) the ASP code is cleaning up it's variables and connections and quietly going away. Since you cannot make the database connection from client-side code, your only option is to write that data out into an array so that you will still have access to it client-side later when you need it (ie, after user has made first selection)