Solved

dependant dynamic drop down list

Posted on 2004-08-18
23
325 Views
Last Modified: 2008-02-01
Hi,

Hope someone could help.

I don’t know much about how to code this in ASP, so please help me.

I’m trying to populate dynamically three drop down list, each is dependant on the one before it.

Using Access, there are three tables which consist of the following values and column names:

County
Name      c_id
Wales      1
England      2
Scotland      3

Region
name       r_id      c_id
Cardiff      1      1
Newport      2      1
Ealing      3      2
Harrow      4      2
Athol      5      5
Adalbane      6      5

Area
Name      a_id      r_id
Roath      1      1
Cathays      2      1
Riverside      3      1
Malpas      4      2
Hanwell      5      3      
Kew      6      4
Straun      7      5
Lawers      8      6


Thanks
0
Comment
Question by:Ysd
  • 10
  • 8
  • 2
  • +2
23 Comments
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="javascript1.1">
function setOptions(chosen) {
var selbox = document.myform.opttwo;
 
selbox.options.length = 0;
if (chosen == " ") {
  selbox.options[selbox.options.length] = new Option('Please select one of the options above first',' ');
 
}
if (chosen == "1") {
  selbox.options[selbox.options.length] = new Option('Cardiff','1');
  selbox.options[selbox.options.length] = new Option('Newport','2');
}
if (chosen == "2") {
  selbox.options[selbox.options.length] = new Option('Ealing','3');
  selbox.options[selbox.options.length] = new Option('Harrow','4');
}
if (chosen == "3") {
  selbox.options[selbox.options.length] = new Option('Athol','5');
  selbox.options[selbox.options.length] = new Option('Adalbane','6');
}
}
function setSubOptions(chosen) {
var selbox = document.myform.optThree;
 
selbox.options.length = 0;
if (chosen == " ") {
  selbox.options[selbox.options.length] = new Option('Please select one of the options above first',' ');
 
}
if (chosen == "1") {
  selbox.options[selbox.options.length] = new Option('Roath','1');
  selbox.options[selbox.options.length] = new Option('Cathays','2');
  selbox.options[selbox.options.length] = new Option('Riverside','3');
}
if (chosen == "2") {
  selbox.options[selbox.options.length] = new Option('Malpas','3');
}
if (chosen == "3") {
  selbox.options[selbox.options.length] = new Option('Hanwell','4');
}
if (chosen == "4") {
  selbox.options[selbox.options.length] = new Option('Kew','5');
}
if (chosen == "5") {
  selbox.options[selbox.options.length] = new Option('Straun','6');
}
if (chosen == "6") {
  selbox.options[selbox.options.length] = new Option('Lawers','7');
}

}
</script>
</head>

<body>
<form name="myform"><div align="center">
<select name="optone" size="1"
onchange="setOptions(document.myform.optone.options[document.myform.optone.selectedIndex].value);">
<option value=" " selected="selected"> </option>
<option value="1">Wales</option>
<option value="2">England</option>
<option value="3">Scotland</option>
</select><br /> <br />
<select name="opttwo" size="1"
onchange="setSubOptions(document.myform.opttwo.options[document.myform.opttwo.selectedIndex].value);">
<option value=" " selected="selected">Please select one of the options above first</option>
</select><br /> <br />
<select name="optThree" size="1">
<option value=" " selected="selected">Please select one of the options above first</option>
</select>
</form>
</body>
</html>
0
 

Author Comment

by:Ysd
Comment Utility
leechoonhwee,

I've got it to work on static basis like you have done aboce.  But I cant seem to get it to work dynamically from a database, as mentioned above, there are three tables in MS Access which the data is populated from.

0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
Revised for alittle bit more user friendly

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="javascript1.1">
function setOptions(chosen) {
var selbox = document.myform.opttwo;
 
selbox.options.length = 0;
if (chosen == " ") {
  selbox.options[selbox.options.length] = new Option('Please select one of the options above first',' ');
 
}
if (chosen == "1") {
  selbox.options[selbox.options.length] = new Option('Please select one','0');
  selbox.options[selbox.options.length] = new Option('Cardiff','1');
  selbox.options[selbox.options.length] = new Option('Newport','2');
}
if (chosen == "2") {
  selbox.options[selbox.options.length] = new Option('Please select one','0');
  selbox.options[selbox.options.length] = new Option('Ealing','3');
  selbox.options[selbox.options.length] = new Option('Harrow','4');
}
if (chosen == "3") {
  selbox.options[selbox.options.length] = new Option('Please select one','0');
  selbox.options[selbox.options.length] = new Option('Athol','5');
  selbox.options[selbox.options.length] = new Option('Adalbane','6');
}
}
function setSubOptions(chosen) {
var selbox = document.myform.optThree;
 
selbox.options.length = 0;
if (chosen == " ") {
  selbox.options[selbox.options.length] = new Option('Please select one of the options above first',' ');
 
}
if (chosen == "1") {  
  selbox.options[selbox.options.length] = new Option('Roath','1');
  selbox.options[selbox.options.length] = new Option('Cathays','2');
  selbox.options[selbox.options.length] = new Option('Riverside','3');
}
if (chosen == "2") {
  selbox.options[selbox.options.length] = new Option('Malpas','3');
}
if (chosen == "3") {
  selbox.options[selbox.options.length] = new Option('Hanwell','4');
}
if (chosen == "4") {
  selbox.options[selbox.options.length] = new Option('Kew','5');
}
if (chosen == "5") {
  selbox.options[selbox.options.length] = new Option('Straun','6');
}
if (chosen == "6") {
  selbox.options[selbox.options.length] = new Option('Lawers','7');
}

}
</script>
</head>

<body>
<form name="myform"><div align="center">
<select name="optone" size="1"
onchange="setOptions(document.myform.optone.options[document.myform.optone.selectedIndex].value);">
<option value=" " selected="selected"> </option>
<option value="1">Wales</option>
<option value="2">England</option>
<option value="3">Scotland</option>
</select><br /> <br />
<select name="opttwo" size="1"
onChange="setSubOptions(document.myform.opttwo.options[document.myform.opttwo.selectedIndex].value);">
<option value=" " selected="selected">Please select one of the options above first</option>
</select><br /> <br />
<select name="optThree" size="1">
<option value=" " selected="selected">Please select one of the options above first</option>
</select>
</form>
</body>
</html>
0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
I am sorry about the mistake!!!! Do you want to post to refresh or you want it to refresh instantly.
0
 

Author Comment

by:Ysd
Comment Utility
I dont want the page to reload, so I guess I want the page to refresh instantly.
0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
<%
v_state = request.querystring("State")

set conn = server.createobject("ADODB.Connection")
Conn.open("driver={SQL Server};server=XX;uid=xx;pwd=xxx;database=xxxx")
strSQL = "Select Distinct * from Country Order by Country"
set rs = conn.execute(strSQL)

if rs.eof then
    response.write("No Country Found")
else
    if v_State = "" then
    v_State = rs("ID")
    end if
    %>
    <form method="POST" action="">
       <select size="1" name="State" onChange="window.location='<%=request.servervariables("Script_Name")%>?State='+this.value;">

    <%do while not rs.eof
    if rs("Country") = v_State then
        %>
        <option selected value="<%=rs("ID")%>"><%=rs("Country")%></option>
        <%
    else
        %>
        <option value="<%=rs("ID")%>"><%=rs("Country")%></option>
        <%
    end if
    rs.movenext
    loop
    rs.close
    set rs = nothing
    %>
    </select>
    &nbsp;&nbsp; City
    <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 * from Region where Cid='" & v_State & "' Order by City"
    set rs = conn.execute(strSQL)
    do while not rs.eof
    %>
    <option value="<%=rs("Rid")%>"><%=rs("City")%></option>
    <%
    rs.movenext
    loop
    %>
    </select>
    <p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
    </form>
    <%
end if
%>
0
 

Author Comment

by:Ysd
Comment Utility
leechoonhwee,

Thanks for your response.

1/. I've tried your code, i had to change the name of the variables because they were from an old code.
It's displaying the drop down list, but with no values in the drop down list.

2/.  There should be three drop down list, it's only working on two at the moment.

This is the code I'm working on:


<%
v_state = request.querystring("county")

Set Conn  = Server.CreateObject("ADODB.Connection")
      Conn.Open "DSN=provision;UID=shdsuk;PWD=snowball"
      strSQL = "Select Distinct * from County Order by County"
set rs = conn.execute(strSQL)

if rs.eof then
    response.write("No County Found")
else
    if v_State = "" then
    v_State = rs("c_id")
    end if
    %>
    <form method="POST" action="">
  County
  <select size="1" name="county" onChange="window.location='<%=request.servervariables("Script_Name")%>?county='+this.value;">

    <%do while not rs.eof
    if rs("County") = v_State then
        %>
        <option selected value="<%=rs("c_id")%>"><%=rs("County")%></option>
        <%
    else
        %>
        <option value="<%=rs("c_id")%>"><%=rs("County")%></option>
        <%
    end if
    rs.movenext
    loop
    rs.close
    set rs = nothing
    %>
    </select>
  &nbsp;&nbsp;Region
  <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 * from Region where c_id='" & v_State & "' Order by region"
    set rs = conn.execute(strSQL)
    do while not rs.eof
    %>
    <option value="<%=rs("r_id")%>"><%=rs("region")%></option>
    <%
    rs.movenext
    loop
    %>
    </select>
    <p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
    </form>
    <%
end if
%>
0
 

Author Comment

by:Ysd
Comment Utility
leechoonhwee you have left me??!!
0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
LOL....no I have not. Just that I went home! I came across a better script at macromedia but itz more complicated than this one that I gave you. I have tried similiar code before and it should work. However, it is based on 2 listbox model. You need to duplicate the populateDynaList function a second time and add the respective variable to accomodate the 3rd listbox. The advantage is that this will be instant population without refresh. I will be glad to help you with the code if you are having difficulties. Cheers!!!

Opps.. This is the link to view the code...http://www.macromedia.com/support/dreamweaver/ts/documents/client_dynamic_listbox.htm
0
 

Author Comment

by:Ysd
Comment Utility
Leechoonhwee,

I've been on this link a few days ago, I've got it to work for two drop down menu list.  But cant get it to work for the third one.  Could you help me on the third one please, here is the code:


<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/instant_exchange.asp" -->
<%
Dim county_list
Dim county_list_numRows

Set county_list = Server.CreateObject("ADODB.Recordset")
county_list.ActiveConnection = MM_instant_exchange_STRING
county_list.Source = "SELECT * FROM county"
county_list.CursorType = 0
county_list.CursorLocation = 2
county_list.LockType = 1
county_list.Open()

county_list_numRows = 0
%>
<%
Dim region_list
Dim region_list_numRows

Set region_list = Server.CreateObject("ADODB.Recordset")
region_list.ActiveConnection = MM_instant_exchange_STRING
region_list.Source = "SELECT * FROM region"
region_list.CursorType = 0
region_list.CursorLocation = 2
region_list.LockType = 1
region_list.Open()

region_list_numRows = 0
%>
<%
Dim area_list
Dim area_list_numRows

Set area_list = Server.CreateObject("ADODB.Recordset")
area_list.ActiveConnection = MM_instant_exchange_STRING
area_list.Source = "SELECT * FROM area"
area_list.CursorType = 0
area_list.CursorLocation = 2
area_list.LockType = 1
area_list.Open()

area_list_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Input Bangladesh Bank Details</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="file:///C|/css.css" type="text/css">
<!-- Dynamic Dependent List box Code for *** VBScript *** Server Model //-->
<script language="JavaScript">
<!--

var arrDynaList = new Array();
var arrDL1 = new Array();

arrDL1[1] = "select_county";               // Name of parent list box
arrDL1[2] = "form1";                  // Name of form containing parent list box
arrDL1[3] = "select_region";               // Name of child list box
arrDL1[4] = "form1";                  // Name of form containing child list box
arrDL1[5] = arrDynaList;
 
<%
Dim txtDynaListRelation, txtDynaListLabel, txtDynaListValue, oDynaListRS

txtDynaListRelation = "c_id"      ' Name of recordset field relating to parent
txtDynaListLabel = "region"            ' Name of recordset field for child Item Label
txtDynaListValue = "r_id"            ' Name of recordset field for child Value
Set oDynaListRS = region_list             ' Name of child list box recordset
 
Dim varDynaList
varDynaList = -1

Dim varMaxWidth
varMaxWidth = "1"

Dim varCheckGroup
varCheckGroup = oDynaListRS.Fields.Item(txtDynaListRelation).Value

Dim varCheckLength
varCheckLength = 0

Dim varMaxLength
varMaxLength = 0

While (NOT oDynaListRS.EOF)

 If (varCheckGroup <> oDynaListRS.Fields.Item(txtDynaListRelation).Value) Then
  If (varCheckLength > varMaxLength) Then
   varMaxLength = varCheckLength
  End If
  varCheckLength = 0
 End If
%>
 arrDynaList[<%=(varDynaList+1)%>] = "<%=(oDynaListRS.Fields.Item(txtDynaListRelation).Value)%>"
 arrDynaList[<%=(varDynaList+2)%>] = "<%=(oDynaListRS.Fields.Item(txtDynaListLabel).Value)%>"
 arrDynaList[<%=(varDynaList+3)%>] = "<%=(oDynaListRS.Fields.Item(txtDynaListValue).Value)%>"
<%
 If (len(oDynaListRS.Fields.Item(txtDynaListLabel).Value) > len(varMaxWidth)) Then
  varMaxWidth = oDynaListRS.Fields.Item(txtDynaListLabel).Value
 End If
 varCheckLength = varCheckLength + 1
 varDynaList = varDynaList + 3
 oDynaListRS.MoveNext()
Wend

If (varCheckLength > varMaxLength) Then
 varMaxLength = varCheckLength
End If
%>

//-->
</script>

<!-- End of object/array definitions, beginning of generic functions -->

<script language="JavaScript">
<!--
function setDynaList(arrDL){

 var oList1 = document.forms[arrDL[2]].elements[arrDL[1]];
 var oList2 = document.forms[arrDL[4]].elements[arrDL[3]];
 var arrList = arrDL[5];
 
 clearDynaList(oList2);
 
 if (oList1.selectedIndex == -1){
  oList1.selectedIndex = 0;
 }

 populateDynaList(oList2, oList1[oList1.selectedIndex].value, arrList);
 return true;
}
 
function clearDynaList(oList){

 for (var i = oList.options.length; i >= 0; i--){
  oList.options[i] = null;
 }
 
 oList.selectedIndex = -1;
}
 
function populateDynaList(oList, nIndex, aArray){

 for (var i = 0; i < aArray.length; i= i + 3){
  if (aArray[i] == nIndex){
   oList.options[oList.options.length] = new Option(aArray[i + 1], aArray[i + 2]);
  }
 }

 if (oList.options.length == 0){
  oList.options[oList.options.length] = new Option("[none available]",0);
 }
 
 oList.selectedIndex = 0;
}

function MM_callJS(jsStr) { //v2.0
  return eval(jsStr)
}
//-->
</script>


</head>
<body bgcolor="#BCDDF3" leftmargin="0" topmargin="0" onLoad="MM_callJS('setDynaList(arrDL1)')">
<div align="left"></div>
<table width="548" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutTable" -->
  <tr>
    <td width="548" height="67" valign="top"><form name="form1" method="post" action="">
        <select name="select_county" id="select" onChange="setDynaList(arrDL1)">
          <%
While (NOT county_list.EOF)
%>
          <option value="<%=(county_list.Fields.Item("c_id").Value)%>"><%=(county_list.Fields.Item("county").Value)%></option>
          <%
  county_list.MoveNext()
Wend
If (county_list.CursorType > 0) Then
  county_list.MoveFirst
Else
  county_list.Requery
End If
%>
        </select>
        <br>
        <select name="select_region" id="select_region" onChange="setDynaList(arrDL1);">
          <%
While (NOT region_list.EOF)
%>
          <option value="<%=(region_list.Fields.Item("r_id").Value)%>"><%=(region_list.Fields.Item("region").Value)%></option>
          <%
  region_list.MoveNext()
Wend
If (region_list.CursorType > 0) Then
  region_list.MoveFirst
Else
  region_list.Requery
End If
%>
        </select>
        <br>
      </form></td>
  </tr>
</table>
</body>
</html>
<%
county_list.Close()
Set county_list = Nothing
%>
<%
region_list.Close()
Set region_list = Nothing
%>
<%
area_list.Close()
Set area_list = Nothing
%>





regards,
0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
Ok..i will try to work on it but it seems to be quite a revamp. I may need to rewrite the whole code in the end :) LOL...
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:shmoel
Comment Utility
0
 

Author Comment

by:Ysd
Comment Utility
I need it to be drop down list
0
 

Expert Comment

by:shmoel
Comment Utility
You can specify the size of your select like this: <select name="select" size="1">,
set it to one and it'll turn to a dropdown. Just replace "nSize" variable with "1"
0
 

Author Comment

by:Ysd
Comment Utility
yes, this is what I want.

Like I said, I'm not that good with coding, could you help me with this so that it dynamicall reads from the database.

thanks.

0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
<%@ Language = VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
strCountry = Request.Form("Country")
strRegion = Request.Form("Region")
strCity = Request.Form("City")

'set connection strings for entire application

strDataPath = server.MapPath("YOURDB.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
            + " Data Source= " & strDataPath & ";"_
            + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
       
if not IsObject("ojbConnection") then
    set objConnection=Server.CreateObject("ADODB.Connection")
    objConnection.ConnectionTimeout = 15
    objConnection.CommandTimeout = 10
    objConnection.Mode = 3 'adModeReadWrite
    if objConnection.state = 0 then
        objConnection.Open strConnectString
    end if
end if

sub makeCountry()
    if not isObject("objRS") then
        set objRS=Server.CreateObject("ADODB.RecordSet")
    end if
    if objRS.state <> 0 then
        objRS.close
    end if
    strSQL = "SELECT DISTINCT * FROM County ORDER BY County"
    objRS.Open strSQL,objConnection,3,3
    Response.Write("<option></option>" & VBCRLF )
    do while not objRS.EOF
        if objRS("County") = strCountry then
            strSelected = " Selected "
        else
            strSelected = ""
        end if
        Response.Write("<option" & strSelected & " value = "& objRS("c_ID") &">" &_
                 objRS("County") & "</option>" & VBCRLF )
        objRS.MoveNext
    loop
    objRS.Close
    set objRS=Nothing
end sub

sub makeRegion()
    if strCountry <> "" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if
        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL ="SELECT DISTINCT * FROM Region WHERE c_ID = " &_
                 strCountry & " ORDER BY Name"
        objRS.Open strSQL,objConnection,3,3

        if objRS.eof then
            Response.Write("<option>No Regions Found</option>")
        else
            Response.Write("<option>Select Region Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("Name") = strRegion then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & " value = "& objRs("r_ID") &">" &_
                         objRS("Name") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Country First</option>")
    end if
end sub

sub makeCity()
    if strRegion <> "Select a Country First" AND strRegion <> "Select Region Now" AND strRegion <>"" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if

        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL = "SELECT DISTINCT * FROM Area WHERE r_ID = " &_
                 strRegion & " ORDER BY Name"
        objRS.Open strSql,objConnection,3,3

        if objRS.eof then
            Response.Write("<option>No Cities Found</option>")
        else
            Response.Write("<option>Select City Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("Name") = strCity then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & ">" &_
                        objRS("Name") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Region First</option>")
    end if
end sub

%>

<SCRIPT LANGUAGE=javascript>

<!--

function submitCountry(){
    var objForm = document.forms[0];
    objForm.elements['Region'].selectedIndex=0;
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();

}

function submitRegion(){
    var objForm = document.forms[0];
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();

}

function submitForm(){
    var objForm = document.forms[0];
    objForm.action = ""
    return true;

}

//-->

</SCRIPT>

</HEAD>

<BODY>

<FORM action="" method=POST id=form1 name=form1 onSubmit="return submitForm()">
<SELECT name="Country" onChange="submitCountry()">
    <%call makeCountry%>
</SELECT><br>

<SELECT name="Region" onChange="submitRegion()">
    <%call makeRegion%>
</SELECT><br>

<SELECT name="City">
    <%call makeCity%>
</SELECT><br>

<p><INPUT type="submit" value="Submit" id=submit1 name=submit1></p>

</FORM>

</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>
</HTML>

0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
This is a test version

<%@ Language = VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
strCountry = Request.Form("Country")
strRegion = Request.Form("Region")
strCity = Request.Form("City")

'set connection strings for entire application

strDataPath = server.MapPath("YOURDB.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
            + " Data Source= " & strDataPath & ";"_
            + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
       
if not IsObject("ojbConnection") then
    set objConnection=Server.CreateObject("ADODB.Connection")
    objConnection.ConnectionTimeout = 15
    objConnection.CommandTimeout = 10
    objConnection.Mode = 3 'adModeReadWrite
    if objConnection.state = 0 then
        objConnection.Open strConnectString
    end if
end if

sub makeCountry()
    if not isObject("objRS") then
        set objRS=Server.CreateObject("ADODB.RecordSet")
    end if
    if objRS.state <> 0 then
        objRS.close
    end if
    strSQL = "SELECT DISTINCT * FROM County ORDER BY County"
    objRS.Open strSQL,objConnection,3,3
    Response.Write("<option></option>" & VBCRLF )
    do while not objRS.EOF
        if objRS("c_ID") = cint(strCountry) then
            strSelected = " Selected "
        else
            strSelected = ""
        end if
        Response.Write("<option" & strSelected & " value = "& objRS("c_ID") &">" &_
                 objRS("County") & "</option>" & VBCRLF )
        objRS.MoveNext
    loop
    objRS.Close
    set objRS=Nothing
end sub

sub makeRegion()
    if strCountry <> "" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if
        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL ="SELECT DISTINCT * FROM Region WHERE c_ID = " &_
                 strCountry & " ORDER BY Name"
        objRS.Open strSQL,objConnection,3,3

        if objRS.eof then
            Response.Write("<option>No Regions Found</option>")
        else
            Response.Write("<option>Select Region Now</option>" & VBCRLF )
            do while not objRS.EOF
                  if isnumeric(StrRegion) Then
                        if objRS("r_ID") = cint(strRegion) then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                  end if
                Response.Write("<option" & strSelected & " value = "& objRs("r_ID") &">" &_
                         objRS("Name") & "</option>" & VBCRLF )

                objRS.MoveNext
                        
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Country First</option>")
    end if
end sub

sub makeCity()
    if strRegion <> "Select a Country First" AND strRegion <> "Select Region Now" AND strRegion <>"" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if

        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL = "SELECT DISTINCT * FROM Area WHERE r_ID = " &_
                 strRegion & " ORDER BY Name"
        objRS.Open strSql,objConnection,3,3

        if objRS.eof then
            Response.Write("<option>No Cities Found</option>")
        else
            Response.Write("<option>Select City Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("a_ID") = strCity then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & " value = "& objRS("a_ID")&">" &_
                        objRS("Name") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Region First</option>")
    end if
end sub

%>

<SCRIPT LANGUAGE=javascript>

<!--

function submitCountry(){
    var objForm = document.forms[0];
    objForm.elements['Region'].selectedIndex=0;
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();

}

function submitRegion(){
    var objForm = document.forms[0];
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();

}

function submitForm(){
    var objForm = document.forms[0];
    objForm.action = ""
    return true;

}

//-->

</SCRIPT>

</HEAD>

<BODY>

<FORM action="" method=POST id=form1 name=form1 onSubmit="return submitForm()">
<SELECT name="Country" onChange="submitCountry()">
    <%call makeCountry%>
</SELECT><br>

<SELECT name="Region" onChange="submitRegion()">
    <%call makeRegion%>
</SELECT><br>

<SELECT name="City">
    <%call makeCity%>
</SELECT><br>

<p><INPUT type="submit" value="Submit" id=submit1 name=submit1></p>

</FORM>

</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>
</HTML>

0
 

Author Comment

by:Ysd
Comment Utility
leechoonhwee.

Thanks for the code it works fine, but I realised when I added your code into my main page it submitted the page which was actioned to move to the next page.
My main page contains of many text fields, if the drop down list is selected it moves onto the next page without allowing the user to input details on the rest of the text fields.

I appreciate you hard effort and have increased the points.

I found code that works how I want it to work, but its not reading from the database.  below is the code:



<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/instant_exchange.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

  MM_editConnection = MM_instant_exchange_STRING
  MM_editTable = "customer_detail"
  MM_editRedirectUrl = "input_bank_details.asp"
  MM_fieldsStr  = "uk_ad1|value|uk_ad2|value|uk_county|value|uk_region|value|uk_area|value|uk_pc|value|uk_home_tel|value|uk_home_fax|value|uk_home_mob1|value|uk_home_mob2|value|uk_home_email|value|uk_work_tel|value|uk_work_email|value|uk_occupation|value|os_town_add|value|os_town_po|value|os_ps_region|value|os_town_district|value|os_town_HTel|value|os_town_mob|value|os_vlg_village|value|os_vlg_po|value|os_vlg_ps|value|os_vlg_district|value|os_vlg_HTel|value|os_vlg_mob|value|old_ac_no|value|title|value|fname|value|surname|value|mname|value|aka|value|sex|value|origin_country|value"
  MM_columnsStr = "uk_ad1|',none,''|uk_ad2|',none,''|uk_county|',none,''|uk_region|',none,''|uk_area|',none,''|uk_pc|',none,''|uk_home_tel|none,none,NULL|uk_home_fax|none,none,NULL|uk_home_mob1|none,none,NULL|uk_home_mob2|none,none,NULL|uk_email|',none,''|uk_work_tel|none,none,NULL|uk_work_email|',none,''|uk_occupation|',none,''|os_town_add|',none,''|os_town_po|',none,''|os_ps_region|',none,''|os_town_district|',none,''|os_town_HTel|none,none,NULL|os_town_mob|none,none,NULL|os_vlg_village|',none,''|os_vlg_po|',none,''|os_vlg_ps|',none,''|os_vlg_district|',none,''|os_vlg_HTel|none,none,NULL|os_vlg_mob|none,none,NULL|old_ac_no|',none,''|title|',none,''|fname|',none,''|sname|',none,''|mname|',none,''|aka|',none,''|sex|',none,''|origin_country|',none,''"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
 
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_tableValues = MM_tableValues & ","
      MM_dbValues = MM_dbValues & ","
    End If
    MM_tableValues = MM_tableValues & MM_columns(MM_i)
    MM_dbValues = MM_dbValues & MM_formVal
  Next
  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

  If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
<%
Dim county
Dim county_numRows

Set county = Server.CreateObject("ADODB.Recordset")
county.ActiveConnection = MM_instant_exchange_STRING
county.Source = "SELECT * FROM county"
county.CursorType = 0
county.CursorLocation = 2
county.LockType = 1
county.Open()

county_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Input Customer Details</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="../css.css" type="text/css">
<SCRIPT LANGUAGE="JavaScript">
<!--
<!--


<!-- Begin
var arrItems1 = new Array();
var arrItemsGrp1 = new Array();


arrItems1[3] = "Cardiff";
arrItemsGrp1[3] = 1;
arrItems1[4] = "Newport";
arrItemsGrp1[4] = 1;
arrItems1[5] = "Swindon";
arrItemsGrp1[5] = 1;
arrItems1[6] = "Caerphilly";
arrItemsGrp1[6] = 1;
arrItems1[7] = "Bristol";
arrItemsGrp1[7] = 1;
arrItems1[8] = "Rhondda";
arrItemsGrp1[8] = 1;
arrItems1[9] = "Merthyr Tydfil";
arrItemsGrp1[9] = 1;
arrItems1[10] = "Port Talbot";
arrItemsGrp1[10] = 1;
arrItems1[11] = "Bridgend";
arrItemsGrp1[11] = 1;
arrItems1[12] = "Pontyprid";
arrItemsGrp1[12] = 1;
arrItems1[13] = "Rhondda";
arrItemsGrp1[13] = 1;


arrItems1[101] = "Birmingham";
arrItemsGrp1[101] = 2;
arrItems1[102] = "Manchester";
arrItemsGrp1[102] = 2;
arrItems1[103] = "London";
arrItemsGrp1[103] = 2;
arrItems1[104] = "Swindon";
arrItemsGrp1[104] = 2;
arrItems1[105] = "Bradford";
arrItemsGrp1[105] = 2;
arrItems1[106] = "Luton";
arrItemsGrp1[106] = 2;
arrItems1[107] = "Reading";
arrItemsGrp1[107] = 2;
arrItems1[108] = "Watford";
arrItemsGrp1[108] = 2;

arrItems1[109] = "Glasgow";
arrItemsGrp1[109] = 3;
arrItems1[110] = "Edinburg";
arrItemsGrp1[110] = 3;
arrItems1[111] = "Aberdeen";
arrItemsGrp1[111] = 3;
arrItems1[112] = "Newcastle Upon Tyne";
arrItemsGrp1[112] = 3;

arrItems1[113] = "Dublin";
arrItemsGrp1[113] = 4;
arrItems1[114] = "Cork";
arrItemsGrp1[114] = 4;
arrItems1[115] = "Longford";
arrItemsGrp1[115] = 4;
arrItems1[116] = "Monaghan";
arrItemsGrp1[116] = 4;



var arrItems2 = new Array();
var arrItemsGrp2 = new Array();

arrItems2[201] = "Canton";
arrItemsGrp2[201] = 3
arrItems2[202] = "Riverside";
arrItemsGrp2[202] = 3
arrItems2[203] = "Grangetown";
arrItemsGrp2[203] = 3
arrItems2[204] = "Cardiff Bay";
arrItemsGrp2[204] = 3
arrItems2[205] = "Roath";
arrItemsGrp2[205] = 3
arrItems2[206] = "Cathays";
arrItemsGrp2[206] = 3
arrItems2[207] = "Splott";
arrItemsGrp2[207] = 3
arrItems2[208] = "Ely";
arrItemsGrp2[208] = 3
arrItems2[209] = "St Mellons";
arrItemsGrp2[209] = 3
arrItems2[210] = "Whitchurch";
arrItemsGrp2[210] = 3

arrItems2[222] = "Caerleon";
arrItemsGrp2[222] = 4
arrItems2[333] = "Marshfield";
arrItemsGrp2[333] = 4

arrItems2[444] = "Bettws";
arrItemsGrp2[444] = 4
arrItems2[555] = "Cwmbran";
arrItemsGrp2[555] = 4

arrItems2[300] = "Milton";
arrItemsGrp2[300] = 4
arrItems2[301] = "Kilo Class";
arrItemsGrp2[301] = 4
arrItems2[303] = "Risca";
arrItemsGrp2[303] = 4

function selectChange(control, controlToPopulate, ItemArray, GroupArray)
{
  var myEle ;
  var x ;
  // Empty the second drop down box of any choices
  for (var q=controlToPopulate.options.length;q>=0;q--) controlToPopulate.options[q]=null;
  if (control.name == "firstChoice") {
    // Empty the third drop down box of any choices
    for (var q=form1.thirdChoice.options.length;q>=0;q--) form1.thirdChoice.options[q] = null;
 }
  // ADD Default Choice - in case there are no values
  myEle = document.createElement("option") ;
  myEle.value = 0 ;
  myEle.text = "Select Area" ;
  controlToPopulate.add(myEle) ;
  // Now loop through the array of individual items
  // Any containing the same child id are added to
  // the second dropdown box
  for ( x = 0 ; x < ItemArray.length  ; x++ )
    {
      if ( GroupArray[x] == control.value )
        {
          myEle = document.createElement("option") ;
          myEle.value = x ;
          myEle.text = ItemArray[x] ;
          controlToPopulate.add(myEle) ;
        }
    }
}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function YY_checkform() { //v4.66
//copyright (c)1998,2002 Yaromat.com
  var args = YY_checkform.arguments; var myDot=true; var myV=''; var myErr='';var addErr=false;var myReq;
  for (var i=1; i<args.length;i=i+4){
    if (args[i+1].charAt(0)=='#'){myReq=true; args[i+1]=args[i+1].substring(1);}else{myReq=false}
    var myObj = MM_findObj(args[i].replace(/\[\d+\]/ig,""));
    myV=myObj.value;
    if (myObj.type=='text'||myObj.type=='password'||myObj.type=='hidden'){
      if (myReq&&myObj.value.length==0){addErr=true}
      if ((myV.length>0)&&(args[i+2]==1)){ //fromto
        var myMa=args[i+1].split('_');if(isNaN(myV)||myV<myMa[0]/1||myV > myMa[1]/1){addErr=true}
      } else if ((myV.length>0)&&(args[i+2]==2)){
          var rx=new RegExp("^[\\w\.=-]+@[\\w\\.-]+\\.[a-z]{2,4}$");if(!rx.test(myV))addErr=true;
      } else if ((myV.length>0)&&(args[i+2]==3)){ // date
        var myMa=args[i+1].split("#"); var myAt=myV.match(myMa[0]);
        if(myAt){
          var myD=(myAt[myMa[1]])?myAt[myMa[1]]:1; var myM=myAt[myMa[2]]-1; var myY=myAt[myMa[3]];
          var myDate=new Date(myY,myM,myD);
          if(myDate.getFullYear()!=myY||myDate.getDate()!=myD||myDate.getMonth()!=myM){addErr=true};
        }else{addErr=true}
      } else if ((myV.length>0)&&(args[i+2]==4)){ // time
        var myMa=args[i+1].split("#"); var myAt=myV.match(myMa[0]);if(!myAt){addErr=true}
      } else if (myV.length>0&&args[i+2]==5){ // check this 2
            var myObj1 = MM_findObj(args[i+1].replace(/\[\d+\]/ig,""));
            if(myObj1.length)myObj1=myObj1[args[i+1].replace(/(.*\[)|(\].*)/ig,"")];
            if(!myObj1.checked){addErr=true}
      } else if (myV.length>0&&args[i+2]==6){ // the same
            var myObj1 = MM_findObj(args[i+1]);
            if(myV!=myObj1.value){addErr=true}
      }
    } else
    if (!myObj.type&&myObj.length>0&&myObj[0].type=='radio'){
          var myTest = args[i].match(/(.*)\[(\d+)\].*/i);
          var myObj1=(myObj.length>1)?myObj[myTest[2]]:myObj;
      if (args[i+2]==1&&myObj1&&myObj1.checked&&MM_findObj(args[i+1]).value.length/1==0){addErr=true}
      if (args[i+2]==2){
        var myDot=false;
        for(var j=0;j<myObj.length;j++){myDot=myDot||myObj[j].checked}
        if(!myDot){myErr+='* ' +args[i+3]+'\n'}
      }
    } else if (myObj.type=='checkbox'){
      if(args[i+2]==1&&myObj.checked==false){addErr=true}
      if(args[i+2]==2&&myObj.checked&&MM_findObj(args[i+1]).value.length/1==0){addErr=true}
    } else if (myObj.type=='select-one'||myObj.type=='select-multiple'){
      if(args[i+2]==1&&myObj.selectedIndex/1==0){addErr=true}
    }else if (myObj.type=='textarea'){
      if(myV.length<args[i+1]){addErr=true}
    }
    if (addErr){myErr+='* '+args[i+3]+'\n'; addErr=false}
  }
  if (myErr!=''){alert('The required information is incomplete or contains errors:\t\t\t\t\t\n\n'+myErr)}
  document.MM_returnValue = (myErr=='');
}
//-->
</script>
</head>
<body bgcolor="#BCDDF3" leftmargin="23" topmargin="0">
<div align="center">
  <form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1">
    <table width="152" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr>
        <td width="152" height="9"></td>
      </tr>
      <tr>
        <td height="20" valign="middle"> <select id=firstChoice name=uk_county class="small" onChange="selectChange(this, form1.secondChoice, arrItems1, arrItemsGrp1);" >
            <%
While (NOT county.EOF)
%>
            <option value="<%=(county.Fields.Item("c_id").Value)%>"><%=(county.Fields.Item("county").Value)%></option>
            <%
  county.MoveNext()
Wend
If (county.CursorType > 0) Then
  county.MoveFirst
Else
  county.Requery
End If
%>
          </select> </td>
      </tr>
      <tr>
        <td height="20" valign="middle"> <select id=secondChoice name=uk_region onChange="selectChange(this, form1.thirdChoice, arrItems2, arrItemsGrp2);" class="small">
          </select> </td>
      </tr>
      <tr>
        <td height="20" valign="middle"> <select id=thirdChoice name=uk_area class="small">
          </select> </td>
      </tr>
    </table>
    <input type="hidden" name="MM_insert" value="form1">
  </form>
</div>
</body>
</html>
<%
county.Close()
Set county = Nothing
%>
0
 
LVL 9

Expert Comment

by:leechoonhwee
Comment Utility
Strange....it shouldn wander away to other page unless you put it into another form.

This function

function submitForm(){
    var objForm = document.forms[0];
    objForm.action = ""
    return true;

}

handles the onsubmit action which by default means submit to itself. Probably you need to check out how many forms are there in your page and did they resubmit to somewhere else??
0
 
LVL 46

Expert Comment

by:fritz_the_blank
Comment Utility
Well, that code looks very, very familiar!!!

Here is a link that explains how I wrote this:

http://www.fairfieldconsulting.com/asp_multipleSelects.asp

Fritz the Blank
0
 

Expert Comment

by:heyme
Comment Utility
leechoonhwee,

this is the code I'm using below, when the first drop down is selected, it gets submitted to the next page (add/input_bank_details.asp)




<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/instant_exchange.asp" -->
<br>
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

  MM_editConnection = MM_instant_exchange_STRING
  MM_editTable = "customer_detail"
  MM_editRedirectUrl = "add/input_bank_details.asp"
  MM_fieldsStr  = "county|value|Region|value|area|value|name|value"
  MM_columnsStr = "uk_county|',none,''|uk_region|',none,''|uk_area|',none,''|fname|',none,''"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
 
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_tableValues = MM_tableValues & ","
      MM_dbValues = MM_dbValues & ","
    End If
    MM_tableValues = MM_tableValues & MM_columns(MM_i)
    MM_dbValues = MM_dbValues & MM_formVal
  Next
  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

  If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
dim  strDataPath, strConnectString, objConnection, strcounty, strRegion, strarea, objRS, strSelected

strcounty = Request.Form("county")
strRegion = Request.Form("Region")
strarea = Request.Form("area")


'set connection strings for entire application
strDataPath = server.MapPath("db/instant_exchange.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"_
                  + " Data Source= " & strDataPath & ";"_
                  + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
                  
if not IsObject("ojbConnection") then
      set objConnection=Server.CreateObject("ADODB.Connection")
      objConnection.ConnectionTimeout = 15
      objConnection.CommandTimeout =  10
      objConnection.Mode = 3 'adModeReadWrite
      if objConnection.state = 0 then
            objConnection.Open strConnectString
      end if
end if

sub makecounty()
      if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
      end if
      if objRS.state <> 0 then
            objRS.close
      end if
      objRS.Open "SELECT DISTINCT County  FROM address ORDER BY county",objConnection,3,3
      Response.Write("<option></option>" & VBCRLF )
      do while not objRS.EOF
            if objRS("county") = strcounty then
                  strSelected = " Selected "
            else
                  strSelected = ""
            end if
            Response.Write("<option" & strSelected & ">" & objRS("county") & "</option>" & VBCRLF )
            objRS.MoveNext
      loop
      objRS.Close
      set objRS=Nothing
end sub

sub makeRegion()
      if strcounty <> "" then
            if not isObject("objRS") then
                  set objRS=Server.CreateObject("ADODB.RecordSet")
            end if
            if objRS.state <> 0 then
                  objRS.close
            end if
            objRS.Open "SELECT DISTINCT Region FROM address WHERE county = '" & strcounty & "' ORDER BY Region",objConnection,3,3
            if objRS.eof then
                  Response.Write("<option>No Regions Found</option>")
            else
                  Response.Write("<option>Select Region Now</option>" & VBCRLF )
                  do while not objRS.EOF
                        if objRS("Region") = strRegion then
                              strSelected = " Selected "
                        else
                              strSelected = ""
                        end if
                        Response.Write("<option" & strSelected & ">" & objRS("Region") & "</option>" & VBCRLF )
                        objRS.MoveNext
                  loop
            end if
            objRS.Close
            set objRS=Nothing
      else
            Response.Write("<option>Select a county First</option>")
      end if
end sub

sub makearea()
      if strRegion <> "Select a county First" AND  strRegion <> "Select Region Now" AND strRegion <>"" then
            if not isObject("objRS") then
                  set objRS=Server.CreateObject("ADODB.RecordSet")
            end if
            if objRS.state <> 0 then
                  objRS.close
            end if
            objRS.Open "SELECT DISTINCT area FROM address WHERE Region = '" & strRegion & "' ORDER BY area",objConnection,3,3
            if objRS.eof then
                  Response.Write("<option>No Cities Found</option>")
            else
                  Response.Write("<option>Select area Now</option>" & VBCRLF )
                  do while not objRS.EOF
                        if objRS("area") = strarea then
                              strSelected = " Selected "
                        else
                              strSelected = ""
                        end if
                        Response.Write("<option" & strSelected & ">" & objRS("area") & "</option>" & VBCRLF )
                        objRS.MoveNext
                  loop
            end if
            objRS.Close
            set objRS=Nothing
      else
            Response.Write("<option>Select a Region First</option>")
      end if
end sub
%>

<SCRIPT LANGUAGE=javascript>
<!--

function submitcounty(){
      var objForm = document.forms[0];
      objForm.elements['Region'].selectedIndex=0;
      objForm.elements['area'].selectedIndex = 0;
      objForm.submit();
}
function submitRegion(){
      var objForm = document.forms[0];
      objForm.elements['area'].selectedIndex = 0;
      objForm.submit();
}

//-->
</SCRIPT>

</HEAD>
<BODY>
<FORM action="<%=MM_editAction%>" method=POST id=form1 name=form1 onSubmit="">
 
  <p>
    <SELECT  name="county" onChange="submitcounty()">
      <%call  makecounty%>
    </SELECT>
    <br>
    <SELECT  name="Region" onChange="submitRegion()">
      <%call makeRegion%>
    </SELECT>
    <br>
    <SELECT  name="area">
      <%call makearea%>
    </SELECT>
    <br>
    <input name="name" type="text" id="name" value="<%=Request.Form("name")%>">
  </p>
  <p>
<INPUT type="submit" value="Submit" id=submit1 name=submit1></p>

  <input type="hidden" name="MM_insert" value="form1">
</FORM>
</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>

</HTML>
0
 
LVL 9

Accepted Solution

by:
leechoonhwee earned 175 total points
Comment Utility
<%@ Language = VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
<%
strCountry = Request.Form("Country")
strRegion = Request.Form("Region")
strCity = Request.Form("City")
StrName = Request.Form("Name")

'set connection strings for entire application

strDataPath = server.MapPath("YOURDB.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
            + " Data Source= " & strDataPath & ";"_
            + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
       
if not IsObject("ojbConnection") then
    set objConnection=Server.CreateObject("ADODB.Connection")
    objConnection.ConnectionTimeout = 15
    objConnection.CommandTimeout = 10
    objConnection.Mode = 3 'adModeReadWrite
    if objConnection.state = 0 then
        objConnection.Open strConnectString
    end if
end if

If Request("Submit1")="Submit" Then 'THIS IS THE CODE TO INSERT THE FIELDS INTO DB
Sql = "Insert into table (county,Region,area,name) values ('"& StrCounty &"','"& StrRegion &"','"& StrCity &"','"& StrName &"')"
objConnection.Execute(Sql)
Response.redirect "SOmeasp.asp" 'You should redirect to somewhere after processing.
End if

sub makeCountry()
    if not isObject("objRS") then
        set objRS=Server.CreateObject("ADODB.RecordSet")
    end if
    if objRS.state <> 0 then
        objRS.close
    end if
    strSQL = "SELECT DISTINCT * FROM County ORDER BY County"
    objRS.Open strSQL,objConnection,3,3
    Response.Write("<option></option>" & VBCRLF )
    do while not objRS.EOF
        if objRS("c_ID") = cint(strCountry) then
            strSelected = " Selected "
        else
            strSelected = ""
        end if
        Response.Write("<option" & strSelected & " value = "& objRS("c_ID") &">" &_
                 objRS("County") & "</option>" & VBCRLF )
        objRS.MoveNext
    loop
    objRS.Close
    set objRS=Nothing
end sub

sub makeRegion()
    if strCountry <> "" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if
        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL ="SELECT DISTINCT * FROM Region WHERE c_ID = " &_
                 strCountry & " ORDER BY Name"
        objRS.Open strSQL,objConnection,3,3

        if objRS.eof then
            Response.Write("<option>No Regions Found</option>")
        else
            Response.Write("<option>Select Region Now</option>" & VBCRLF )
            do while not objRS.EOF
               if isnumeric(StrRegion) Then
                    if objRS("r_ID") = cint(strRegion) then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
               end if
                Response.Write("<option" & strSelected & " value = "& objRs("r_ID") &">" &_
                         objRS("Name") & "</option>" & VBCRLF )

                objRS.MoveNext
                   
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Country First</option>")
    end if
end sub

sub makeCity()
    if strRegion <> "Select a Country First" AND strRegion <> "Select Region Now" AND strRegion <>"" then
        if not isObject("objRS") then
            set objRS=Server.CreateObject("ADODB.RecordSet")
        end if

        if objRS.state <> 0 then
            objRS.close
        end if
        strSQL = "SELECT DISTINCT * FROM Area WHERE r_ID = " &_
                 strRegion & " ORDER BY Name"
        objRS.Open strSql,objConnection,3,3

        if objRS.eof then
            Response.Write("<option>No Cities Found</option>")
        else
            Response.Write("<option>Select City Now</option>" & VBCRLF )
            do while not objRS.EOF
                if objRS("a_ID") = strCity then
                    strSelected = " Selected "
                else
                    strSelected = ""
                end if
                Response.Write("<option" & strSelected & " value = "& objRS("a_ID")&">" &_
                        objRS("Name") & "</option>" & VBCRLF )
                objRS.MoveNext
            loop
        end if
        objRS.Close
        set objRS=Nothing
    else
        Response.Write("<option>Select a Region First</option>")
    end if
end sub

%>

<SCRIPT LANGUAGE=javascript>

<!--

function submitCountry(){
    var objForm = document.forms[0];
    objForm.elements['Region'].selectedIndex=0;
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();

}

function submitRegion(){
    var objForm = document.forms[0];
    objForm.elements['City'].selectedIndex = 0;
    objForm.submit();

}

function submitForm(){
    var objForm = document.forms[0];
    objForm.action = ""
    return true;

}

//-->

</SCRIPT>

</HEAD>

<BODY>

<FORM action="" method=POST id=form1 name=form1 onSubmit="return submitForm()">
<SELECT name="Country" onChange="submitCountry()">
    <%call makeCountry%>
</SELECT><br>

<SELECT name="Region" onChange="submitRegion()">
    <%call makeRegion%>
</SELECT><br>

<SELECT name="City">
    <%call makeCity%>
</SELECT><br>
<input name="name" type="text" id="name" value="<%=Request.Form("name")%>">
<p><INPUT type="submit" value="Submit" id=submit1 name=submit1></p>

</FORM>

</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>
</HTML>
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now