<html>
<head>
<title>Multiple Dynamic Drop Down Boxes - ASP</title>
</head>
<body>
<%
' Get the form value for State and place it in a variable
v_state = request.form("State")
' Open Database and setup a recordset with States Listed.
' Note the Distinct option in the SQL statement. This will limit each state to
showing only once in the recordset.
set conn = server.createobject("ADODB.Connection")
Conn.open("driver={SQL Server};server=xxxx;uid=xxxx;pwd=xxxx;database=xxxx")
strSQL = "Select Distinct State from tbAddress Order by State"
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")
else
if v_State = "" then
v_State = rs("State")
end if
%>
<form name="frmAddress"
method="POST" action="">
<table>
<tr>
<td>Name:</td>
<td><input type=text name="fullName"
value="<%=request.form("fullName")%>"
/></td>
</tr>
<tr>
<td>Address:</td>
<td><input type=text name="address"
value="<%=request.form("Address")%>"
/></td>
</tr>
<tr>
<td>State</td>
<td>
<!-- Here is the key to the whole thing. The onChange
function is set to submit the page with the new State value.
For those who don't know, request.servervariables("Script_Name") will return the
name of your current page
You could of course hard code your page name rather than using the server
variable, I use this in the example
so that if it is copied to another page the script requires less modification.
The onChange event is actually running two commands here. The first
changes the action of the form to the current
page. The second submits the form. (note this is one line although it may
wrap on your screen.
-->
<select size="1" name="State"
onChange="document.frmAddress.action='<%=request.servervariables("Script_Name")%>';document.frmAddress.submit();">
<%do while not rs.eof
if rs("State") = v_State then
%>
<option selected value="<%=rs("State")%>"><%=rs("State")%></option>
<%
else %>
<option value="<%=rs("State")%>"><%=rs("State")%></option>
<%
end if
rs.movenext
loop
rs.close
set rs = nothing
%>
</select>
</td>
</tr>
<tr>
<td>City</td>
<td>
<select size="1" name="City">
<%
' Here is where we are using the State value to select the correct cities to be
shown.
strSQL = "Select Distinct City from tbAddress where State='" & v_State & "'
Order by City"
set rs = conn.execute(strSQL)
do while not rs.eof %>
<option value="<%=rs("City")%>"><%=rs("City")%></option>
<%
rs.movenext
loop
%>
</select>
</td>
</tr>
<tr>
<td>Phone</td>
<td><input type=text name="Phone"
value="<%=request.form("Phone")%>"
/></td>
</tr>
</table>
<p><input type="submit" value="Submit"
name="B1"><input type="reset"
value="Reset" name="B2"></p>
</form>
<%
end if
%>
</body>
</html>