Many articles detail how to create "independent" HTML select objects with
ASP. However, I have not found a good article about creating "dependent"
HMTL select objects with ASP. This article details how to leverage several
technologies -- both client- and server-side -- to create dependent HTML
select objects with ASP. In this article, I will use the following
technologies:
Active Server Pages (ASP)
ActiveX Data Objects (ADO)
SQLServer 7.0
DHTML (<SPAN> elements in particular)
Let me begin with a brief definition of dependent HTML select objects and
an introduction to the example I will use to demonstrate the concepts in
this article. A dependent HTML select object is simply a select box whose
option values depend wholly on some previously selected value.
To illustrate the creation of these dependent HTML select objects, I will
create an HTML form that allows users to select a product from a catalog.
You could use a single HTML select box to accomplish this task, however,
the resulting select box would be rather unruly and require a lot of user
scrolling.
Let's suppose we have a product catalog with several product types,
for simplicity's sake, let's say we only sell books and software.
Now, suppose we have several suppliers for each of these product types,
some suppliers offer both product types (i.e., Microsoft sells software and
books) and some offer just one type of product (i.e., Wrox sells books, but
not software.) Now let's also assume each supplier can offer several
products.
To solve this problem, I will use three select objects:
Product Type
Vendor
Product
The Vendor select object's option values depend on the product type
selected from the Product Type select object. The Product select
object's option values will depend on a combination of the product
type and vendor selected. To control the select object's option
values, I will use several JavaScript arrays and functions that will be
created dynamically with ASP. The Vendor and Product select objects will
be enclosed in <SPAN> tags so I can alter their contents with
JavaScipt. Below is a diagram representing our HTML.
Pretty simple! Let's get started . . .
Database Schema
OK, we'll create our very (perhaps overly) simplified data schema.
We'll use three tables:
t_product_types
t_vendors
t_products
t_product_types
This table stores information about each product type we will offer our
customers.
Column Name
Data Type (length)
Notes
pt_id
Integer
Auto-incrementing primary key used to associate a
product with a product type
pt_title
String(50)
Name of the product type
Create Table dbo.t_product_types
(
pt_id int Identity (1, 1) Not Null,
pt_title varchar(50) Not Null
)
Go
Alter Table dbo.t_product_types Add Constraint
PK_t_product_types Primary Key Nonclustered
(
pt_id
)
Go
Sample Data
pt_id
pt_title
1
Books
2
Software
t_vendors
This table stores information about the vendors whose product we will offer
our customers.
Column Name
Data Type (length)
Notes
v_id
Integer
Auto-incrementing primary key used to associate a
product with a vendor
v_name
String(100)
Name of the vendor
Create Table dbo.t_vendors
(
v_id int Identity (1, 1) Not Null,
v_name varchar(100) Not Null
)
Go
Alter Table dbo.t_vendors Add Constraint
PK_t_vendors Primary Key Nonclustered
(
v_id
)
Go
Sample Data
v_id
v_name
1
Microsoft
2
Wrox
t_products
This table stores information about each product we will offer our customers.
Column Name
Data Type (length)
Notes
p_id
Integer
Auto-incrementing primary key
p_title
String(100)
Name of the product type
pt_id
Integer
Foreign key to t_product_types.pt_id
v_id
Integer
Foreign key to t_vendors.v_if
Create Table dbo.t_products
(
p_id int Identity (1, 1) Not Null,
p_title varchar(100) Not Null,
pt_id int Not Null,
v_id int Not Null
)
Go
Alter Table dbo.t_vendors Add Constraint
PK_t_vendors Primary Key Nonclustered
(
v_id
)
Go
Alter Table dbo.t_products Add Constraint
FK_t_products_t_product_types Foreign Key(pt_id)
References dbo.t_product_types(pt_id )
Go
Alter Table dbo.t_products Add Constraint
FK_t_products_t_vendors Foreign Key(v_id)
References dbo.t_vendors(v_id )
Go
Sample Data
p_id
p_title
pt_id
v_title
1
Visual Basic 6.0, Step by Step
1
1
2
Windows 2000
2
1
3
Visual Basic 6.0
2
1
4
Professional ASP XML
1
2
5
ADO 2.5 RDS
1
2
6
Professional Active Server Pages 3.0
1
2
Note: The relationship between these tables is fairly straightforward.
We re-use the same field names in each our tables to represent the
relationships. All of our relationships are one-to-many, meaning each
primary key can have any number of records in the related table. For
example, there can be many products of a given product type and there can
be many products from a vendor.
As I mentioned earlier, these tables are very simplified and not optimized.
They should ONLY be used for demonstration purposes.
Stored Procedure
We will use one stored procedure to retrieve the product information we
need to build our page. You could use several if that is your preference,
but I use just one.
sp_GetProductData
This stored procedure returns three recordsets one containing product-type
data, one with vendor data, and the last with product data.
CREATE PROCEDURE sp_GetProductData AS
SET NOCOUNT ON
SELECT DISTINCT t_product_types.pt_id, t_product_types.pt_title
FROM t_product_types, t_products
WHERE t_product_types.pt_id = t_products.pt_id
ORDER BY pt_title
SELECT DISTINCT t_vendors.v_id, t_vendors.v_name, t_product_types.pt_id
FROM t_vendors, t_product_types, t_products
WHERE t_product_types.pt_id = t_products.pt_id AND t_vendors.v_id =
t_products.v_id
ORDER BY t_product_types.pt_id, v_name
SELECT *
FROM t_products
ORDER BY pt_id, v_id, p_title
RETURN
Note that I am using SQL Server join syntax to return only product types and vendors for which we have product information. There's no sense in retu
rning more than you need.
ASP Script
In this example, I will use only one ASP script, products.asp. This ASP
script is meant to act only as a foundation upon which you can build. The
concepts presented can be extended and/or abstracted to meet your specific
needs.
Step 1
Begin ASP script with standard code (or at least my standard).
<% @LANGUAGE="VBSCRIPT" %>
<%
Option Explicit
Response.Buffer = True
On Error Resume Next
Step 2
Dimension all variables we'll be using in the scripts. I like to
dimension my variables in blocks, as you'll notice, for ease of
readability.
Dim oConn, oRS, oCmd
Dim aProducts, aTypes, aVendors
Dim iCount
Dim y
Step 3
Let's get our data using the sp_GetProductData stored procedure and
persist it in arrays using ADO's GetRows method. I use the GetRows
method frequently so I can open my database objects, get the data, and
close the objects as quickly as possible. You'll also notice that
since out stored procedure returns three recordsets, I am using ADO's
NextRecordset method.
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.RecordSet")
Set oCmd = Server.CreateObject("ADODB.Command")
oConn.Open "Provider=SQLOLEDB;SERVER=(local);DATABASE=[database
name];UID=[User ID];PWD=[Password]"
oCmd.ActiveConnection = oConn
oCmd.CommandType = 4
oCmd.CommandText = "sp_GetProductData"
oRS.Open oCmd,,2,3,4
aTypes = oRS.GetRows
Set oRS = oRS.NextRecordset
aVendors = oRS.GetRows
Set oRS = oRS.NextRecordset
aProducts = oRS.GetRows
oRS.Close
Set oRS = Nothing
Set oCmd = Nothing
oConn.Close
Set oConn = Nothing
Step 4
Now we'll start generating our HTML. You'll notice that I use
the ASP Response object's Write method to generate my HTML. I do this
because it's easier for the ASP engine to parse the file if HTML and
VBScript are not intermingled.
The Vendor constructor accepts three parameters: Vendor ID (iID) , Vendor
Name (sName), and Product Type ID (iTypeID.) The Product constructor
accepts four parameters: Product ID (iID), Product Title (sTitle), Product
Type ID (iTypeID), and Vendor ID (iVendorID.) We will later use arrays to
multiple instances of these objects.
Step 6
In order to alter the option values in our select objects, we will use <SPAN> elements. The following function, getVendors, will rewrite the cmbV
endors object based on the product type selected. This function will be
called by the cmbTypes select object's OnChange event.
The getVendors function (1) captures the option value of the selected
product type from the cmbTypes object, (2) loops through the aVendors
array, (3) creates a string representing a select object whose option
values depend on the selected product type, and (4) replaces the HTML
within the Vendors span tag.
The following function, getProducts, will rewrite the cmbProducts object
based on the vendor and product type selected. This function will be
called by the cmbVendors select object's OnChange event.
The getProducts function (1) captures the option values for the selected
product type and vendor from the cmbTypes and cmbVendor objects,
respectively, (2) loops through the aProducts array, (3) creates a string
representing a select object whose option values depend on the selected
product type and vendor, and (4) replaces the HTML within the Products span
tag.
Let's create two JavaScript arrays, one for our Vendors and one for
our Products:
Response.Write("var aVendors = new Array;" & chr(13))
Response.Write("var aProducts = new Array;" & chr(13))
Response.Write(chr(13))
These arrays will contain instances of Product and Vendor objects.
Step 9
OK, let's loop through our VBScript (server-side) aVendors array and
create a vendor object to be saved in the JavaScript (client-side) aVendors
array.
iCount = 1
For y = 0 To uBound(aVendors,2)
Response.Write("aVendors[" & iCount & "] = new vendor(" &
aVendors(0,y) & ",'" & aVendors(1,y) & "'," & aVendors(2,y) &
");" & chr(13))
iCount = iCount + 1
Next
Based on our sample data, the above statements will produce the following:
aVendors[1] = new vendor(1,'Microsoft',1);
aVendors[2] = new vendor(2,'Wrox',1);
aVendors[3] = new vendor(1,'Microsoft',2);
As you can see, three instances of the Vendor object are created and stored
in the aVendors array.
Step 10
OK, let's loop through our VBScript (server-side) aProducts array and
create a Vendor object to be saved in the JavaScript (client-side)
aProducts array.
iCount = 1
For y = 0 To uBound(aProducts,2)
Response.Write("aProducts[" & iCount & "] = new product(" &
aProducts(0,y) & ",'" & aProducts(1,y) & "'," & aProducts(2,y) &
"," & aProducts(3,y) & ");" & chr(13))
iCount = iCount + 1
Next
Based on our sample data, the above statements will produce the following:
aProducts[1] = new product(1,'Visual Basic 6.0, Step by Step',1,1);
aProducts[2] = new product(5,'ADO 2.5 RDS',1,2);
aProducts[3] = new product(6,'Professional Active Server Pages
3.0',1,2);
aProducts[4] = new product(4,'Professional ASP XML',1,2);
aProducts[5] = new product(3,'Visual Basic 6.0',2,1);
aProducts[6] = new product(2,'Windows 2000',2,1);
As you can see, three instances of the Product object are created and
stored in the aProducts array.
Step 11
Well, that's it for our JavaScript. Now we'll close the
<SCRIPT> and <HEAD> elements and start the form
containing our three select objects: cmbTypes, cmbVendors, and cmbProducts.
If you remember, in Step 2, we created a server-side array named aTypes
that contains our Product Type information. We will now use that array to
create our Product Type select object:
All I've done here is looped through the aTypes array and created
option elements for each of the products types we offer. Notice that the
cmbTypes select object has an OnChange event that calls the getVendors
function.
Step 13
The rest of the HTML is fairly standard. I create the Vendor and Product
select boxes and enclose them in <SPAN> tags:
Creating dependent HMTL select objects with ASP is fairly easy. It only
requires a basic knowledge of ASP, ADO, and DHTML.
Although, for this example, I decided to use ASP and JavaScript, the same
task can be accomplished using other technologies such as Extensible Markup
Language (XML) or Remote Data Services (RDS).
About the Author
Jason Butler is a technical manager for a "Big 5" consulting firm. He has
built numerous Microsoft-centric Web/E-commerce applications for Fortune 500 and
dot-com clients. To contact Jason with questions or comments, please email
him at Jason_m_butler@hotmail.com.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
