asked on

Dynamically Populating Drop Down From SQL Server Table using ASP

Using ASP I need to make a dynamic drop down box where the slected value of the firsst drop down (category) dynamically populates the values of the second drop down box (SubCategories) based on the relationship in the database tables. Does anyone have code or query that might accomplish this?

I've got two tables
TABLE #1 CATEGORIES
Columns
ID
Category

Opened
ID Category
100 Catagory1
200 Catagory2
300.. etc..

TABLE #2 SUBCATEGORIES
Columns
ID - where the next available CatoryID incremented by +1
BindTo - is actually the category ID I want to bind to
SubCategory

Opened
ID Bindto SubCategory
101 100 SubCat1
102 100 SubCat2
103..
201 200 SubCat3,1
202 200 SubCat4,2
203 200 SubCat5,3
204..

fritz_the_blank

Yes, I wrote an article on this here:

http://www.fairfieldconsulting.com/asp_multipleSelects.asp

FtB

ASKER CERTIFIED SOLUTION

fritz_the_blank

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

nschafer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nschafer

Fritz, beat me to it, his example is very good.

Neal.

kibbs

ASKER

I cant get any of these scripts to work.

Here ismy SQL query. Neal I liked that java script example you gave me, ritz looks good but it involves three drop downs, I only have two , and half-a-brain. When I start chopping code shi- happens.

This is the SQL I use

select category.categoryName, subcategory.subcategory
from subcategory, category
Where subcategory.bindto = category.id
ORDER BY category.categoryName, subcategory.bindto

Produces these results
CATEGORY SUBCATEGORY

Business Resources      Corporate Training
Business Resources      Business Networking
Business Resources      Conferences
Business Resources      Import Export
Blogs        Teacher Blogs
Blogs        Business Blogs
Blogs        Political Blogs

I'm geting an error using the java script
Microsoft VBScript compilation error '800a0409'

Unterminated string constant

Here is the code:

<html>
<head>
<title>Multiple Dynamic Drop Down Boxes - Javascript</title>
<script language=javascript >
var categorys = new Array(); // Array of all categorys in database
var Cities = new Array(); // Array of Arrays - One array for each category, each array holds the subcategory names for that category

function addcategory(category) {
// Determine current length of the categorys Array
v = categorys.length;
// increase the size of the categorys array by 1
categorys.length ++;
// Store the new category information into the array (note using the old length because arrays start at 0
categorys[v] = category;
// Determine the size of the Cities array
v = Cities.length;
// increase the size of the Cities array by 1
Cities.length ++;
// create a new array in the Cities array for the new category
Cities[category] = new Array();
}

function addsubcategory(category,subcategory) {
// Determine the current length of the subcategory[category] array.
// that is the sub array of Cities for the specified category
v = Cities[category].length;
// Increase the size of the subcategory[category] array by 1.
Cities[category].length ++;
// Add the new subcategory to the array
Cities[category][v] = subcategory;
}

function loadcategoryList() {
// Clear the category options
var ctrlcategory = document.frmAddress.category;
ctrlcategory.options.length = 0;
// Load the category options
for (i=0;i<categorys.length;i++) {
ctrlcategory.options[i] = new Option(categorys[i],categorys[i]);
}
}

function loadsubcategoryList() {
// Determine the Selected category
var ctrlcategory = document.frmAddress.category;
var selcategory = ctrlcategory.options[ctrlcategory.selectedIndex].value;
// Clear the subcategory Options
var ctrlsubcategory = document.frmAddress.subcategory;
ctrlsubcategory.options.length = 0;
// Load the subcategory Options
for (i=0;i<Cities[selcategory].length;i++) {
ctrlsubcategory.options[i] = new Option(Cities[selcategory][i]);
}
}
</script>

</head>
<body onload="loadcategoryList();loadsubcategoryList();">
<h2>Dynamically Linked Drop-Down Boxes - JavaScript</h2>
<%
' Open Database and setup a recordset with categorys Listed.
' Note the Distinct option in the SQL categoryment. This will limit each category to showing only once in the recordset.
set conn = server.createobject("ADODB.Connection")
Conn.open("driver={SQL Server};server=USA\SERVER;uid=UserID;pwd=password;database=parked")
strSQL = "select category.categoryName, subcategory.subcategory from subcategory, category Where subcategory.bindto = category.id
ORDER BY category.categoryName, subcategory.bindto"
set rs = conn.execute(strSQL)
' If there are no records found, display a message. Otherwise display our form.
if rs.eof then
response.write("No Addresses Found")
rs.close
set rs=nothing
response.end
end if
' Here is where we load up the javascript arrays:
' If the category not the same as the previous record then add it to the
' categorys array and create a new array to hold the cities for the new category
' then add the subcategory to the new category array
' if the category is the same as the previous record, then check to see if the
' subcategory is the same as the previous record. If not then add it to the array
' for the correct category.
strcategory = ""
strsubcategory = ""
do until rs.eof
if not rs("category") = strcategory then
response.write("<script>addcategory('" & rs("category") & "')</script>")
response.write("<script>addsubcategory('" & rs("category") & "','" & rs("subcategory") & "')</script>")
strcategory = rs("category")
strsubcategory = rs("subcategory")
else
if not rs("subcategory") = strsubcategory then
response.write("<script>addsubcategory('" & rs("category") & "','" & rs("subcategory") & "')</script>")
strsubcategory = rs("subcategory")
end if
end if
rs.MoveNext
loop
rs.close
set rs=nothing
' OK so now our arrays are all created
' Lets actually put the information on the screen.
%>
<form name="frmAddress" method="POST" action="">
<table>
<tr>
<td>Name:</td>
<td><input type=text name="fullName" /></td>
</tr>
<tr>
<td>Address:</td>
<td><input type=text name="address" /></td>
</tr>
<tr>
<td>category</td>
<td>

<select size="1" name="category" onChange="loadsubcategoryList();">
</select>
</td>
</tr>
<tr>
<td>subcategory</td>
<td>
<select size="1" name="subcategory">
</select>
</td>
</tr>
<tr>
<td>Phone</td>
<td><input type=text name="Phone" /></td>
</tr>
</table>
<p>
<input type="submit" value="Submit" name="B1" />
<input type="reset" value="Reset" name="B2" />
</p>
</form>
</body>
</html>

nschafer

On what like are you getting the error?

Can you post the exact error message please

Neal.

nschafer

Check out your SQL statement I think you have an extra carriage return in there.

Neal.

kibbs

ASKER

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/dynamic_droplist.asp, line 65

nschafer

Did you check your SQL statement? and which line is line 65?

ksbhat

Make sure the following is on a ssingle line

strSQL = "select category.categoryName, subcategory.subcategory from subcategory, category Where subcategory.bindto = category.id ORDER BY category.categoryName, subcategory.bindto"

ksbhat

else

strSQL = "select category.categoryName, subcategory.subcategory from subcategory, category Where subcategory.bindto = category.id " & _
"ORDER BY category.categoryName, subcategory.bindto"

kibbs

ASKER

Here's what I've done.

I made one table called categories having three fields. ID, Category, SubCategory
Using Fritzs Code.. I get the window, a single drop down
BUT it is not populated.

you can see result by viewing http://ESLZ.net/3.asp

Is it the connection string?
I am developing this on the Win2003 server using FP extensions, with SQL SVR 2005
set objConnection = server.createobject("ADODB.Connection")
strConnectString = "DSN=DSN_name;DRIVER={SQL Native Client};UID=xxxUSERid;PWD=xxxPasword"

I am unfamiliar with how to use fritzs code below to make the connection:
'set connection strings for entire application
strDataPath = server.MapPath("NorthWind.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
+ " Data Source= " & strDataPath & ";"_
+ " Mode=Share Deny None;User Id=admin;PASSWORD=;"

if not IsObject("ojbConnection") then
set objConnection=Server.CreateObject("ADODB.Connection")
objConnection.ConnectionTimeout = 15
objConnection.CommandTimeout = 10
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
objConnection.Open strConnectString
end if
end if

Thanks again for your patient help and response..

kibbs

ASKER

Here's the code as it is being used now.. Thanks for being patient I am still learning about these things.

<%@ Language = VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 6.0">
<TITLE></TITLE>

<%
dim strConnectString, objConnection
dim strCategory, strSubcategory, objRS, strSelected, strSQL

strCategory = Request.Form("category")
strSubcategory = Request.Form("Subcategory")

'set connection strings for entire application
set objConnection = server.createobject("ADODB.Connection")
strConnectString = "DSN=DSN_name;DRIVER={SQL Native Client};UID=xxxUSERid;PWD=xxxPasword"

sub makecategory()
if not isObject("objRS") then
set objRS=Server.CreateObject("ADODB.RecordSet")
end if
if objRS.state <> 0 then
objRS.close
end if
strSQL = "SELECT DISTINCT Category FROM categories ORDER BY category"
objRS.Open strSQL,objConnection,2,2
Response.Write("<option></option>" & VBCRLF )
do while not objRS.EOF
if objRS("Category") = strcategory then
strSelected = " Selected "
else
strSelected = ""
end if
Response.Write("<option" & strSelected & ">" &_
objRS("category") & "</option>" & VBCRLF )
objRS.MoveNext
loop
objRS.Close
set objRS=Nothing
end sub

sub makeSubcategory()
if strcategory <> "" then
if not isObject("objRS") then
set objRS=Server.CreateObject("ADODB.RecordSet")
end if
if objRS.state <> 0 then
objRS.close
end if
strSQL ="SELECT DISTINCT SubCategory FROM Categories WHERE Category = '" &_
strcategory & "' ORDER BY SubCategory"
objRS.Open strSQL,objConnection,3,3
if objRS.eof then
Response.Write("<option>No SubCategories Found</option>")
else
Response.Write("<option>Select Subcategory Now</option>" & VBCRLF )
do while not objRS.EOF
if objRS("Subcategory") = strSubcategory then
strSelected = " Selected "
else
strSelected = ""
end if
Response.Write("<option" & strSelected & ">" &_
objRS("Subcategory") & "</option>" & VBCRLF )
objRS.MoveNext
loop
end if
objRS.Close
set objRS=Nothing
else
Response.Write("<option>Select a category First</option>")
end if
end sub

sub makeCity()
if strSubcategory <> "Select a category First" AND _
strSubcategory <> "Select Subcategory Now" AND strSubcategory <>"" then
if not isObject("objRS") then
set objRS=Server.CreateObject("ADODB.RecordSet")
end if
if objRS.state <> 0 then
objRS.close
end if
strSQL = "SELECT DISTINCT City FROM Customers WHERE Subcategory = '" &_
strSubcategory & "' ORDER BY City"
objRS.Open strSQL,objConnection,3,3
if objRS.eof then
Response.Write("<option>No Cities Found</option>")
else
Response.Write("<option>Select City Now</option>" & VBCRLF )
do while not objRS.EOF
if objRS("City") = strCity then
strSelected = " Selected "
else
strSelected = ""
end if
Response.Write("<option" & strSelected & ">" &_
objRS("City") & "</option>" & VBCRLF )
objRS.MoveNext
loop
end if
objRS.Close
set objRS=Nothing
else
Response.Write("<option>Select a Subcategory First</option>")
end if
end sub
%>

<SCRIPT LANGUAGE=javascript>

</SCRIPT>

</HEAD>
<BODY onLoad="window.moveTo(0,0);window.resizeTo(800,400);">
<div style="align: center; text-align: center;">
<a href=# onClick="window.close(); return false;">Close Window</a>
</div>
<FORM action="" method=POST id=form1 name=form1 onSubmit="return submitForm()">
<SELECT name="category" onChange="submitcategory()">
<%call makecategory%>
</SELECT><br>
<SELECT name="Subcategory" onChange="submitSubcategory()">
<%call makeSubcategory%>
</SELECT><br>

<p><INPUT type="submit" value="Submit" id=submit1 name=submit1></p>
</FORM>
</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>

</HTML>

nschafer

Fritz's code is for an Access database, not SQL. If you are using SQL you may continue to use your
connection information.

kibbs

ASKER

Its returning nothing.. The drop down list is blank.. I've tested on another site as well.. using another database..

fritz_the_blank

I am wondering where you actually open the connection?

<%@ Language = VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 6.0">
<TITLE></TITLE>

<%
dim strConnectString, objConnection
dim strCategory, strSubcategory, objRS, strSelected, strSQL

strCategory = Request.Form("category")
strSubcategory = Request.Form("Subcategory")

'set connection strings for entire application
set objConnection = server.createobject("ADODB.Connection")
strConnectString = "DSN=DSN_name;DRIVER={SQL Native Client};UID=xxxUSERid;PWD=xxxPasword"

if not IsObject("objConnection") then
set objConnection=Server.CreateObject("ADODB.Connection")
objConnection.ConnectionTimeout = 15
objConnection.CommandTimeout = 10
objConnection.Mode = 3 'adModeReadWrite
if objConnection.state = 0 then
objConnection.Open strConnectString
end if
end if

Now you have a connection to work with for your recordsets.

FtB

kibbs

ASKER

Hi Fritz,

That seemed to do the trick, thanks for your help!