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.
|
|
<%@ LANGUAGE="VBSCRIPT" %>
<!--#Include virtual = "adovbs.inc"-->
<%
Response.Buffer=true
Response.AddHeader "cache-control", "private"
Response.AddHeader "pragma", "no-cache"
Response.ExpiresAbsolute = #January 1, 1990 00:00:01#
Response.Expires=Now()-1
Response.AddHeader "Cache-Control", "must-revalidate"
Response.AddHeader "Cache-Control", "no-cache"
|
|
'--------------------------------------------------------------------------
'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
'--------------------------------------------------------------------------
|
|
strScriptName = Request.ServerVariables("SCRIPT_NAME")
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 catConn = Server.CreateObject("ADODB.Connection")
catConn.Open "DRIVER=Microsoft Access Driver (*.mdb); " & _
"DBQ=" & Server.MapPath("travel.mdb")
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
'---------------------------------------------------------------
|
|
%>
<HTML>
<HEAD>
<TITLE>Multiple Dependent Lists</TITLE>
</HEAD>
<H2>Multiple Dependent Lists</H2>
<HR color="red">
<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:
<P>
<FORM Name="DemoForm" Action="<%=StrScriptName%>" Method=Post>
<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>
<Input Type="hidden" Name="CategorySelectOld" Value="<%=Request("CategorySelect")%>">
<Input Type="hidden" Name="subCategorySelectOld" Value="<%=Request("subCategorySelect")%>">
</FORM>
<%
If ShowDisplay=True then
Response.Write (" <FONT color='red' >Welcome to " & MyCity & "," & MyCountry & " in " & MyRegion & "</FONT> <br>")
End if
%>
</BODY>
</HTML>
| (From: aspkey)
|
Sitemap Links Advertising
Copyright © 2003-2005 downcodes.comregistration numbers:Hebei ICP: 05003145
