running DB hit when drop-down is selected

[crmpicco]

I have this code in a ASP page:

<%
set rsregion=con.execute("SELECT DISTINCT Geog_loc FROM `dbo_tp_country` where Geog_loc IS NOT NULL order by Geog_loc asc")
set rsregion2=con.execute("SELECT DISTINCT Geog_loc,area,CountryCode FROM `dbo_tp_country` where Geog_loc IS NOT NULL order by CountryCode asc")
%>

<select name="region" style="font-size:9px" onChange="<%bRunMySQL=true%>">
<%
if not rsregion.eof then
while not rsregion.eof
%>
<option value="<%=rsregion2("CountryCode")%>" id="region<%=r%>"><%=rsregion("Geog_loc")%>
<%
r=r+1
rsregion.movenext
wend
end if
rsregion.close
set rsregion=nothing
%>
</select>

<%
if bRunMySQL then
set rstest=con.execute("select * from dbo_tp_city")
response.Write "select * from dbo_tp_city"
end if
%>

When the drop-down is selected I need to run another MySQL query to produce the drop-down
values for another drop-down.

How can i do this, as you can see i have attempted this with setting the boolean vaue bRunMySQL.

Any ideas?

TIA.

Picco

[nikkiH]

Something like this, except use ASP instead of PHP.

http://www.ipwebdesign.net/useful_dynamicSelectsDB.html

[neocool00]

onChange=form.submit()

Submit the form when the user selects something in the first drop down, then on the reload, populate the second drop down.

Another option would be to use Javascript to supply the values of the second drop down, but depending on the number of options and such, this can get quite messy. Also, you would need to use ASP to pull all of the drop down options and store them into Javascript arrays.

[glenngv]

This generates the option list from the DB without reloading the page.
The demo is in php but you can download an asp version.

http://www.ashleyit.com/rs/jsrs/select/php/select.php