<%
  Set objDC = Server.CreateObject("ADODB.Connection")
  strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/database/northwind.mdb")
  objDC.Open strConn
  Set objRS = objDC.Execute("Select Distinct Country FROM Suppliers")
    %>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="33%">&nbsp;</td>
<td width="47%">
<FORM METHOD="POST" NAME="Form1" ACTION="our_supplier_in.asp">
<p align="left">
<SELECT NAME="Country" SIZE="1" ONCHANGE=Form1.submit()>
<option selected><% = Request.Form("Country") %></option>
<%
  ' Continue until we get to the end of the recordset.
  Do Until objRS.EOF
  ' For each record we create a option tag and set it's value to the country
  %>
<OPTION><%= objRS("Country") %></OPTION>
<%
  ' Get next record
  objRS.MoveNext
  Loop
  %>
</SELECT> <b><font face="Arial" size="2" color="#008080">Choose a Country</font></b></p>
</FORM>
<%
  ' Close Data Access Objects and free DB variables
    objRS.Close
    Set objRS = Nothing
    objDC.Close
    Set objDC = Nothing
    %>
  
  <!-- End first Drop Down -->
  <!--Second drop down -->
  <%
    'Some code to hide the second drop down until we make a selection from the first
    IF Request.Form("Country") = "" Then
    Else
    'If Country has a value then we get a list of cities for the second drop down
    Set objDC = Server.CreateObject("ADODB.Connection")
    strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/database/northwind.mdb")
    objDC.Open strConn
  Set objRS = objDC.Execute("Select City FROM Suppliers WHERE Country = '" & Request.Form("Country") & "'")<br>
    %>
<FORM METHOD="POST" NAME="Form2" ACTION="our_supplier_in.asp">
<p align="left">
<font face="Arial"><font color="#008080"><b>
<SELECT NAME="City" SIZE="1" ONCHANGE=Form2.submit()>
<option selected><% = Request.Form("City") %></option>
<%
  ' Continue until we get to the end of the recordset.
  Do Until objRS.EOF
  ' For each record we create a option tag and set it's value to the city
  %>
<OPTION><%= objRS("City") %></OPTION>
<%
  ' Get next record
  objRS.MoveNext
  Loop
  %>
<%
  'Set a hidden value in the second form for the Country 
  'so we can pass it along with the city to the next query
  %>
</SELECT></b></font><b><font size="2" color="#008080"> Choose a City</font></b><font color="#008080"><b><input type="hidden" name="Country" value="<% = Request.Form("Country") %>"></b></font></font></p>
</FORM>
  <%
    ' Close Data Access Objects and free DB variables
    objRS.Close
    Set objRS = Nothing
    objDC.Close
    Set objDC = Nothing
    End IF
    %>
  <!-- Display the records -->
   </td>
<td width="20%"></td>
</tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber2">
<tr>
<td width="33%">&nbsp;</td>
<td width="47%">
<% 
  'Make sure we have submitted a city and don't show results until we do
  IF Request.Form("city") = "" Then
  Else
  Set objDC = Server.CreateObject("ADODB.Connection")
  strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/database/northwind.mdb")
  objDC.Open strConn
  Set objRS = objDC.Execute("Select * FROM Suppliers WHERE Country = '" & Request.Form("Country") & "' AND City = '" & Request.Form("city") & "'")
  'Loop through the database and assign the appropriate values to variables
    'that we will use later
 Do Until objRS.EOF
    CompanyName = objRS("CompanyName")
    ContactName = objRS("ContactName")
    Address = objRS("Address")
    City = objRS("City")
    Region = objRS("Region")
    Zip = objRS("PostalCode")
    Phone1 = objRS("Phone")
    Fax1 = objRS("Fax")
    Country = objRS("country")
    objRS.MoveNext
    Loop
  objRS.Close
    Set objRS = Nothing
    objDC.Close<    Set objDC = Nothing
    %>
</div>
<p align="left"><b><font face="Arial" size="2" color="#008080">Our Supplier in: </font>
<font face="Arial" size="2" color="#800000"> <% Response.Write City & ", " & Country %> </font></b>
<br>
<font face="Arial" size="1" color="#008080">
<%
  'Set up the display of the record
  Response.Write CompanyName & "<br>"
  Response.Write ContactName & "<br>"
  Response.Write Address & "<br>"
  Response.Write City & ", " & Region & " " & Zip & "<br>"
  Response.Write "Phone: " & Phone1 & "<br>"
  IF Fax1 <> "" Then
  Response.Write "Fax: " & Fax1 & "<br>"
  End IF
  End IF
  %> 
  </font>
  </p>
   </td>
<td width="20%">&nbsp;</td>
</tr>
</table>