Creating Dependent Select Objects With ASP--tutorial.jcwcn.com

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.



Response.Write("<HTML>" & chr(13))
Response.Write("<HEAD>" & chr(13))
Response.Write("<TITLE>Creating Dependent HTML Select Objects
with ASP</TITLE>" & chr(13))
Response.Write("<STYLE>" & chr(13))
Response.Write("     BODY {font-family: Arial; font-size: 12pt; backcolor:
#FFFFFF; text: #00000}" & chr(13))
Response.Write("     SELECT {font-family: Arial; font-size: 12pt}" & chr(13))
Response.Write("</STYLE>" & chr(13))
Response.Write("<SCRIPT LANGUAGE=" & chr(34) & "JAVASCRIPT" & chr(34)
& ">" & chr(13))

Step 5

OK, here's the important stuff, the JavaScript code. We'll start by creating two functions that will act as Vendor and Product object constructors.



Response.Write("function vendor(iID, sName, iTypeID) {" & chr(13))
Response.Write("	this.ID = iID;" & chr(13))
Response.Write("	this.Name = sName;" & chr(13))
Response.Write("	this.TypeID = iTypeID;" & chr(13))
Response.Write("}" & chr(13))
Response.Write("function product(iID, sTitle, iTypeID, iVendorID) {" & chr(13))
Response.Write("	this.ID = iID;" & chr(13))
Response.Write("	this.Title = sTitle;" & chr(13))
Response.Write("	this.TypeID = iTypeID;" & chr(13))
Response.Write("	this.VendorID = iVendorID;" & chr(13))
Response.Write("}" & chr(13))
Response.Write(chr(13))

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.



Response.Write("function getVendors() {" & chr(13))
Response.Write("     var sSelect = '<SELECT NAME=cmbVendors
OnChange=" & chr(34) & "getProducts();" & chr(34) & "><OPTION
VALUE=0 SELECTED></OPTION>';" & chr(13))
Response.Write("     var iTypeID = document.frmProducts.cmbTypes.value;" &
chr(13))
Response.Write("     for (var x=1; x<aVendors.length; x++) {" & chr(13))
Response.Write("          if (aVendors[x].TypeID == iTypeID) {" & chr(13))
Response.Write("               sSelect = sSelect + '<OPTION
VALUE=' + aVendors[x].ID + '>' + aVendors[x].Name +
'</OPTION>'" & chr(13))
Response.Write("          }" & chr(13))
Response.Write("     }" & chr(13))
Response.Write("     sSelect = sSelect + '</SELECT>';"
& chr(13))
Response.Write("     document.all['Vendors'].innerHTML = " &
chr(34) & chr(34) & ";" & chr(13))
Response.Write("     document.all['Vendors'].innerHTML =
sSelect;" & chr(13))
Response.Write("}" & chr(13))
Response.Write(chr(13))

Step 7

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.



Response.Write("function getProducts() {" & chr(13))
Response.Write("     var sSelect = '<SELECT
NAME=cmbProducts><OPTION VALUE=0
SELECTED></OPTION>';" & chr(13))
Response.Write("     var iTypeID = document.frmProducts.cmbTypes.value;" &
chr(13))
Response.Write("     var iVendorID =
document.frmProducts.cmbVendors.value;" & chr(13))
Response.Write("     for (var x=1; x<aProducts.length; x++) {" & chr(13))
Response.Write("          if (aProducts[x].TypeID == iTypeID &&
aProducts[x].VendorID == iVendorID) {" & chr(13))
Response.Write("               sSelect = sSelect + '<OPTION
VALUE=' + aProducts[x].ID + '>' + aProducts[x].Title +
'</OPTION>'" & chr(13))
Response.Write("          }" & chr(13))
Response.Write("     }" & chr(13))
Response.Write("     sSelect = sSelect + '</SELECT>';"
& chr(13))
Response.Write("     document.all['Products'].innerHTML = " &
chr(34) & chr(34) & ";" & chr(13))
Response.Write("     document.all['Products'].innerHTML =
sSelect;" & chr(13))
Response.Write("}" & chr(13))
Response.Write(chr(13))

Step 8

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.



Response.Write("</SCRIPT>" & chr(13))
Response.Write("</HEAD>" & chr(13))
Response.Write("<BODY>" & chr(13))
Response.Write("<FORM NAME=frmProducts ACTION=products.asp
METHOD=POST>" & chr(13))

Step 12

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:



Response.Write("Product Type:<BR>" & chr(13))
Response.Write("<SELECT NAME=cmbTypes OnChange=" & chr(34) &
"getVendors();" & chr(34) & ">" & chr(13))
Response.Write("  <OPTION VALUE=0 SELECTED></OPTION>" &
chr(13))

For y = 0 To uBound(aTypes,2)
	Response.Write("  <OPTION VALUE=" & aTypes(0,y) & ">" &
aTypes(1,y) & "</OPTION>" & chr(13))
Next

Response.Write("      </SELECT>" & chr(13))
Response.Write("<BR>" & chr(13))
Response.Write("<BR>" & chr(13))

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:



Response.Write("Vendor:<BR>" & chr(13))
Response.Write("<SPAN ID=Vendors>" & chr(13))
Response.Write("<SELECT NAME=cmbVendors OnChange=" & chr(34) &
"getProducts();" & chr(34) & ">" & chr(13))
Response.Write("  <OPTION VALUE=0 SELECTED></OPTION>" &
chr(13))
Response.Write("</SELECT>" & chr(13))
Response.Write("</SPAN>" & chr(13))
Response.Write("<BR>" & chr(13))
Response.Write("<BR>" & chr(13))

Response.Write("Products:<BR>" & chr(13))
Response.Write("<SPAN ID=Products>" & chr(13))
Response.Write("<SELECT NAME=cmbProducts>" & chr(13))
Response.Write("  <OPTION VALUE=0 SELECTED></OPTION>" &
chr(13))
Response.Write("</SELECT>" & chr(13))
Response.Write("</SPAN>" & chr(13))
Response.Write("</FORM>" & chr(13))
Response.Write("</BODY>" & chr(13))
Response.Write("</HTML>" & chr(13))
%>

To view the complete code please click here.

Conclusion

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).

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

Creating Dependent Select Objects With ASP

relation tutorial

Category

New

Hot