Dynamic droplists

C

C White

I'm trying to write an asp script that will create a series of drop
lists based on a table like:

canada ontario toronto street name link
canada ontario toronto road name link
canada ontario hamilton road name link
canada alberta calgary street name link
canada alberta edmonton street name link
usa new york buffalo street name link
usa new york rochester road name link
usa california san jose street name link
usa california sacramento road name link


the first drop list will only display the country

canada
usa

once a country is seleceted a second list will appear and display the
provice/state, so if the user picked canada it would only display

ontario
alberta

and the third list which appears once a selection is made in the
second list will display the city based on which state/province that
is selected and on to the 4th list, which once the 4th list item is
selected it will open a link

i can get the code to display the first drop list, but once the
country is selected it won't display the next drop list, it give me a
new url which indicates the information has been passed but the 2nd
list doees not appear, the url will be:

test.asp?Country=canada

I am using an access 2000 database to store the information and my
code is pasted below,

<html>
<head>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="expires content="-1">
<script language="JavaScript">
<!--
function display(what){
document.getElementById(what).selected = true;
}
//-->
</script>
</head>

<body>

<%

Dim strSQL
Dim cnnLinkDB
Dim rstCountry, rstProv_State, rstCity, rstStreet
Dim Country, Prov_State, City, Street

' The form links back to this same file passing back the id
%>
<form action="test.asp" method="get">
<%

' Create ADO data connection object
Set cnnLinkDB = Server.CreateObject("ADODB.Connection")

' Open data connection
cnnLinkDB.Open "DBQ=" & Server.MapPath("master_table.mdb") & ";" _
& "Driver={Microsoft Access Driver (*.mdb)};", "admin", ""

' Build our query for select box 1
strSQL = "SELECT DISTINCT Country FROM Data;"

' Create and open recordset object using existing connection
Set rstCountry = Server.CreateObject("ADODB.Recordset")
rstCountry.Open strSQL, cnnLinkDB

' Build our drop down box of countries
If Not rstCountry.EOF Then
rstCountry.MoveFirst
%>
Country: <br>
<select name="Country" onchange=submit()>
<option></option>
<% ' Loop through names
Do While Not rstCountry.EOF
Response.Write "<option value="""
Response.Write rstCountry.Fields("Country")
Response.Write """"
Response.Write ">"
Response.Write rstCountry.Fields("Country")
Response.Write "</option>" & vbCrLf

' Move to next record
rstCountry.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstCountry.Close
Set rstCountry = Nothing

' If a request for a specific id comes in, then build second select
box

strSQL = "SELECT DISTINCT Prov_State FROM Data= WHERE Country = '" &
Country & "'"

Set rstProv_State = Server.CreateObject("ADODB.Recordset")
rstProv_State.Open strSQL, cnnLinkDB

' Build our drop down box of Prov_States
If Not rstProv_State.EOF Then
rstProv_State.MoveFirst
%>
<p>Prov/State: <br>
<select name="Prov_State" onchange=submit()>
<option></option>
<% ' Loop through names
Do While Not rstProv_State.EOF
Response.Write "<option value="""
Response.Write rstProv_State.Fields("Prov_State")
Response.Write """"
Response.Write ">"
Response.Write rstProv_State.Fields("Prov_State")
Response.Write "</option>" & vbCrLf

' Move to next record
rstProv_State.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstProv_State.Close
Set rstProv_State = Nothing


' If a request for a specific id comes in, then build second select
box

strSQL = "SELECT DISTINCT City FROM Data WHERE Prov_State = '" &
Prov_State & "'"

Set rstCity = Server.CreateObject("ADODB.Recordset")
rstCity.Open strSQL, cnnLinkDB

' Build our drop down box of areas
If Not rstCity.EOF Then
rstCity.MoveFirst
%>
<p>City: <br>
<select name="City" onchange=submit()>
<option></option>
<% ' Loop through names
Do While Not rstCity.EOF
Response.Write "<option value="""
Response.Write rstCity.Fields("City")
Response.Write """"
Response.Write ">"
Response.Write rstCity.Fields("City")
Response.Write "</option>" & vbCrLf

' Move to next record
rstCity.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstCity.Close
Set rstCity = Nothing



' If a request for a specific id comes in, then build second select
box

strSQL = "SELECT DISTINCT Street FROM Data WHERE City = '" & City &
"'"

Set rstStreet = Server.CreateObject("ADODB.Recordset")
rstStreet.Open strSQL, cnnLinkDB

' Build our drop down box of areas
If Not rstStreet.EOF Then
rstStreet.MoveFirst
%>
<p>Street: <br>
<select name="Street"
onchange="window.open(this.options[this.selectedIndex].value,'_blank')">
<option></option>
<% ' Loop through names
Do While Not rstDetail.EOF
Response.Write "<option value="""
Response.Write rstStreet.Fields("Link")
Response.Write """"
Response.Write "selected=""true"""
Response.Write ">"
Response.Write rstStreet.Fields("Street")
Response.Write "</option>" & vbCrLf

' Move to next record
rstDetail.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstStreet.Close
Set rstStreet = Nothing




' Close ADO objects we're finished with and free DB variables
cnnLinkDB.Close
Set cnnLinkDB = Nothing
%>

</form>
<br>
<FORM ACTION="test.asp" METHOD=GET>
<INPUT TYPE=submit VALUE="Reset">
</FORM>

</body>
</html>

I am trying to use the lesson at:
http://www.asp101.com/samples/db_pulldown_linked.asp

but this example uses multiple tables and numbers, whereas I am trying
to do it with one table and text values, and I have the feeling that
the text value is part of my problem, and the fact that i don't know
what i'm doing is my other problem :)

however, if anyone can point me in the right direction that would be
greatly appreciated

thanks
 
M

McKirahan

C White said:
I'm trying to write an asp script that will create a series of drop
lists based on a table like:

canada ontario toronto street name link
canada ontario toronto road name link
canada ontario hamilton road name link
canada alberta calgary street name link
canada alberta edmonton street name link
usa new york buffalo street name link
usa new york rochester road name link
usa california san jose street name link
usa california sacramento road name link


the first drop list will only display the country

canada
usa

once a country is seleceted a second list will appear and display the
provice/state, so if the user picked canada it would only display

ontario
alberta

and the third list which appears once a selection is made in the
second list will display the city based on which state/province that
is selected and on to the 4th list, which once the 4th list item is
selected it will open a link

i can get the code to display the first drop list, but once the
country is selected it won't display the next drop list, it give me a
new url which indicates the information has been passed but the 2nd
list doees not appear, the url will be:

test.asp?Country=canada

I am using an access 2000 database to store the information and my
code is pasted below,

<html>
<head>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="expires content="-1">
<script language="JavaScript">
<!--
function display(what){
document.getElementById(what).selected = true;
}
//-->
</script>
</head>

<body>

<%

Dim strSQL
Dim cnnLinkDB
Dim rstCountry, rstProv_State, rstCity, rstStreet
Dim Country, Prov_State, City, Street

' The form links back to this same file passing back the id
%>
<form action="test.asp" method="get">
<%

' Create ADO data connection object
Set cnnLinkDB = Server.CreateObject("ADODB.Connection")

' Open data connection
cnnLinkDB.Open "DBQ=" & Server.MapPath("master_table.mdb") & ";" _
& "Driver={Microsoft Access Driver (*.mdb)};", "admin", ""

' Build our query for select box 1
strSQL = "SELECT DISTINCT Country FROM Data;"

' Create and open recordset object using existing connection
Set rstCountry = Server.CreateObject("ADODB.Recordset")
rstCountry.Open strSQL, cnnLinkDB

' Build our drop down box of countries
If Not rstCountry.EOF Then
rstCountry.MoveFirst
%>
Country: <br>
<select name="Country" onchange=submit()>
<option></option>
<% ' Loop through names
Do While Not rstCountry.EOF
Response.Write "<option value="""
Response.Write rstCountry.Fields("Country")
Response.Write """"
Response.Write ">"
Response.Write rstCountry.Fields("Country")
Response.Write "</option>" & vbCrLf

' Move to next record
rstCountry.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstCountry.Close
Set rstCountry = Nothing

' If a request for a specific id comes in, then build second select
box

strSQL = "SELECT DISTINCT Prov_State FROM Data= WHERE Country = '" &
Country & "'"

Set rstProv_State = Server.CreateObject("ADODB.Recordset")
rstProv_State.Open strSQL, cnnLinkDB

' Build our drop down box of Prov_States
If Not rstProv_State.EOF Then
rstProv_State.MoveFirst
%>
<p>Prov/State: <br>
<select name="Prov_State" onchange=submit()>
<option></option>
<% ' Loop through names
Do While Not rstProv_State.EOF
Response.Write "<option value="""
Response.Write rstProv_State.Fields("Prov_State")
Response.Write """"
Response.Write ">"
Response.Write rstProv_State.Fields("Prov_State")
Response.Write "</option>" & vbCrLf

' Move to next record
rstProv_State.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstProv_State.Close
Set rstProv_State = Nothing


' If a request for a specific id comes in, then build second select
box

strSQL = "SELECT DISTINCT City FROM Data WHERE Prov_State = '" &
Prov_State & "'"

Set rstCity = Server.CreateObject("ADODB.Recordset")
rstCity.Open strSQL, cnnLinkDB

' Build our drop down box of areas
If Not rstCity.EOF Then
rstCity.MoveFirst
%>
<p>City: <br>
<select name="City" onchange=submit()>
<option></option>
<% ' Loop through names
Do While Not rstCity.EOF
Response.Write "<option value="""
Response.Write rstCity.Fields("City")
Response.Write """"
Response.Write ">"
Response.Write rstCity.Fields("City")
Response.Write "</option>" & vbCrLf

' Move to next record
rstCity.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstCity.Close
Set rstCity = Nothing



' If a request for a specific id comes in, then build second select
box

strSQL = "SELECT DISTINCT Street FROM Data WHERE City = '" & City &
"'"

Set rstStreet = Server.CreateObject("ADODB.Recordset")
rstStreet.Open strSQL, cnnLinkDB

' Build our drop down box of areas
If Not rstStreet.EOF Then
rstStreet.MoveFirst
%>
<p>Street: <br>
<select name="Street"
onchange="window.open(this.options[this.selectedIndex].value,'_blank')">
<option></option>
<% ' Loop through names
Do While Not rstDetail.EOF
Response.Write "<option value="""
Response.Write rstStreet.Fields("Link")
Response.Write """"
Response.Write "selected=""true"""
Response.Write ">"
Response.Write rstStreet.Fields("Street")
Response.Write "</option>" & vbCrLf

' Move to next record
rstDetail.MoveNext
Loop
%>
</select>
<%
End If

' Close ADO objects we're finished with and free DB variables
rstStreet.Close
Set rstStreet = Nothing




' Close ADO objects we're finished with and free DB variables
cnnLinkDB.Close
Set cnnLinkDB = Nothing
%>

</form>
<br>
<FORM ACTION="test.asp" METHOD=GET>
<INPUT TYPE=submit VALUE="Reset">
</FORM>

</body>
</html>

I am trying to use the lesson at:
http://www.asp101.com/samples/db_pulldown_linked.asp

but this example uses multiple tables and numbers, whereas I am trying
to do it with one table and text values, and I have the feeling that
the text value is part of my problem, and the fact that i don't know
what i'm doing is my other problem :)

however, if anyone can point me in the right direction that would be
greatly appreciated

thanks
Click to expand...

This link may be of some interest:
http://www.quirksmode.org/js/options.html
 
M

McKirahan

[snip]

This isn't what you want but it may give you some ideas.

It has a hardcoded list (as above).

I've change "link" references to "link#.htm".

Test as-is; watch for word-wrap.

<html>
<head>
<title>droplist.htm</title>
<script type="text/javascript">
var i = 0;
var list = new Array();
list[i++] = "canada^ontario^toronto^street name^link1.htm";
list[i++] = "canada^ontario^toronto^road name^link2.htm";
list[i++] = "canada^ontario^hamilton^road name^link3.htm";
list[i++] = "canada^alberta^calgary^street name^link4.htm";
list[i++] = "canada^alberta^edmonton^street name^link5.htm";
list[i++] = "usa^new york^buffalo^street name^link6.htm";
list[i++] = "usa^new york^rochester^road name^link7.htm";
list[i++] = "usa^california^san jose^street name^link8.htm";
list[i++] = "usa^california^sacramento^road name^link9.htm";
var text = new Array("","","","");

function opts(that,what) {
var k = 1;
var item;
var temp = "";
if (that != "") {
if (what == 1) {
text[0] = that.options[that.selectedIndex].text;
text[1] = "";
text[2] = "";
text[3] = "";
document.form1.StateProv.options.length = 0;
document.form1.City.options.length = 0;
document.form1.Street.options.length = 0;
} else if (what == 2) {
text[1] = that.options[that.selectedIndex].text;
text[2] = "";
text[3] = "";
document.form1.City.options.length = 0;
document.form1.Street.options.length = 0;
} else if (what == 3) {
text[2] = that.options[that.selectedIndex].text;
text[3] = "";
document.form1.Street.options.length = 0;
} else if (what == 4) {
text[3] = that.options[that.selectedIndex].text;
var indx = that.options[that.selectedIndex].value;
item = list[indx].split("^");
alert(text[0] + " : " + text[1] + " : " + text[2] + " : " + text[3] + " : "
+ item[4]);
// comment out alert (above).
// enable link (below); if it's a real page.
// location.href = item[4];
}
}
for (var j=0; j<list.length; j++) {
item = list[j].split("^");
if (what == 0) {
if (temp != item[what]) {
temp = item[what];
document.form1.Country.options[k++] = new Option(temp, j);
}
} else if (what == 1) {
if (text[0] == item[0] && temp != item[what]) {
temp = item[what];
document.form1.StateProv.options[k++] = new Option(temp, j);
}
} else if (what == 2) {
if (text[0] == item[0] && text[1] == item[1] && temp !=
item[what]) {
temp = item[what];
document.form1.City.options[k++] = new Option(temp, j);
}
} else if (what == 3) {
if (text[0] == item[0] && text[1] == item[1] && text[2] ==
item[2] && temp != item[what]) {
temp = item[what];
document.form1.Street.options[k++] = new Option(temp, j);
}
}
}
}
</script>
</head>
<body onload="opts('',0)">
<form name="form1">
&nbsp; <b>Country:</b> &nbsp;
<select name="Country" onchange="opts(this,1)" style="width:100px">
<option value=""></option>
</select>
&nbsp; <b>State/ProvinceCity:</b> &nbsp;
<select name="StateProv" onchange="opts(this,2)" style="width:100px">
<option value=""></option>
</select>
&nbsp; <b>City:</b> &nbsp;
<select name="City" onchange="opts(this,3)" style="width:100px">
<option value=""></option>
</select>
&nbsp; <b>Street:</b> &nbsp;
<select name="Street" onchange="opts(this,4)" style="width:100px">
<option value=""></option>
</select>
</body>
</html>

This is strictly client-side though perhaps the array could be built by ASP.

ASP could be used to build an "include"; for example,

<script type="text/javascript" src="droplist.js"></script>

Where "droplist.js" contains; (removed from above):

list[i++] = "canada^ontario^toronto^street name^link1.htm";
list[i++] = "canada^ontario^toronto^road name^link2.htm";
list[i++] = "canada^ontario^hamilton^road name^link3.htm";
list[i++] = "canada^alberta^calgary^street name^link4.htm";
list[i++] = "canada^alberta^edmonton^street name^link5.htm";
list[i++] = "usa^new york^buffalo^street name^link6.htm";
list[i++] = "usa^new york^rochester^road name^link7.htm";
list[i++] = "usa^california^san jose^street name^link8.htm";
list[i++] = "usa^california^sacramento^road name^link9.htm";
 
C

C White

That looks great!!!
Thanks a lot, it looks like exactly what I have been driving myself nuts
searching for the last few days.
 
C

cwhite

I finally got around to putting your example to work, and it did the
job. Thanks.

I've been fiddling around for the last day trying to get it to open the
link a new window, and I'm not having much luck, I figure it has to do
with the line

location.href = item[4]

but I'm not having any luck whatsoever finding a way to do this. Can
you point me in the right direction?

Thanks again
 
M

McKirahan

I finally got around to putting your example to work, and it did the
job. Thanks.

I've been fiddling around for the last day trying to get it to open the
link a new window, and I'm not having much luck, I figure it has to do
with the line

location.href = item[4]

but I'm not having any luck whatsoever finding a way to do this. Can
you point me in the right direction?

Thanks again
Click to expand...

Are you invoking via a URL; for example,
http://localhost/droplist.htm

It won't work if you invoke it via:
C:\temp\droplist.htm

Here's a better version:

<html>
<head>
<title>droplist.htm</title>
<script type="text/javascript">
var i = 0;
var list = new Array();
list[i++] = "canada^ontario^toronto^street name^link1.htm";
list[i++] = "canada^ontario^toronto^road name^link2.htm";
list[i++] = "canada^ontario^hamilton^road name^link3.htm";
list[i++] = "canada^alberta^calgary^street name^link4.htm";
list[i++] = "canada^alberta^edmonton^street name^link5.htm";
list[i++] = "usa^new york^buffalo^street name^link6.htm";
list[i++] = "usa^new york^rochester^road name^link7.htm";
list[i++] = "usa^california^san jose^street name^link8.htm";
list[i++] = "usa^california^sacramento^road name^link9.htm";
var text = new Array("","","","");

function opts(that,what) {
var goto = "";
var item;
if (that != "") {
var okay;
if (what == 1) {
text[0] = that.options[that.selectedIndex].text;
text[1] = "";
text[2] = "";
text[3] = "";
document.form1.StateProv.options.length = 0;
document.form1.City.options.length = 0;
document.form1.Street.options.length = 0;
} else if (what == 2) {
text[1] = that.options[that.selectedIndex].text;
text[2] = "";
text[3] = "";
document.form1.City.options.length = 0;
document.form1.Street.options.length = 0;
} else if (what == 3) {
text[2] = that.options[that.selectedIndex].text;
text[3] = "";
document.form1.Street.options.length = 0;
} else if (what == 4) {
text[3] = that.options[that.selectedIndex].text;
var indx = that.options[that.selectedIndex].value;
if (indx != "") {
item = list[indx].split("^");
okay = "Okay?\n";
okay += "\nCountry : " + text[0];
okay += "\nState/Province : " + text[1];
okay += "\nCity : " + text[2];
okay += "\nStreet : " + text[3];
okay += "\nLink : " + item[4];
if (confirm(okay)) location.href = item[4];
}
}
}
// Build selection lists
if (what < 4) {
var k = 1;
var temp = "";
for (var j=0; j<list.length; j++) {
item = list[j].split("^");
if (what == 0) {
if (temp != item[what]) {
temp = item[what];
document.form1.Country.options[k++] = new Option(temp,
j);
}
} else if (what == 1) {
if (text[0] == item[0] && temp != item[what]) {
temp = item[what];
document.form1.StateProv.options[k++] = new Option(temp,
j);
}
} else if (what == 2) {
if (text[0] == item[0] && text[1] == item[1] && temp !=
item[what]) {
temp = item[what];
document.form1.City.options[k++] = new Option(temp, j);
}
} else if (what == 3) {
if (text[0] == item[0] && text[1] == item[1] && text[2] ==
item[2] && temp != item[what]) {
temp = item[what];
document.form1.Street.options[k++] = new Option(temp,
j);
}
}
}
}
}
</script>
</head>
<body onload="opts('',0)">
<form action="" method="get" name="form1">
&nbsp; <b>Country:</b> &nbsp;
<select name="Country" onchange="opts(this,1)" style="width:100px">
<option value=""></option>
</select>
&nbsp; <b>State/Province:</b> &nbsp;
<select name="StateProv" onchange="opts(this,2)" style="width:100px">
<option value=""></option>
</select>
&nbsp; <b>City:</b> &nbsp;
<select name="City" onchange="opts(this,3)" style="width:100px">
<option value=""></option>
</select>
&nbsp; <b>Street:</b> &nbsp;
<select name="Street" onchange="opts(this,4)" style="width:100px">
<option value=""></option>
</select>
</form>
</body>
</html>


The previous version:

1) was missing the </form> tag
2) had "State/ProvinceCity:"
3) did not have "if (what < 4) {}"
4) did not have the "confirm()"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Another Dynamic Droplist question 3
Filter table rows based on multiple checkboxes value 2
ASP/Java 3
How do I fix this issue in sqaurespace code block? 1
Dynamic Drop down list trouble 1
Combo box on ASP page 2
Survey details won't go through using php, ajax, Mysql 0
Java script on classic ASP page 6

Members online

No members online now.
Total: 57 (members: 0, guests: 57)
Robots: 484

Forum statistics

Threads
473,995
Messages
2,570,228
Members
46,818
Latest member
SapanaCarpetStudio

Latest Threads

Top