Programmer to ProgrammerTM  
Wrox Press Ltd  
   
  Search ASPToday Living Book ASPToday Living Book
Index Full Text
 
ASPToday Home
 
 
Home HOME
Site Map SITE MAP
Index INDEX
Full-text search SEARCH
Forum FORUM
Feedback FEEDBACK
Advertise with us ADVERTISE
Subscribe SUBSCRIBE
Bullet LOG OFF
                         
      The ASPToday Article
January 29, 2001
      Previous article -
January 26, 2001
  Next article -
January 30, 2001
 
   
   
   
Cascading Linked Selection Lists   Geoff Pennington  
by Geoff Pennington
 
CATEGORIES:  Data Access, Scripting  
ARTICLE TYPE: In-Depth Reader Comments
   
    ABSTRACT  
 
Article Rating
 
   Useful
  
   Innovative
  
   Informative
  
 43 responses

This article is a follow-up to Geoff Pennington's "Dynamic Selection Lists" article. In this work, he discusses enhancing these selection lists, for example, by allowing repopulation all the way down the list upon repopulation of a child list by a changed selection in a parent list, instead of stopping at the first child list. This is only the first of the enhancements discussed.

   
                   
    Article Discussion   Rate this article   Related Links   Index Entries  
   
 
    ARTICLE

The article I did a while back on Dynamic Selection Lists was very popular and generated a lot of feedback. Many readers asked for enhancements, specifically:

The current article deals with coding these three improvements. The same basic modification will handle all three, but the last point will be handled separately as its solution requires shifting mental gears. Note that this article covers some of the same ground as the original but a familiarity with that article is not necessary, as the new article is written to stand on its own.

A Cascading Sample

Below is a page with four selection lists. The parent list shows Publishers. Changes to it repopulate the two child lists, Employees and Titles. The child lists are independent of each other - you would not expect the list of employees to depend on the currently selected title, or vice versa. The fourth list, Authors, changes when a different title is selected or when the title list repopulates due to a change in the selected Publisher. The example code uses the Pubs database that comes with SQL Server.

image1

To accomplish these results I use two files. The page itself, CascadingSelect.asp, handles the database access and contains the HTML. The include file, CascadingSelect.inc, may be plugged into any page that needs cascading selection lists. It has been tested with Internet Explorer 5.0, Netscape 4.74, and Netscape 6.0. Both files can be downloaded from this article.

Let's look at SelectLists.asp.

  <%@ Language=VBScript %>
  <% Option Explicit %>
   <!-- #include file=CascadingSelect.inc -->
  <%
     DIM Conn, rsPrimary, rsSecondary1, rsSecondary2, rsTertiary
     DIM intFirstPub, intFirstTitle

     ' Put your connection information here.
     Set Conn = Server.CreateObject("ADODB.Connection")
     Conn.ConnectionString = "PROVIDER=SQLOLEDB" & _
     ";SERVER=CTG-GP;UID=sa;PWD=;DATABASE=Pubs"

     Conn.Open
     SET rsPrimary   = Server.CreateObject("ADODB.RecordSet")
     SET rsSecondary1 = Server.CreateObject("ADODB.RecordSet")
     SET rsSecondary2 = Server.CreateObject("ADODB.RecordSet")
     SET rsTertiary  = Server.CreateObject("ADODB.RecordSet")
  %>

Pretty standard stuff so far. I use Option Explicit to guard against typos being mistaken for undeclared variables. I declare the variables we need, open the database connection, and create a recordset object for each selection list.

Next we have an array that is central to achieving the cascading effect we need. We actually want a 3X3 array but, since JavaScript does not support multi-dimension arrays, we simulate one by making each array element an array in its own right:

<HTML>
<HEAD>
   <SCRIPT LANGUAGE= "JavaScript">
   var arrCascade = new Array(); 
   arrCascade[0] = new Array ('lstPublishers', 'lstTitles', 'arrTitles');
   arrCascade[1] = new Array ('lstPublishers', 'lstEmployees', 'arrEmployees');
   arrCascade[2] = new Array ('lstTitles', 'lstAuthors', 'arrAuthors');
   </SCRIPT>
</HEAD>

Understanding this array is essential for setting up your own pages. Look at the line that defines the first element:

arrCascade[0] = new Array ('lstPublishers', 'lstTitles', 'arrTitles');

lstPublishers is the name of the primary list box in the HTML form, which shows the names of the publishers in the Pubs database, while lstTitles shows the book titles offered by the currently selected publisher. arrTitles is another JavaScript array that's used to repopulate lstTitles when the selected publisher changes. The effect of this line is that a changed selection in lstPublishers will cause lstTitles to reload, using arrTitles. Now look at the next line:

arrCascade[1] = new Array ('lstPublishers', 'lstEmployees', 'arrEmployees');

Again we see lstPublishers, but this time it is followed by lstEmployees and arrEmployees. The effect of this line is that a changed selection in lstPublishers will cause lstEmployees to reload, using arrEmployees. So, a changed selection in lstPublishers will affect both lstTitles and lstEmployees.

The last element in arrCascade is

arrCascade[2] = new Array ('lstTitles', 'lstAuthors', 'arrAuthors');

A change to lstTitles will cause lstAuthors to reload from the array arrAuthors. Taken together, these three lines mean that a changed selection in lstPublishers will cause lstTitles and lstEmployees to reload, and a change to lstTitles will cause lstAuthors to reload. Additional lines specifying additional selection lists could be added for however many levels you want to go to.

Now we begin opening the previously defined recordsets:

<BODY>
  <%
     rsPrimary.Open "SELECT pub_id, pub_name FROM publishers ORDER BY pub_name", Conn

     IF NOT (rsPrimary.BOF AND rsPrimary.EOF) THEN
             intFirstPub = rsPrimary("pub_id")
     ELSE
             intFirstPub = 0
     END IF

Normally I use stored procedures because they are more efficient, more secure, easier to debug, and reusable, but in this case I use embedded SQL because it is easier for a reader to follow. I am retrieving pub_id and pub_name, the two fields needed for the selection list. If the recordset is not empty (IF NOT (rsPrimary.BOF AND rsPrimary.EOF) THEN) I capture the first pub_id; it will be needed later when setting the initial contents of the child lists of lstPublisher.

We continue by opening the recordset for the first of the child lists, lstTitles:

     rsSecondary1.Open "SELECT pub_id, title_id, title FROM titles ORDER BY pub_id, title", Conn 
     WriteClientArray "arrTitles", rsSecondary1, "pub_id", "title_id", "title" 

Again we open a recordset and write a client-side array. In addition to selecting the columns we need for the lstTitles selection list we also selected pub_id, which is the key for the parent list. All three columns are needed for the array arrTitles. We did not capture the first title_id - we will wait until later, when we apply a filter to the recordset.

The function WriteClientArray is in the include file, which we will discuss later. Do you recognize the name of the array, arrTitles ? You should do - we saw it previously when we were loading arrCascade.

Opening the recordset for the other child list, lstEmployees, is much like the opening the one for the parent, lstPublishers:

     rsSecondary2.Open "SELECT pub_id, emp_id, lname FROM employee ORDER BY pub_id, lname", Conn 
     WriteClientArray "arrEmployees", rsSecondary2, "pub_id", "emp_id", "lname"

Opening the recordset for the grandchild list is different only in that, since the SQL is a little longer, we set it up in a string first. Of course, if we were using stored procedures it wouldn't be an issue. We are selecting from two tables because the titleauthor table exists to resolve the many-to-many relationship between the title table and the author table. We really want the author, but we have to go through titleauthor to get it:

     DIM strSql
     strSql = "SELECT ta.title_id, a.au_id, a.au_lname "    & _
             "FROM titleauthor ta "                         & _
              "JOIN authors a ON a.au_id = ta.au_id ORDER BY ta.title_id, a.au_lname"
     rsTertiary.Open strSql, Conn 
     WriteClientArray "arrAuthors", rsTertiary, "title_id", "au_id", "au_lname" 
  %>

Now we setup the form:

   <CENTER>
      <H2>Cascading Select List</H2>
      <FORM id=form1 name=form1>
      <TABLE WIDTH=100%>
      <TR>
         <TD>Primary List (Publishers):<br>
         <%SelectBox rsPrimary, "lstPublishers", "pub_id", "pub_name" %>
         </TD>

Writing out the SELECT list with a function call (SelectBox) is one way to keep the logic separate from the presentation. We will go into the SelectBox function in more detail later, when we discuss the include file (CascadingSelect.inc). For now just note the parameters passed to it: the name of the recordset used to load the list, the name we give the list, the column that will provide the IDs in the list, and the column that will provide the displayed values:

         <TD>Secondary List (Employees): <br>#2 (Employees):<br>
           <%
              rsSecondary2.Filter = "pub_id = '" & intFirstPub & "'"
              SelectBox rsSecondary2, "lstEmployees", "emp_id", "lname" 
              RsSecondary2.Filter = 0
           %>
          </TD>
      </TR>

Setting up similar lstEmployees is similar to what we just did except that first we need to filter the recordset to only show rows related to the first - and currently selected - row in the parent recordset. After creating the list we remove the filter. People who use the include file adovbs.inc can use the line rsSecondary2.Filter = adFilterNone instead of rsSecondary2.Filter = 0. Setting up the other child list brings another wrinkle, namely, after filtering the recordset we capture the ID of the first title, if there is one. We will need it for filtering the grandchild list:

      <TR>
         <TD>Secondary List (Titles): <br>#1 (Titles):<br>
           <%
              rsSecondary1.Filter = "pub_id = '" & intFirstPub & "'"
              IF NOT (rsSecondary1.BOF AND rsSecondary1.EOF) THEN
              intFirstTitle = rsSecondary1("title_id")
              ELSE
              intFirstTitle = 0
              END IF

              SelectBox rsSecondary1, "lstTitles", "title_id", "title" 
              RsSecondary1.Filter = 0
            %>
         </TD>
         <TD></TD>
      </TR>

Once we set up the final select list the HTML is essentially complete:

      <TR>
          <TD>Tertiary List (Authors):<br>
            <%
               rsTertiary.Filter = "title_id = '" & intFirstTitle & "'"
               SelectBox rsTertiary, "lstAuthors", "au_id", "au_lname" 
               rsTertiary.Filter = 0
            %>
         </TD>
         <TD></TD>
      </TR>
      </TABLE>
      </FORM>
   </CENTER>

Finally, we need to clean up after ourselves by getting rid of the objects we are finished with:

  <%
     rsPrimary.Close
     SET rsPrimary = Nothing
     rsSecondary1.Close
     SET rsSecondary1 = Nothing
     rsSecondary2.Close
     SET rsSecondary2 = Nothing
     rsTertiary.Close
     SET rsTertiary = Nothing

     Conn.Close
     SET Conn = Nothing
  %>
</BODY>
</HTML>

Server Side Code in the Include File

This all look pretty simple so far, but life gets more interesting as we look at the include file, CascadingSelect.inc. Of course, we want the implementation to be simple - that way we can use the include file without having to think about it too hard.

The include file contains two server-side subroutines, SelectBox() and WriteClientArray(), and one client side function - ChangeOptions(). We'll start with SelectBox().

Many of you long ago worked out how to loop through a recordset and create an HTML select list. It isn't very hard. Since it is a common task you probably have a routine like this one, either in an include file or in a COM object, so you don't have to code it from scratch every time. Here is the first line:

SUB SelectBox(rsOptions, strName, strValue, strDisplay)

The parameters it takes, in order, are the recordset containing the data, the name to assign to the list, the column whose value will be passed to the server when the form is submitted, and the column whose value will appear in the dropdown list. If you remember that the first call to this function, from the main ASP page, was SelectBox rsPrimary, lstPublishers, pub_id, pub_name, you will better understand how the parameters are used.

The first thing the subroutine does is write out the <SELECT> tag:

   DIM strSelect
   strSelect = vbCRLF & "<SELECT name=" & chr(34) & strName & chr(34)    & _
      " id=" & chr(34) & strName & chr(34)                               & _
      " OnChange=" & chr(34) & "ChangeOptions('"                         & _
      strName & "')" & chr(34) & ">" & vbCRLF

   Response.Write strSelect

vbCRLF is just a carriage-return / line-feed, which will have no effect on the processing but will make the HTML easier to read if I need to view the source in the browser for any reason. The chr(34) s provide quotes where needed. You can see that, using the parameters from the first call, the name of the list box will be lstPublishers.

Pay particular attention to the function for the OnChange event. When it reaches the browser it will be OnChange="ChangeOptions('lstPublishers')". A reader of the original article pointed out that, for the non-cascading selection lists, it is better to pass an object reference like so: OnChange="ChangeOptions(this)". For reasons we will explore when we get to ChangeOptions() we cannot use an object reference here. We write out the string with Response.Write strSelect. It would be somewhat more efficient to concatenate the whole thing together and do just one Write at the end, but I find the code easier to deal with if the Writes come in logical chunks.

We continue by looping through the recordset and writing out the <OPTION>s as we go (as you can see below). The MoveFirst after the loop is to reset the recordset in case our program needs to use the same recordset for another purpose; any routine using a recordset should be able to assume the recordset is ready. The developer should not have to think about whether the recordset has been used or not, and what state it was left in.

   Do Until rsOptions.EOF
   Response.Write "<OPTION value=" & chr(34)              & _
            rsOptions.Fields(strValue) & chr(34) & ">"    & _
       Trim(rsOptions.Fields(strDisplay))             & _
            "</OPTION>" & vbCRLF
            rsOptions.MoveNext
   LOOP
   rsOptions.MoveFirst
   Response.Write "</SELECT>"
END SUB

Next we come to the subroutine that writes out the arrays we will use to reset the list boxes. There are other ways to make data available for client-side processing - XML data islands, for example. However, writing out an array is simple and works with any browser that supports JavaScript:

   'Populate strArrName as a two dimensional array.
   '[n][0] =value, [n][1] =display
   SUB WriteClientArray(strArrName, rsSource, strKey, strValue, strDisplay)

We pass in the name we will assign to the array, the source recordset, and the names of three columns (the key to be used when we search the array, the value to be passed when we submit the form, and the value that will display in the list box). Recall that the first call to this subroutine is

WriteClientArray "arrEmployees", rsSecondary1, "pub_id", "emp_id", "lname"

Breaking up the word "SCRIPT" in the following tag may seem a little odd, but it is necessary because otherwise the server-side script engine gets confused and thinks we are nesting script blocks, even though this is meant to be client-side code:

   Response.Write "<SC" & "RIPT LANGUAGE= ""JavaScript"">" & _
            "var " & strArrName & " = new Array(); " & vbCRLF

As we loop through the recordset we set each element of the array we just declared (and gave the name contained in strArrName) to another array. This is because, as mentioned earlier, JavaScript does not actually support multi-dimension arrays; we have to simulate one:

   DIM intRow
    intRow = 0
    DO UNTIL rsSource.EOF
     Response.Write strArrName & "[" & CStr(intRow)                & _
             "] = new Array ('"                                    & _
                    Replace(Trim(rsSource (strKey)), "'", "\'")         & _
             "', '" & Replace(Trim(rsSource(strValue)), "'", "\'")	& _
             "', '" & Replace(Trim(rsSource(strDisplay)), "'", "\'") & _
                    "');" & vbCRLF

     intRow = intRow + 1
     rsSource.MoveNext
    Loop

Pay attention to the Replace() functions used here. We are looking for single-quote characters that might appear in a name (like "O'Leary" for example) and replacing them with " \' "; that is, we are "escaping" the single quotes so that they do not cause problems with the single quotes that enclose the elements in the array.

My previous selection list article used a custom-coded routine, which used substring functions to examine each character and escape the single-quotes. A reader of this article wrote to me to point out that using the Replace() function is easier to code, easier to read, and will run faster as well.

Now all we have to do is close out the client-side script tag, end the subroutine, and close out the server-side script tag. As before we reset the recordset so it will be ready for anything else that uses it.

    Response.Write " </SC" & "RIPT>"
    rsSource.MoveFirst
   END SUB
   </script>

Client Side Code in the Include File

Now the real fun: ChangeOptions() - this is the client side code. When you come right down to it we are interested in a client side effect: change the selected item in a list and other lists change, without going to the server.

Here are the things this function must do:

Initializing the variables is not really necessary, but is a good practice and can often prevent bugs, especially when your code makes decisions based on changing values.

<script LANGUAGE="Javascript">
function ChangeOptions(lstPrimary) 
{ 
   var listLen  = 0;
   var strKey   = "";
   var lstSecondary   = "";
   var arrSource      = "";

The next section of code checks for an empty primary list. How could that happen? If list A changes list B, and list B changes C, then from C's point of view B is the primary. But if the current selection in A has no related items in B, then B will be empty when we come to change C. We will then want to leave C blank:

//If the current 'primary' list is empty, we want to skip this statement, 
//but still blank out subordinate lists without re-populating them.
   if (eval("document.forms[0]." + lstPrimary + ".options.length") > 0)
{
   var strKey = eval("document.forms[0]." + lstPrimary + ".options[document.forms[0]." + lstPrimary + ".selectedIndex].value");
}

I mentioned earlier that there are advantages to passing a reference to the list as an object. If we had done so, the two eval() functions above would be unnecessary; for example, we could have simply written

if (document.forms[0].lstPrimary.options.length > 0). However, the next "if" condition shows why we could not do so:

//Identify each list to be affected by the change in lstPrimary
   for (var i = 0; i < arrCascade.length; i++) 
   { if (arrCascade[i][0] == lstPrimary)

You see, we need to compare lstPrimary to the contents of arrCascade, and there is no way to see if two objects (or object references), are the same. Now, I hate to say that something can't be done, but I did a fair amount of searching and came up empty handed. If you can prove me wrong I will be happy to improve my code!

Let's not gloss over the loop we just entered. We will check each row in arrCascade and cannot stop when we find the first "hit", because we may find others:

{
   lstSecondary     = arrCascade[i][1];
   arrSource 	= arrCascade[i][2];

Having found a matching array member we keep determine the list to be reset and the array to use. We continue by emptying out the child list:

   //Remove all current options from the secondary list
   eval("document.forms[0]." + lstSecondary + ".options.length = 0");

Then, if the primary list is not empty (remember how we set strKey ?) we begin the function's inner loop, looking for values to plug into the child list. It is important to set listLen = 0 when we start this loop; the select lists become very strange if you don't!

   if (strKey != 0)
{
   listLen = 0;
   for (var j = 0; j < eval(arrSource + ".length"); j++) 
{ 

As we loop through arrSource, every time we find an entry that matches the key from the primary list we add the entry to the child list:

if (eval(arrSource + "[j][0]") == strKey)
    { 
    eval("document.forms[0]." + lstSecondary + ".options[listLen] = new Option(" + arrSource + "[j][2], " + arrSource + "[j][1])" );
    listLen = listLen + 1;
    }

   } //End inner loop, which resets the target list

And then we are done with the inner loop. If the child list is not empty we make sure the first option is selected. Some browsers will automatically do this, but not all.

if (listLen > 0)
   {eval("document.forms[0]." + lstSecondary + ".options[0].selected = true");}

   }    // End "if (strKey != 0)"

Next, while still inside the outer loop, we have another function call. Do you recognize it? Of course! ChangeOptions() is the function we are in right now! However, now our secondary list takes a turn as the primary. This is the real secret to making the lists cascade.

   ChangeOptions(lstSecondary);   //Recursive call; change any lists affected
// by the change to this list.

Those of you with good computer science backgrounds, either self-taught or from college, recursive function calls, i.e. functions that call themselves, will be a concept that you are quite comfortable with. They are a type of loop in which a function calls itself repeatedly. Like any loop there must be a limiting condition or the program will eventually either time out or bomb when it runs out of system resources. In this case the limit is reached when the current list has no matches in arrCascade.

   }
} //End outer loop, which looks for lists that need resetting.

Now we can end the outer loop, but we are still not quite finished. Versions of Netscape prior to 6.0 need to be "nudged" to make the changes appear on the screen. Here we are using the difference in document object models supported by the different browsers to include this functionality.

//if (document.all == null) //Not using Internet Explorer
 // {history.go(0);} 
 if (document.layers != null) //Not using Internet Explorer or NS6
  {history.go(0);} 
}
</script>

A Different Situation

That's how you can make the selection lists cascade, either to an arbitrary number of levels or to multiple children, but what about the third case, where two parent lists cascade to a shared child? That trick can be performed with the same basic code; here is a screen shot showing one implementation:

image2

Since the process is essentially the same and this article is getting quite long enough, we will skip an in-depth discussion. The code for CascadeTwoToOne.asp is included with the download material for your inspection. This implementation relies on a trick: the two parent lists (Publishers and Jobs) are not truly independent. The Jobs list query is a cross-join between the Publishers and Jobs tables. Doing it this way has two disadvantages. First, choosing a different Publisher makes you lose the currently selected Job; you have to select it again. Second, cross-joins can easily return very large result sets (for example, if each table has 100 rows, the cross join will have 10,000 rows). Perhaps a better result could be achieved with an array that says "if A or B changed, concatenate the current selections from those lists and find the corresponding items for list C". As many a textbook says, that is left as an exercise for the reader!

Parting Thoughts

To be honest, I had to think a bit to figure out how to achieve the results here. The solution was not immediately obvious. However if you break the problem down into pieces you can work it out. None of the techniques are overwhelmingly difficult. The main requirements are the ability to visualize a solution, and the patience to work it through. Happy coding!

 
 
   
  RATE THIS ARTICLE
  Please rate this article (1-5). Was this article...
 
 
Useful? No Yes, Very
 
Innovative? No Yes, Very
 
Informative? No Yes, Very
 
Brief Reader Comments?
Your Name:
(Optional)
 
  USEFUL LINKS
  Related Tasks:
 
 
   
  Related ASPToday Articles
   
  • Dynamic Selection Lists (April 14, 2000)
  •  
           
     
     
     
           
      Search the ASPToday Living Book   ASPToday Living Book
     
      Index Full Text Advanced 
     
     
           
      Index Entries in this Article
     
  • arrays
  •  
  • child lists
  •  
  • client-side code
  •  
  • dynamic selection lists
  •  
  • enhancements
  •  
  • grandchild lists
  •  
  • include files
  •  
  • JavaScript
  •  
  • JavaScript arrays
  •  
  • MoveFirst method
  •  
  • multidimensional arrays
  •  
  • parent lists
  •  
  • populating
  •  
  • Recordset object
  •  
  • recordsets
  •  
  • repopulating
  •  
  • server-side code
  •  
  • simulating
  •  
     
     
    HOME | SITE MAP | INDEX | SEARCH | REFERENCE | FEEDBACK | ADVERTISE | SUBSCRIBE
    .NET Framework Components Data Access DNA 2000 E-commerce Performance
    Security Admin Site Design Scripting XML/Data Transfer Other Technologies

     
    ASPToday is brought to you by Wrox Press (http://www.wrox.com/). Please see our terms and conditions and privacy policy.
    ASPToday is optimised for Microsoft Internet Explorer 5 browsers.
    Please report any website problems to webmaster@asptoday.com. Copyright © 2001 Wrox Press. All Rights Reserved.