<html>
<head>
<title>A SAMPLE PAGE TO SHOW HOW TO RETRIEVE DATA FROM CASCADING COMBOS LOADED FROM THE DATABASE</title>
<%
IF REQUEST.FORM("PB")="POSTED" THEN
POSTBACK = TRUE
ELSE
POSTBACK = FALSE
END IF
SET CONN = SERVER.CREATEOBJECT("ADODB.CONNECTION")
SET RS = SERVER.CREATEOBJECT("ADODB.RECORDSET")
CONNSTR = "DRIVER=MICROSOFT ACCESS DRIVER (*.MDB);DBQ=" & SERVER.MAPPATH("/FPDB/US_CITIES.MDB")
CONN.OPEN CONNSTR
%>
<script language="vbscript">
<%IF NOT POSTBACK THEN%>
SUB WINDOW_ONLOAD
X = WINDOW.SETTIMEOUT ("WINDOW.FORM1.SUBMIT",5000)
END SUB
<%ELSE%>
SUB CITY_ONCHANGE
NDX = WINDOW.FORM1.CITY.SELECTEDINDEX
LL = WINDOW.FORM1.CITY(NDX).VALUE
N = INSTR(1,LL,"|")
L = LEN(LL)
WINDOW.LONGITUDE.INNERTEXT = LEFT(LL,N-1)
WINDOW.LATITUDE.INNERTEXT = RIGHT(LL,L-N)
END SUB
<%END IF%>
</script>
</head>
<body>
<div align="center">
<h2>US CITIES GEOGRAPHICAL COORDINATES DATABASE</h2>
<form method="POST" action="cities.asp" id="FORM1" target="_self">
<%
IF NOT POSTBACK THEN
QUERYSTR = "SELECT COUNT(STATES.STATE) FROM STATES"
RS.OPEN QUERYSTR , CONN
STATES_COUNT = RS.FIELDS(0)
RS.CLOSE
QUERYSTR = "SELECT COUNT(CITY), MIN(LONGITUDE), MAX(LONGITUDE), MIN(LATITUDE), MAX(LATITUDE) FROM CITIES"
RS.OPEN QUERYSTR , CONN
CITIES_COUNT = RS.FIELDS(0)
MN_G = RS.FIELDS(1)
MX_G = RS.FIELDS(2)
MN_T = RS.FIELDS(3)
MX_T = RS.FIELDS(4)
RS.CLOSE
QUERYSTR = "SELECT CITY, STATE FROM CITIES WHERE LONGITUDE=" & MN_G
RS.OPEN QUERYSTR , CONN
MIN_LONG = MN_G & " (" & RS.FIELDS(0) & ", " & RS.FIELDS(1) & ")"
RS.CLOSE
QUERYSTR = "SELECT CITY, STATE FROM CITIES WHERE LONGITUDE=" & MX_G
RS.OPEN QUERYSTR , CONN
MAX_LONG = MX_G & " (" & RS.FIELDS(0) & ", " & RS.FIELDS(1) & ")"
RS.CLOSE
QUERYSTR = "SELECT CITY, STATE FROM CITIES WHERE LATITUDE=" & MN_T
RS.OPEN QUERYSTR , CONN
MIN_LAT = MN_T & " (" & RS.FIELDS(0) & ", " & RS.FIELDS(1) & ")"
RS.CLOSE
QUERYSTR = "SELECT CITY, STATE FROM CITIES WHERE LATITUDE=" & MX_T
RS.OPEN QUERYSTR , CONN
MAX_LAT = MX_T & " (" & RS.FIELDS(0) & ", " & RS.FIELDS(1) & ")"
RS.CLOSE
%>
<h3>Total number of States = <%=STATES_COUNT%></h3>
<h3>Total number of Cities = <%=CITIES_COUNT%></h3>
<h3>Minimum Longitude = <%=MIN_LONG%></h3>
<h3>Maximum Longitude = <%=MAX_LONG%></h3>
<h3>Minimum Latitude = <%=MIN_LAT%></h3>
<h3>Maximum Latitude = <%=MAX_LAT%></h3>
<input type="button" value="OK" onclick="WINDOW.FORM1.SUBMIT">
<%
ELSE
%>
<table border="0" width="90%" id="table1" cellpadding="2">
<tr>
<td align="center" width="34%"><b><font size="4">State</font></b></td>
<td align="center" width="34%"><b><font size="4">City</font></b></td>
<td align="center" width="15%"><b><font size="4">Longitude</font></b></td>
<td align="center" width="15%"><b><font size="4">Latitude</font></b></td>
</tr>
<tr>
<td align="center">
<select name="STATE" style="width:95%" onchange="WINDOW.FORM1.SUBMIT">
<%
STATE = Request.Form("STATE")
QUERYSTR = "SELECT STATE,ABBREVIATION FROM STATES ORDER BY STATE"
RS.OPEN QUERYSTR , CONN
DO WHILE NOT RS.EOF
IF STATE = RS.FIELDS("ABBREVIATION") THEN
SLC = " SELECTED"
ELSE
SLC=""
END IF
%>
<option value=<%=RS.FIELDS("ABBREVIATION")%> <%=SLC%>><%=RS.FIELDS("STATE")%></option>
<%
RS.MOVENEXT
LOOP
RS.CLOSE
%>
</select>
</td>
<td align="center">
<select name="CITY" style="width:95%">
<option value="--" selected>SELECT CITY</option>
<%
QUERYSTR = "SELECT CITY,LONGITUDE,LATITUDE FROM CITIES WHERE STATE='" & STATE & "' ORDER BY CITY"
RS.OPEN QUERYSTR , CONN
DO WHILE NOT RS.EOF
%>
<option value=<%=RS.FIELDS("LONGITUDE") & "|" & RS.FIELDS("LATITUDE") %>><%=RS.FIELDS("CITY")%></option>
<%
RS.MOVENEXT
LOOP
RS.CLOSE
%>
</select>
</td>
<td id="LONGITUDE" align="center" style="border-style: solid; border-width: 1px; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px" bordercolor="#000000"> </td>
<td id="LATITUDE" align="center" style="border-style: solid; border-width: 1px; padding-left: 4px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px" bordercolor="#000000"> </td>
</tr>
</table>
<%
END IF
%>
<%
SET RS = NOTHING
CONN.CLOSE
SET CONN = NOTHING
%>
<input type="hidden" name="PB" value="POSTED">
</form>
</div>
</body>
</html>
