Search with multiple menus

forry · November 5th, 2005, 07:23 AM

I've used a 'Search with multiple menus' page (www.burnsupportgroupsdatabase.com/test) which is linked to a database. However and if you have a look at the website (select, search/Support Group), you will see that if one select 'North America' as a region and then USA or Canada (countries in that region) as the next selection, all states/provinces of both countries are showing up instead of just the state/province of USA or Canada showing up. Any ideas folks? Code is below:

<%
Dim rsCountry
Dim rsCountry_numRows

Set rsCountry = Server.CreateObject("ADODB.Recordset")
rsCountry.ActiveConnection = MM_BSGDatabase_STRING
rsCountry.Source = "SELECT DISTINCT country FROM bsgd WHERE continent = '" + Replace(rsCountry__MMColParam, "'", "''") + "'"
rsCountry.CursorType = 0
rsCountry.CursorLocation = 2
rsCountry.LockType = 1
rsCountry.Open()

rsCountry_numRows = 0
%>
<%
Dim rsStateProvince__MMColParam
rsStateProvince__MMColParam = "1"
If (Request.Form("continent") <> "") Then
  rsStateProvince__MMColParam = Request.Form("continent")
End If
%>
<%
Dim rsStateProvince
Dim rsStateProvince_numRows

Set rsStateProvince = Server.CreateObject("ADODB.Recordset")
rsStateProvince.ActiveConnection = MM_BSGDatabase_STRING
rsStateProvince.Source = "SELECT DISTINCT state_province FROM bsgd WHERE continent = '" + Replace(rsStateProvince__MMColParam, "'", "''") + "'"
rsStateProvince.CursorType = 0
rsStateProvince.CursorLocation = 2
rsStateProvince.LockType = 1
rsStateProvince.Open()

rsStateProvince_numRows = 0
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.1 Transitional//EN">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Search Form 2</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="../css/normallinks.css" rel="stylesheet" type="text/css" />
</head>

<body>
<p><img src="../images/english/counstatprov.gif"></p>
<h4><br>
  Your selected World Region is: <strong><%= Request.Form("continent") %></strong></h4>
<p>Please select your Country, your State-Province-Sub country and click 'Search'.</p>
<form action="results.asp" method="post" name="country_state_form" id="country_state_form">
  <table width="60%" border="0" cellspacing="2" cellpadding="2">
    <tr>
      <td width="37%">Country</td>
      <td width="63%">State-Province-Sub Country</td>
    </tr>
    <tr>
      <td><select name="country" id="country">
          <option value="%" selected>All</option>
          <%
While (NOT rsCountry.EOF)
%>
          <option value="<%=(rsCountry.Fields.Item("country").Value) %>"& gt;<%=(rsCountry.Fields.Item("country").Value)%></o ption>
          <%
  rsCountry.MoveNext()
Wend
If (rsCountry.CursorType > 0) Then
  rsCountry.MoveFirst
Else
  rsCountry.Requery
End If
%>
        </select></td>
      <td><select name="state_province" id="state_province">
          <option value="%" selected>All</option>
          <%
While (NOT rsStateProvince.EOF)
%>
          <option value="<%=(rsStateProvince.Fields.Item("state_prov ince"). Value)%>"><%=(rsStateProvince.Fields.Item("state_p r ovince").Value)%></option>
          <%
  rsStateProvince.MoveNext()
Wend
If (rsStateProvince.CursorType > 0) Then
  rsStateProvince.MoveFirst
Else
  rsStateProvince.Requery
End If
%>
        </select></td>
    </tr>
    <tr>
      <td><input name="continent" type="hidden" id="continent" value="<%= Request.Form("continent") %>"></td>
      <td align="right" valign="top"><input type="submit" name="Submit" value="Search"></td>
    </tr>
  </table>
</form>
<p> </p>
</body>
</html>
<%
rsCountry.Close()
Set rsCountry = Nothing
%>
<%
rsStateProvince.Close()
Set rsStateProvince = Nothing
%>