Hello silvia21,
I don’t know if this is the best solution. But, it is one option. In this example the Access database (MyDatabase.mdb) has two tables (Countries and Territories). You can extend the database schema if need be.
The table {Countries} just has two fields one called {CountryID} and the other is {CountryName}. {CountryID} is an auto incrementing field and {CountryName} is a text field that stores the name of a country.
The table {Territories} has two fields also; one called {CountryID} is a number field that is used to link the two tables together. The other field is {TerritoryName} and that is a text field that stores the name of a State or Province.
Let me know if I need to explain the relationship between the two tables more. For now I will assume that you understand the database part of this example.
Both Access Database and ASP file in this example are in the same folder.
Here is the example page called “Example.asp” (without the quotes):
-
<%
-
Set Conn = Server.CreateObject("ADODB.Connection")
-
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MyDatabase.mdb")
-
-
Set rsCountries = Server.CreateObject("ADODB.Recordset")
-
SQL = "SELECT * FROM Countries"
-
rsCountries.Open SQL, Conn
-
-
If (Len(Request.Form("CountryID")) > 1) Then
-
SQL = "SELECT * FROM Territories"
-
Else
-
SQL = "SELECT * FROM Territories WHERE CountryID = '" & Request.Form("CountryID") & "'"
-
End If
-
-
Set rsTerritories = Server.CreateObject("ADODB.Recordset")
-
rsTerritories.Open SQL, Conn
-
%>
-
<html>
-
<head>
-
<title>Example</title>
-
</head>
-
<body>
-
<table border="0">
-
<form method="post" action="Example.asp" name="xform">
-
<select name="CountryID" size="1" onchange="document.xform.submit();">
-
<%Do Until (rsCountries.EOF)%>
-
<option value="<%Response.Write(rsCountries("CountryID"))%>"<%If (Request.Form("CountryID")=rsCountries("CountryID")) Then%> selected<%End If%>><%Response.Write(rsCountries("CountryName"))%></option>
-
<%rsCountries.MoveNext%>
-
<%Loop%>
-
</select>
-
<select name="Territories" size="1">
-
<%Do Until (rsTerritories.EOF)%>
-
<option value="<%Response.Write(rsTerritories("TerritoryName"))%>"><%Response.Write(rsTerritories("TerritoryName"))%></option>
-
<%rsTerritories.MoveNext%>
-
<%Loop%>
-
</select>
-
</form>
-
</table>
-
</body>
-
</html>
-
<%
-
rsCountries.close
-
rsTerritories.close
-
Conn.close
-
%>
-
-
Hope this helps out~
6
