|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
March 29th, 2005, 06:45 AM
|
||||
|
||||
|
Dynamic dependant listboxes
One of the most common questions asked by visitors to these forums is "How can I fill a listbox based on the choice made in another listbox". The actual question takes many varied forms, but the bottom line is everybody wants to know how they can have a list box whose values change dynamically based on selections in another listbox. So here's my attempt at what is hopefully a scalable solution to this common question.
Part 1: The first step is to retrieve relevant records from the database which will be used to populate the listboxes. In this example we will create a listbox of country names, and a listbox of city names for those countries, so we need to retrieve a recordset containing all country and city names. The database is set up so that the countryID is a foreign key in the city names table so we can perform a join of the two tables. Code:
<% Option Explicit
'declare some variables
dim objRS, strSQL, arrRS
'create recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.mappath("testDB.mdb")
'Construct SQL statement
strSQL = "SELECT x.countryID, x.countryName, y.cityID, y.cityName FROM tblCountry AS x INNER JOIN tblCity AS y ON x.countryID = y.countryID ORDER BY x.countryName, y.cityName;"
'execute SQL statement to return a recordset
objRS.Open strSQL
'build an array from the recordset using GetRows
arrRS = objRS.GetRows
'close the recordset
objRS.close
set objRS = nothing
'each of the fields from the sql statement will be referenced in the function call
'by their numeric position, starting from 0, so in this example:
'countryID = 0
'countryName = 1
'cityID = 2
'cityName = 3
%>
Which is quite straightforward. The point to note here is that the order in which you specify the field names in the sql statement is the order of the 'columns' in the array that will be created when the GetRows function is called. We will be referencing the ordinal positions of the array columns in the function call later, so it is probably a good idea to place a reference comment in your code as a reminder, as shown in the last 6 lines of the above code. Part 2: Before getting to the function that does all the grunt work I'm going to show the markup and function call. This is actually the only bit of code that needs to be on the page. The database call above, and the function call below can be called from an include file, keeping the logic separate from the markup. So: Code:
<style type="text/css">
.width150 {
width: 150px;
}
</style>
<form id="testform" method="post">
<%= buildLinkedList("listCountry",0,1,1,"Select a country","width150","listCity",2,3,1,"Select a city","width150") %>
</form>
Nothing too daunting (I hope!). A standard style declaration, which would normally be in an external CSS file, and a very simple form. But what's that long line of meaningless code in the middle of the form? That, dear friends, is the call to the function that is going to do all the work for us. And so, without further ado ... Part 3: The grunt work is wrapped up in a function so that it can be placed in an include file, and can also be referenced numerous times throughout an application. There are four processes being carried out inside the function: 1) Build the first listboxThe function is called with 12 parameters, 6 for each listbox. The parameters, from left to right, are: Listbox 1 Code:
<%
function buildLinkedList(list1name,list1value,list1text,lis t1size,list1prompt,list1class,list2name,list2value ,list2text,list2size,list2prompt,list2class)
dim strList1, i, strValue, strAssocArr, strAssocArrElem, strList2, strArray, strFunctions
'build the output for the first list
strList1 = "<select class='" & list1class & "' name='" & list1name & "' size='" & list1size & "' onchange='getList2(this);'>" & VbCrLf
strList1 = strList1 & "<option value='' selected='selected'>" & list1prompt & "</option>" & VbCrLf
for i = 0 to uBound(arrRS,2)
if arrRS(list1text,i) <> strValue then
strValue = arrRS(list1text,i)
strList1 = strList1 & "<option value='" & arrRS(list1value,i) & "'>"
strList1 = strList1 & arrRS(list1text,i) & "</option>" & VbCrLf
if len(strAssocArr) > 0 then
strAssocArr = left(strAssocArr, len(strAssocArr) - 3) & VbCrLf & ");" & VbCrLf
end if
strAssocArr = strAssocArr & "assocArray[""" & list1name & "=" & arrRS(list1value,i) & """] = new Array(" & VbCrLf
strAssocArr = strAssocArr & """"",""" & list2prompt & """," & VbCrLf
end if
strAssocArrElem = """" & arrRS(list2value,i) & """, """ & arrRS(list2text,i) & """," & VbCrLf
strAssocArr = strAssocArr & strAssocArrElem
next
strList1 = strList1 & "</select>" & VbCrLf
'build the output for the second list
strList2 = "<select class='" & list2class & "' name='" & list2name & "' size='" & list2size & "'>" & VbCrLf
strList2 = strList2 & "<option value=''> </option>" & VbCrLf
strList2 = strList2 & "</select>" & VbCrLf
'build the associative array to populate list 2
strAssocArr = mid(strAssocArr, 1, len(strAssocArr)-3) & ");"
strArray = "<script type='text/javascript' language='javascript'>" & VbCrLf
strArray = strArray & "var assocArray = new Object();" & VbCrLf
strArray = strArray & strAssocArr & VbCrLf
'build the javascript functions to operate the listboxes
strFunctions = "function getList2(listOptions){" & VbCrLf
strFunctions = strFunctions & " var thisform = listOptions.form;" & VbCrLf
strFunctions = strFunctions & " clearDropDown(thisform." & list2name & ");" & VbCrLf
strFunctions = strFunctions & " var newvalue = listOptions.name + ""="" + listOptions.options[listOptions.selectedIndex].value;" & VbCrLf
strFunctions = strFunctions & " fillDropDown(thisform." & list2name & ", newvalue); // fill the 2nd dropdown options" & VbCrLf
strFunctions = strFunctions & "}" & VbCrLf
strFunctions = strFunctions & "function clearDropDown(listOptions){" & VbCrLf
strFunctions = strFunctions & " for (var i = listOptions.options.length - 1; i >= 0; i--){" & VbCrLf
strFunctions = strFunctions & " listOptions.options[i] = null;" & VbCrLf
strFunctions = strFunctions & " }" & VbCrLf
strFunctions = strFunctions & " listOptions.selectedIndex = -1;" & VbCrLf
strFunctions = strFunctions & "}" & VbCrLf
strFunctions = strFunctions & "function fillDropDown(listOptions, vValue){" & VbCrLf
strFunctions = strFunctions & " if (vValue != """" && assocArray[vValue]){" & VbCrLf
strFunctions = strFunctions & " var arrX = assocArray[vValue];" & VbCrLf
strFunctions = strFunctions & " for (var i = 0; i < arrX.length; i = i + 2){" & VbCrLf
strFunctions = strFunctions & " listOptions.options[listOptions.options.length] = new Option(arrX[i + 1], arrX[i]);" & VbCrLf
strFunctions = strFunctions & " }" & VbCrLf
strFunctions = strFunctions & " } else listOptions.options[0] = new Option("""", """");" & VbCrLf
strFunctions = strFunctions & "}" & VbCrLf
strFunctions = strFunctions & "</script>" & VbCrLf
buildLinkedList = strList1 & VbCrLf & strList2 & VbCrLf & strArray & VbCrLf & strFunctions & VbCrLf
end function
%>
Phew!! There's a lot of work going on there, and it's probably easier to see what is happening by viewing the source of the final output. Attached is a working copy of the above script, with supporting database. Place the script and database in the same folder and load the page in your browser to see it in action. Post any questions about the script or suggestions to make the script better here. Enjoy! EDIT: Need to work with 3 (or more) linked dropdowns? A sample Triple Linked Dynamic Dropdowns script is now available.
__________________
-
thought-after | my thoughts on web development Get Firefox, the developers browser Budget hosting - recommended [/left] Last edited by Lafinboy : February 21st, 2006 at 04:34 PM. Reason: Updated code
|
|
#2
March 29th, 2005, 08:39 AM
|
||||
|
||||
|
/me nitpicks!
<style> should be: Code:
<style type="text/css"></style> And the "name" attribute is now illegal on all non-input elements. Thus <form name=""> is illegal.
|
|
#3
March 29th, 2005, 09:38 AM
|
||||
|
||||
|
Nitpicks indeed
 But if we're gonna put up samples then they should be accurate and correct. Code and attachments modified.
|
|
#4
May 26th, 2005, 01:15 PM
|
|||
|
|||
|
does this work in VBScript, I need to build two lists. the first one is a list of acct_groups. When the user selects a specific account_group, I want the second list to be populated with the specific accounts in that group. The way I get the data for the second group is a little tricky. some of the groups I pull from the database while other I have to manually enter the values into the array. I have the values for the second list but I can assign them to the list. Specifically the call in your code
listOptions.options[listOptions.options.length] = new Option(arrX[i + 1], arrX[i]);" & VbCrLf gives me an error. Error Type: Microsoft VBScript compilation (0x800A03F2) Expected identifier /comm_rep/comm_REPORTS.asp, line 83, column 82 document.form1.acct_lvl_2.options(document.form1.a cct_lvl_2.options.length) = new Option(acct_name, acct_name) I am not sure how to implement your code for this situation. Any help would be greatly appreciated. Thanks
|
|
#5
May 26th, 2005, 01:56 PM
|
||||
|
||||
|
options isn't a function. You msust have the wrong type of brackets.
Read lafin's code properly. Do you mean you want to use client-side vbscript? Or server-side?
|
|
#6
May 26th, 2005, 04:50 PM
|
||||
|
||||
|
Quote:
actually, I fear name is essential for client side coding. it's used when refering to the form or frame with such code: Code:
document.forms["form1"] as far as I've seen, the name of the form, not its ID, is used to identify it in the forms collection. when would the name attribute be totally deprecated?
|
|
#7
May 27th, 2005, 01:48 PM
|
|||
|
|||
|
i wanted to use client side script. The first list is loaded by the server and I would like to load the second one using client side script..I see the mistake I made in my code..how does the client vs server side change this?
|
|
#9
June 27th, 2005, 10:39 PM
|
|||
|
|||
|
Well I hate to regress in complexity so please forgive me I'm a little green when it comes to javascript. I'm trying to modify the code in your post to call database information into an array and instead of display a second list box display a label with the corosponding value from the record set. I'm only selecting 2 fields from one table. I'd like to have a drop down menu listing all the values in one column, and when selected have the corosponoding value displayed in a label next to the list box. My problem comes when dealing with the javascript function that builds the second list box. I get an error that options.length is null or not an object. I'm not sure how to change this into a label with only one possible value. I've been able to populate the list as desired and have the second list box contain only the corosponding value, but I need that second value to be displayed in a label. Any modification of the example code to do this would be greatly appreciated.
Thanks
|
|
#10
July 12th, 2005, 02:25 PM
|
|||
|
|||
Help tweeking this excellent code
I have been struggling with these dynamic dependent lists for a while this code is the only one I got to work correctly.
I have one request. Is it possible to split a comma separated string into a list in this code? Where and How would I do it? I have 1 table 4 colums ID, States, State_Abbreviation, Cities it is the cities that I am having problems with they come in one long line New York,Albany,Buffalo etc. I need to break them into a list. MS Access 2000 VBscript Thank you
|
|
#11
November 14th, 2005, 01:27 AM
|
|||
|
|||
|
Yeah this example really helpful. Thanks for the concept!
|
|
#12
November 14th, 2005, 01:52 AM
|
||||
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
