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?Countr y=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="JavaS cript">
<!--
function display(what){
document.getEle mentById(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.as p" method="get">
<%
' Create ADO data connection object
Set cnnLinkDB = Server.CreateOb ject("ADODB.Con nection")
' Open data connection
cnnLinkDB.Open "DBQ=" & Server.MapPath( "master_table.m db") & ";" _
& "Driver={Micros oft 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.CreateOb ject("ADODB.Rec ordset")
rstCountry.Open strSQL, cnnLinkDB
' Build our drop down box of countries
If Not rstCountry.EOF Then
rstCountry.Move First
%>
Country: <br>
<select name="Country" onchange=submit ()>
<option></option>
<% ' Loop through names
Do While Not rstCountry.EOF
Response.Write "<option value="""
Response.Write rstCountry.Fiel ds("Country")
Response.Write """"
Response.Write ">"
Response.Write rstCountry.Fiel ds("Country")
Response.Write "</option>" & vbCrLf
' Move to next record
rstCountry.Move Next
Loop
%>
</select>
<%
End If
' Close ADO objects we're finished with and free DB variables
rstCountry.Clos e
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.CreateOb ject("ADODB.Rec ordset")
rstProv_State.O pen strSQL, cnnLinkDB
' Build our drop down box of Prov_States
If Not rstProv_State.E OF Then
rstProv_State.M oveFirst
%>
<p>Prov/State: <br>
<select name="Prov_Stat e" onchange=submit ()>
<option></option>
<% ' Loop through names
Do While Not rstProv_State.E OF
Response.Write "<option value="""
Response.Write rstProv_State.F ields("Prov_Sta te")
Response.Write """"
Response.Write ">"
Response.Write rstProv_State.F ields("Prov_Sta te")
Response.Write "</option>" & vbCrLf
' Move to next record
rstProv_State.M oveNext
Loop
%>
</select>
<%
End If
' Close ADO objects we're finished with and free DB variables
rstProv_State.C lose
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.CreateOb ject("ADODB.Rec ordset")
rstCity.Open strSQL, cnnLinkDB
' Build our drop down box of areas
If Not rstCity.EOF Then
rstCity.MoveFir st
%>
<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.MoveNex t
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.CreateOb ject("ADODB.Rec ordset")
rstStreet.Open strSQL, cnnLinkDB
' Build our drop down box of areas
If Not rstStreet.EOF Then
rstStreet.MoveF irst
%>
<p>Street: <br>
<select name="Street"
onchange="windo w.open(this.opt ions[this.selectedIn dex].value,'_blank' )">
<option></option>
<% ' Loop through names
Do While Not rstDetail.EOF
Response.Write "<option value="""
Response.Write rstStreet.Field s("Link")
Response.Write """"
Response.Write "selected=""tru e"""
Response.Write ">"
Response.Write rstStreet.Field s("Street")
Response.Write "</option>" & vbCrLf
' Move to next record
rstDetail.MoveN ext
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.as p" 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
