On more than one occasion I have seen a request for examples of how to create multiple dependent lists. The technique has usually been to use client-side scripts to fill arrays. This example will retrieve results from a database and create an array with the GetRows method.
This is an extension of work originally done by others. I liked what was previously done and wanted to extend the list boxes from two to three as well as provide a means of returning the final results back to the page as an an output using the split function on the returned value from the lists.
This example will use information from a database with the following structure:
Database name="Travel.mdb" There are three tables Region", "Country", "Place"
"Region" has the following fields: RegionID - Autonumber Region - Text
"Country" has the following fields: CountryID - Autonumber RegionID - Number Country - Text
"Place" has the following fields: PlaceID - Autonumber RegionID - Number CountryID - Number Place - Text
With the database in place, let's start the scripting. Include the constants for ADO. Create the headers as desired to prevent cacheing.
'-------------------------------------------------------------------------- 'Get the page name as this script calls itself for processing the data. ' 'Check to see if request is from reset key ' 'Collect Variables and Inputs. Each selected category or subcategory value 'returns a pair of data. The pair is separated by a "pipe" character, which 'allows splitting out the individual values into an array. ' This in turns allows us to redisplay the final results for the user '--------------------------------------------------------------------------
If request("Reset")="" then Category=Request("CategorySelect") subCategory=Request("subCategorySelect") sub2Category=Request("sub2CategorySelect") else Category="" subCategory="" sub2Category="" end if
dim ARR
If Category <> "" and Category <> "NONE" then Arr = split(Category,"|") CatChosen = Arr(0) MyRegion = Arr(1)
end if
If subCategory <> "" and subCategory <> "NONE" then Arr = split(subCategory,"|") sub2CatChosen = Arr(0) MyCountry = Arr(1) End if
If sub2Category <> "" and sub2Category <> "NONE"then Arr = split(sub2Category,"|") sub3catChosen = Arr(0) MyCity = Arr(1) End if
'To show display - check if Category is not blank or unchanged '---------------------------------------------------- If MyRegion <> "" and (Request("CategorySelect") = Request("CategorySelectOld")) Then
'To show display - check if subCategory is unchanged '-------------------------------------------- If Request("subCategorySelect") = Request("subCategorySelectOld") then
'To show display - check if sub2Category is selected '-------------------------------------------- If Request("sub2CategorySelect") <> "" AND Request("sub2CategorySelect") <> "NONE" then showDisplay = true End if End if else showDisplay = false End if
'--------------------------------------------------------------- ' create and open the connection to the database ' get the first list of categories ' convert the record set to an array and clean up '---------------------------------------------------------------
Set catRS = catConn.Execute("SELECT regionID, Region " & _ "FROM Region ORDER BY Region")
catRows = catRS.getRows catRS.Close Set catRS = nothing
'--------------------------------------------------------------- 'Check for presence of first Category selection '---------------------------------------------------------------
'Process input if found '------------------------------- showSubcat = False If (CatChosen <> "" and catChosen <> "NONE") Then
CatChosen = CInt(CatChosen) showSubcat = True
'get the list of subcategories for given category '----------------------------------------------------- Set subRS = catConn.Execute("SELECT * " & _ " FROM Country " & _ " WHERE Country.RegionID = " & CatChosen & _ " ORDER BY Country")
'convert the record set to an array '----------------------------------------------- subRows = subRS.getRows subRS.Close Set subRS = nothing
End If
'--------------------------------------------------------------- 'Check for presence of second subCategory selection '---------------------------------------------------------------
showSub2cat = False
If (sub2catChosen <> "" and sub2catChosen <> "NONE") and _ (Request("CategorySelect") = Request("CategorySelectOld")) Then sub2catChosen = CInt(sub2catChosen) showSub2cat = True
'get the list of sub2categories for given category '----------------------------------------------- Set sub2RS = catConn.Execute("SELECT * " & _ " FROM Place " & _ " WHERE Place.CountryID = " & Sub2CatChosen & _ " ORDER BY Place")
'convert the record set to an array '----------------------------------------------- sub2Rows = sub2RS.getRows sub2RS.Close Set sub2RS = nothing
End If
' clean up '------------------------ catConn.Close Set catConn = nothing
'--------------------------------------------------------------- 'Begin ouput to page 'Create a list box for each category or subcategory '---------------------------------------------------------------
<FONT color='blue' ><I>What destination are you interested in?</I></FONT><br> First select a Region and then, <br> select a Country, and then <br> select a City:
<SELECT Name="CategorySelect" MULTIPLE size="5" onChange="document.DemoForm.submit();"> <OPTION Value="NONE">-- choose a Region-- <% For cnum = 0 To UBound(catRows,2) %> <OPTION Value="<%= catRows(0,cnum) %>|<%= catRows(1,cnum) %>" <% If catRows(0,cnum) = Cint(catChosen) Then %> SELECTED <% End If %> ><%= catRows(1,cnum) %> <% Next %> </SELECT>
<SELECT Name="SubcategorySelect" MULTIPLE Size="5" onChange="document.DemoForm.submit();"> <% If showSubcat = false Then %> <OPTION Value="NONE">-- no Countries yet -- <% Else %> <OPTION Value="NONE">-- choose a Country-- <% For cnum = 0 To UBound(subRows,2) %> <OPTION Value="<%= subRows(0,cnum) %>|<%= subRows(2,cnum) %>" <% If subRows(0,cnum) = Cint(sub2catChosen) Then %> SELECTED <% End If %> ><%= subRows(2,cnum) %> <% Next %> <% End If %> </SELECT>
<SELECT Name="Sub2categorySelect" MULTIPLE Size="5" onChange="document.DemoForm.submit();"> <% If showSub2cat = false Then %> <OPTION Value="NONE">-- no Cities yet -- <% Else %> <OPTION Value="NONE">-- choose a City -- <% For cnum = 0 To UBound(sub2Rows,2) %> <OPTION Value="<%= sub2Rows(0,cnum) %>|<%= sub2Rows(3,cnum) %>" <% If sub2Rows(0,cnum) = Cint(sub3catChosen) Then %> SELECTED <% End If %> ><%= sub2Rows(3,cnum) %> <% Next %> <% End If %> </SELECT>
