Developer

Using dynamic select/option data

How do you set up a form page that changes secondary selections when you change the "master" selection? We'll show you how to use DHTML and dynamically-generated JavaScript to make it work.


Editor’s note: This article originally appeared in TechRepublic’s Web Development Zone TechMail. Subscribe, and you’ll receive information on Web-development-related projects and trends.

Recently, a client requested that I create a form input page that enforced database relationships. As a result, if the user changes the selection of the master selection box, the relation-dependent selection presents a different listing of related options.

However, I ran into a problem when I was working on the project management application and needed to enforce the relationships between two tables. The master table in this case is ProjectType. The secondary table is ProjectCategories; it has a foreign key that ties it to the master table.

Here are the definitions for the tables used:
Table ProjectType;
Fields: 
ProjectTypeId int PK, 
ProjectTypeDesc varchar(50)
 
Table ProjectCategories; 
Fields: 
ProjectCatId int PK, 
ProjectTypeId int FK, 
ProjectCatDesc varchar(50)

At first, I thought this would require another trip to the server to refresh the data in the secondary list when the primary list changed. Then it occurred to me to use an array to upload the data when the Web page loads.

This program uses DHTML with dynamically generated JavaScript arrays to make the secondary list change when the user changes the value of the primary selection list.

The following application is written as an Active Server Page. I have kept the code easy to translate to non-Windows platforms by keeping the functional processing in one place on the page.
 
<%@ Language=VBScript %>
<%
DBconStr = "The DSN or DataBase Connection string"
 
set con = server.createobject("adodb.connection")
con.ConnectionString = DbconStr
con.open 
set rs1 = server.createobject("adodb.recordset")
set rs = server.createobject("adodb.recordset")
 
OptionArray1 = "<OPTION VALUE=""-1"">" & _
"Select Project Type</option>" & vbcrlf
SelectText = ""
 
rs1.Open "SELECT ProjectTypeDesc, " & _
"ProjectTypeId FROM ProjectType",con
 
'Start of primary list generation list loop.
while not rs1.EOF
'Add items to the primary list. 
OptionArray1 = OptionArray1 & "<option id=""" & _
rs1("ProjectTypeId") & """ value=""" & _
rs1("ProjectTypeId") & """>" & _
rs1("ProjectTypeDesc") & _
"</option>" & vbcrlf
 
'Add items to the dynamic array.
sqlArray ="SELECT ProjectCatDesc, ProjectCatId," & _
" ProjectTypeID FROM ProjectCategories where " & _
"ProjectTypeID=" & rs1("ProjectTypeId")
rs.Open sqlArray, con
if not rs.EOF then
   SelectText = SelectText & _
   "new Array(new Array(""   "", 0),"
   while not rs.EOF
      SelectText = SelectText & _
      "new Array(""" & rs("ProjectCatDesc") & _
      """, " & rs("ProjectCatId") & ")," & vbcrlf
      rs.MoveNext
   wend
   SelectText=left(SelectText,(len(SelectText) - 3))
   SelectText=SelectText & "),"
else
   SelectText = SelectText & _
   "new Array(new Array(""There are no " & _
   "categories available."", 0)),"
end if
 
rs.Close
rs1.MoveNext
wend
'end of primary loop
rs1.Close
SelectText=left(SelectText,(len(SelectText) - 2))
 
set rs=nothing
set rs1=nothing
con.Close
set con = nothing
%>
<html>
<head>
<SCRIPT LANGUAGE="JavaScript">
function fillSelectFromArray(selectCtrl,
itemArray, goodPrompt, badPrompt, defaultItem) {
var i, j;
var prompt;
// empty existing items
for (i = selectCtrl.options.length; i >= 0; i—) {
selectCtrl.options[i] = null;
}
prompt = (itemArray != null) ? goodPrompt : badPrompt;
if (prompt == null) {
j = 0;
}
else {
selectCtrl.options[0] = new Option(prompt);
j = 1;
}
if (itemArray != null) {
// add new items
for (i = 0; i < itemArray.length; i++) {
selectCtrl.options[j] = new Option(itemArray[i][0]);
if (itemArray[i][1] != null) {
selectCtrl.options[j].value = itemArray[i][1];
}
j++;
}
// select first item (prompt) for sub list
selectCtrl.options[0].selected = true;
   }
}
appropCat = new Array(
<%=SelectText%>)
);
 
</script>
<title>Dynamic Item Select</title>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000" >
<form method=post name="frmProjectMain"  >
<table width="400" border="1">
<tr>
<td valign="top" align="center" colspan="4">
<h1>Dynamic Item Select</h1>
</td>
</tr>
<tr>
<td width="100%" colspan="4">Project Type
&nbsp;
<select name="ProjType"
onChange="fillSelectFromArray(this.form.AppropCat,
((this.selectedIndex == -1) ? null :
appropCat[this.selectedIndex-1]));">
<%=OptionArray1%>
</select>
<td >
</tr>
<tr>
<td colspan="4">Catagory&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<select name="AppropCat" >
<OPTION>                       </OPTION>
<OPTION>                       </OPTION>
</select>
</td>
</tr>
</table>
</BODY>
</HTML>
 

Have you encountered similar requests from clients?
If you’ve ever been asked to put together something like this for your clients, tell us how you made it work. Send us an e-mail or post your advice below.

 

Editor's Picks

Free Newsletters, In your Inbox