It is very common to see hierarchical data in many of the
forms used in web applications. The data is presented to the user in combo
boxes. When the user selects the data in the first level combo box, the next
level combo box gets filled with the data that is associated to the selected
value in the previous level combo box.
A very good example is a form where the user needs to
select a geographical region, a country within that region and a state within that
country. If the user changes the
geographical region, the countries and states within that geographical region
need to be shown for selection.
Database
The database that is used in this example, has the following
tables:
GEO_REGION
Column Name
Description
GEO_REGION_ID
Primary Key
GEO_REGION
Name of the Geographical Region
COUNTRY
Column Name
Description
COUNTRY_ID
Primary Key
GEO_REGION_ID
Foreign Key
COUNTRY
Name of the Country
STATE
Column Name
Description
STATE_ID
Primary Key
COUNTRY_ID
Foreign Key
GEO_REGION_ID
Foreign Key
STATE
Name of the State
Code Listing
Hierarchy.asp
<%@ Language=VBScript %>
<% Option Explicit %>
<%
Call Main()
Sub Main()
' If the form is submitted, just display the selected country and state
If Request.Form("cmdSubmit") <> "" Then
Response.Write "<B> Geographical Region = " & Request.Form("lstGeoRegion") & _
" Country = " & Request.Form("lstCountry") & " State = " & Request.Form("lstState") & "</B>"
Exit SubEnd IfDim objConnection ' ADO Connection object
Dim strSQL ' SQL query to be executed
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "DSN=Hierarchy;UID=sa;PWD=;"
objConnection.Open
Dim rsGeoRegion ' recordset that holds the Geographical Region Information
Dim rsCountry ' recordset that holds the Country Information
Dim rsState ' recordset that holds the State Information
Dim strGeoRegion ' holds the Geographical Region ID
Dim strCountry ' holds the Country ID
Dim strState ' holds the State ID
Set rsGeoRegion = Server.CreateObject("ADODB.Recordset")
Set rsCountry = Server.CreateObject("ADODB.Recordset")
Set rsState = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM GEO_REGION"
Set rsGeoRegion = objConnection.Execute(strSQL)
' Check if theres a Geographical Region selected by the user
' If not, just take the first Geographical Region as the
' filter for the Country List
strGeoRegion = Request.Form("lstGeoRegion")
If strGeoRegion = "" ThenIf Not rsGeoRegion.EOF Then
strGeoRegion = rsGeoRegion("GEO_REGION_ID")
End IfEnd IfIf strGeoRegion <> "" Then
strSQL = "SELECT * FROM COUNTRY WHERE GEO_REGION_ID = '" & strGeoRegion & "'"
Set rsCountry = objConnection.Execute(strSQL)
strCountry = Request.Form("lstCountry")
If strCountry = "" Or Request.Form("hid_GeoRegion_Changed") = "True" ThenIf Not rsCountry.EOF Then
strCountry = rsCountry("COUNTRY_ID")
End IfEnd If
strSQL = "SELECT * FROM STATE WHERE COUNTRY_ID='" & strCountry & "'" & _
" AND GEO_REGION_ID = '" & strGeoRegion & "'"
Set rsState = objConnection.Execute(strSQL)
End If
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<CENTER>
<FORM NAME=frmHierarchy METHOD=post ACTION="Hierarchy.asp">
<INPUT TYPE=HIDDEN NAME=hid_GeoRegion_Changed>
<INPUT TYPE=HIDDEN NAME=hid_Country_Changed>
<P><H2>Hierarchial Selection of Items</H2></P>
<TABLE CELLSPACING=5 CELLPADDING=5 BORDER=0 ALIGN="CENTER">
<TR>
<TD>Geographical Region: </TD>
<TD><SELECT id=lstGeoRegion name=lstGeoRegion onchange="ChangeGeoRegion()">
<%
' Add the geographical regions to the list
If Not rsGeoRegion.EOF ThenDo While Not rsGeoRegion.EOF
strGeoRegion = rsGeoRegion("GEO_REGION_ID") & " - " & rsGeoRegion("GEO_REGION")
If rsGeoRegion("GEO_REGION_ID") = Request.Form("lstGeoRegion") Then %>
<OPTION VALUE="<%=rsGeoRegion("GEO_REGION_ID")%>" SELECTED> <%=strGeoRegion%></OPTION>
<%
Else
%>
<OPTION VALUE="<%=rsGeoRegion("GEO_REGION_ID")%>" > <%=strGeoRegion%></OPTION>
<%
End If
rsGeoRegion.MoveNext
LoopEnd If
'Reset the record pointer to the first record
rsGeoRegion.MoveFirst
%>
</SELECT></TD>
</TR>
<TR>
<TD>Country: </TD>
<TD><SELECT id=lstCountry name=lstCountry onchange="ChangeCountry()" >
<%
' Add the countries to the list
If Not rsCountry.EOF ThenDo While Not rsCountry.EOF
strCountry = rsCountry("COUNTRY_ID") & " - " & rsCountry("COUNTRY")
If rsCountry("COUNTRY_ID") = Request.Form("lstCountry") Then
%>
<OPTION Value="<%=rsCountry("COUNTRY_ID")%>" SELECTED> <%=strCountry%></OPTION>
<%
Else
%>
<OPTION Value="<%=rsCountry("COUNTRY_ID")%>" > <%=strCountry%></OPTION>
<%
End If
rsCountry.MoveNext
LoopEnd If
'Reset the record pointer to the first record
rsCountry.MoveFirst
%>
</SELECT></TD>
</TR>
<TR>
<TD>State: </TD>
<TD><SELECT id=lstState name=lstState>
<%
' Add the States to the list
If Not rsState.EOF ThenDo While Not rsState.EOF
strState = rsState("STATE_ID") & " - " & rsState("STATE")%>
<OPTION Value="<%=rsState("STATE_ID")%>" > <%=strState%></OPTION>
<%
rsState.MoveNext
LoopEnd If
%>
</SELECT></TD>
</TR>
</TABLE>
<P><INPUT id=cmdSubmit name=cmdSubmit type=submit value=Submit></P>
</FORM>
</CENTER>
</BODY>
<SCRIPT LANGUAGE="JavaScript">
function ChangeGeoRegion()
{
document.frmHierarchy.hid_GeoRegion_Changed.value = "True";
document.frmHierarchy.submit();
}
function ChangeCountry()
{
document.frmHierarchy.hid_Country_Changed.value = "True";
document.frmHierarchy.submit();
}
</SCRIPT>
</HTML>
<%
' Release the connection and recordsets
Set objConnection = NothingSet rsCountry = NothingSet rsState = NothingEnd Sub
%>
Explanation of the Code
The code fetches the values from the GEO_REGION table. It
then checks to see if it is the first time the page gets loaded. If so, the
COUNTRY records that match the first GEO_REGION are selected into the country
combo box. The state combo box is filled with the records that has the first
GEO_REGION and COUNTRY within that.
If the user selects a new GEO_REGION, then the COUNTRY
records associated with that GEO_REGION are selected. At the same time the STATE
records with the selected GEO_REGION/COUNTRY is also selected.
There are two hidden form fields named hid_GeoRegion_Changed
and hid_Country_Changed that will help determine if the selection by the user
changed the Geo Region Or Country values. Once the form is submitted, the code
displays the selected values in the browser window.
Pre-requisites to run the code
The code expects a system DSN by name "Hierarchy" and
accesses the database using the user id "sa" with a blank password. The
database is created in SQL Server 7.0. You just need to run the script that
comes along with this article. The script has the SQL statements to create the
tables and populate some sample data.
About the Author
Raja is a software consultant living in San Jose working on an
e-commerce product and will answer any questions regarding
this article.
