run database query when drop-down is selected

crmpicco · July 29th, 2005, 08:19 AM

I have this code in a ASP page:

Code:

<%
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

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

rupen · July 29th, 2005, 10:03 AM

Hi Picco,

I have been banging my head on wall to solve this prob, what i can say is that this is a sort of limitation of ASP, as it runs on serverside and values in combo is fill at client side, through reasearch of 1 month i found a link:
----------------
http://www.tek-tips.com/faqs.cfm?fid=3978
----------------
But i can't solve this one.

I can fill them with javascript array but i don't prefer this(although i have code for this).
If you come to solve this plea let me know on this forum.
All the best!!!

W

Rupen Anjaria.
We CAN'T avoid problems, but of course can solve it.

Big_Brother · July 31st, 2005, 03:46 AM

Hey,

You can run a JavaScript so that when the first drop-down list changes it
reloades the page with for example (page.asp?code=123) where the code is the country code
selected. Or you can do that but using POST.

crmpicco · August 9th, 2005, 10:16 AM

Code:

<% ' Last Edit: CRM_08aug05 %>

<%
Response.Buffer =true
on error resume next
''''''''''''''''''''''''''''''''''''''''
' destination.asp
''''''''''''''''''''''''''''''''''''''''
' Allow user to select the Airport 
' code from thrre drop-down menus
' Region, Country, City
'''''''''''''''''''''''''''''''''''''''''
companyid = Session("companyid")
'response.Write "companyid = " & companyid & "<br>"
Dim r
r=0

setLocale(2057) '... set the Local Settings to English - United Kingdom
%>
<HTML>
<HEAD>
<META NAME="Owner" CONTENT="crmpicco">
<META NAME="Copyright" CONTENT="crmpicco">
<META NAME="Author" CONTENT="crmpicco">
<title>Destination Search</title>

<style TYPE="text/css">

A:link{    COLOR: ;    TEXT-DECORATION: none}
A:visited{    COLOR: ;    TEXT-DECORATION: none}
A:hover{    COLOR: ;    TEXT-DECORATION: none}

</style>

</HEAD>

<body bgcolor="#f5f5f5" leftmargin="0"  rightmargin="0"  topmargin="0"  bottommargin="0">
<br>
<form name="form" id="form" method="post" action="destination2.asp">
<%
set rsregion=con.execute("SELECT DISTINCT Geog_loc FROM `dbo_tp_country` where Geog_loc IS NOT NULL order by Geog_loc asc")
%>
<table width="500" border="0" align="center" cellpadding="0" cellspacing="0">

    <tr>
      <td width="16"><img src="images/top_lefbb.gif" width="16" height="16"></td>
      <td height="16" background="images/top_midbb.gif"><img src="images/top_midbb.gif" width="16" height="16"></td>
      <td width="24"><img src="images/top_rigbb.gif" width="24" height="16"></td>
    </tr>
    <tr>
      <td width="16" background="images/cen_lef.gif"><img src="images/cen_lef.gif" width="16" height="11"></td>
      <td align="center" valign="middle" bgcolor="#FFFFFF">

<table width="100%" align="center" border="1" bordercolor="#000000">
<tr bgcolor="#006699" bordercolor="#000000">
<td colspan="10" bgcolor="#006699" bordercolor="#000000">
<b>Destination Search</b>
</td>
</tr>
<tr bordercolor="#ffffff"><td>&nbsp;</td></tr>
<tr bordercolor="#ffffff">
<td align="center">

Region:

</td>
<td colspan="2" align="left">

<select name="region" style="font-size:9px" onChange="form.submit()">
<option value="empty"> </option>
<%
if not rsregion.eof then
while not rsregion.eof
if rsregion("Geog_loc") <> "Africa - Gulf of Guinea" AND rsregion("Geog_loc") <> "Africa - Mozambique Channel" AND rsregion("Geog_loc") <> "Asia - Arabian Sea" AND rsregion("Geog_loc") <> "Asia - Commonwealth of Independent States" AND rsregion("Geog_loc") <> "Europe - Commonwealth of Independent States" AND rsregion("Geog_loc") <> "North America - Atlantic Ocean" AND rsregion("Geog_loc") <> "North America - North Atlantic Ocean" then
%>
<option value="<%=rsregion("Geog_loc")%>" id="region<%=r%>"><%=rsregion("Geog_loc")%></option>
<%
end if
r=r+1
rsregion.movenext
wend
end if
rsregion.close
set rsregion=nothing
%>
</select>
</td>
</tr>
<%
'if bRunMySQL then
'set rstest=con.execute("select * from dbo_tp_city")
'response.Write ("select * from dbo_tp_city")
'end if
%>
<tr bordercolor="#ffffff">
<td align="center" >Country:</td>
<td align="left">
<div onMouseOver="style.cursor='hand'">
<select name="country" style="font-size:9px " disabled title="Please Select a Region">
<option value="0"> </option>
</select>
</div>
</td>
</tr>

<tr bordercolor="#ffffff">
<td align="center">City:</td>
<td align="left">
<div onMouseOver="style.cursor='hand'">
<select name="city" style="font-size:9px " disabled  title="Please Select a Region">
<option value="0"> </option>
</select>
</div>
</td>
</tr>
<tr bordercolor="#ffffff"><td>&nbsp;</td></tr>
</table>


</td>
      <td width="24" background="images/cen_rigbb.gif"><img src="images/cen_rigbb.gif" width="24" height="11"></td>
    </tr>
    <tr>
      <td width="16" height="16"><img src="images/bot_lefbb.gif" width="16" height="16"></td>
      <td height="16" background="images/bot_midbb.gif"><img src="images/bot_midbb.gif"  height="16"></td>
      <td width="24" height="16"><img src="images/bot_rigbb.gif" width="24" height="16"></td>
    </tr>

</table>
</td></tr>
<div align="center">
<br>
<table width="75">
<tr>
<td height="20" bordercolor="#000000" align="center" bgcolor="#006699" onClick="window.close();"
onMouseOver="this.bgColor = '#C0C0C0'; this.style.color='#000000'; style.cursor='hand';window.status='';" 
    onMouseOut="this.bgColor = '#006699'; this.style.color='#FFFFFF';window.status='';">

        Close

    </td>
    </tr>
    </table>
  </div>

<input type="hidden" name="onlycity" value="true">
<input type="hidden" name="paste" value="<%=request("paste")%>">
</form>
</BODY> 
</HTML>

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt
www.piccosmini.co.uk.tt
www.morton.uk.tt

vinod_yadav1919 · August 9th, 2005, 12:28 PM

Crmpicco!!
Can we use the same thing
with
http://p2p.wrox.com/topic.asp?TOPIC_ID=31530

Cheers :)

vinod