ASP 101 - Active Server Pages 101 - Web04
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
What is ASP?
VBScript Classes: Part 1 of N
Migrating to ASP.NET

QUICK TIP:
Spell Check from Within Visual Studio
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Presentation Of Hierarchical Data

By: Raja Mani

MS Word version of this article: ../../articles/hierarchy/Hierarchy.doc
Code for this article: HierarchySrc.zip

Introduction

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 Sub
	End If
	Dim 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 = ""  Then
		If Not rsGeoRegion.EOF Then
			strGeoRegion = rsGeoRegion("GEO_REGION_ID")
		End If
	End If
	If 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" Then
			If Not rsCountry.EOF Then
				strCountry = rsCountry("COUNTRY_ID")
			End If
		End 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 Then
		Do 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
		Loop
	End 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 Then
		Do 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
		Loop
	End 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 Then
		Do While Not rsState.EOF 
			strState = rsState("STATE_ID") & " - " & rsState("STATE")%>
		<OPTION Value="<%=rsState("STATE_ID")%>" > <%=strState%></OPTION>
		<%
			rsState.MoveNext
		Loop
	End 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 = Nothing
	Set rsCountry = Nothing
	Set rsState = Nothing
End 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.


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers