Populate Child Dropdown without Page Refresh / Page Return From Database

[Arielle]

I've argued with this for quite some time and I've seen a number of other people on various other sites asking for this same thing with no answer so I figured that now that I have solved it I would share it. It could probably use some tweaking but anyway feel free to use this code as you see fit.

The idea is that you have a dropdown, and dependent on what they pick on the first dropdown will determine what they get in the second one. Now the kicker I had is that both are populated by the database as well.. so here's what I've got.

The following assumptions are made.
The two tables are related in some fashion, they have an ID that can be associated with one another. For the sake of this example I'm calling it "MyID"

Also Running on the assumption that you already have a connection string and Conn is your connection.

Only tested to work in IE. If you're using Firefox you need to add a try/catch on the add element and add a different method of adding the element to the container.

Code goes before the BODY tag

<%Dim sql,rsFirst,rsSecond,x
Set rsFirst = Server.Createobject("ADODB.recordset")
sql = "SELECT * FROM FirstTable"
rsFirst.Open sql,Conn
 
Set rsSecond = Server.Createobject("ADODB.recordset")
sql = "SELECT * FROM SecondTable"
rsSecond.Open sql,Conn
 
'Write to page a script to declare a javascript array to hold your data.
x=0
%>
<%="<script language=javascript>" & vbCrLf%>
<%="var myArray = new Array()" & vbCrLf%>
<%do until rsSecond.eof%>
<%="myArray[" + x + "] = '" + rsSecond("MyID") + ";" + rsSecond("LabelField") + ";" + rsSecond("LastField") + "';"%>
<%x=x+1%>
<%rsSecond.movenext%>
<%loop%>
<%="</script>"%>
 
<script language="javascript">
 function setField(setField,setID)
{
  var container = document.getElementById(setField);
  container.length = 0;
  var tempLine;
 
  for (var x=0;x<myArray.length;x++)
  {
    tempLine = myArray[x].split(";");
    if (tempLine[0] == setID)
    {
      var newOpt document.createElement('option');
      newOpt.text = tempLine[1];    //LabelField
      newOpt.value = tempLine[0];   //MyID
      container.add(newOpt);
    }
  }
}
</script>
 

HTML Form

<label>Select First:</label>
<select id="parentField" name="parentField" onChange="setField('childField',this.value)">
  <%do until rsFirst.eof%>
    <option value="<%=rsFirst("MyID")%>"><%=rsFirst("FirstLabel")%></option>
  <%rsFirst.movenext%>
  <%loop%>
</select>
 
<label>Select Second:</label>
<select id="childField" name="childField">
</select>
 

[rarebond]

Try using AJAX for this.

[puafo]

I am trying your code for "Populate Child Dropdown without Page Refresh / Page Return From Database"

I am hopeless at Javascript, and I have changed your script to the following and get an error...

<%
Set rsFirst = Server.Createobject("ADODB.recordset") 
sql = "SELECT categoryID, categoryActive, categoryTitle FROM category" 
rsFirst.Open sql,adoCon 
 
Set rsSecond = Server.Createobject("ADODB.recordset") 
sql = "SELECT categorySubID, categorySubActive, categorySubTitle, categorySubCategory FROM categorySub" 
rsSecond.Open sql,adoCon 
 
x = 0
 
%>

Head

<script language=javascript> 
var myArray = new Array() 
<% do until rsSecond.eof %> 
<%="myArray[" + x + "] = '" + rsSecond("categorySubID") + ";" + rsSecond("categorySubTitle") + ";" + rsSecond("categorySubCategory") + "';"%> 
<% x=x+1 %> 
<% rsSecond.movenext
loop %> 
</script>
<script language="javascript"> 
 function setField(setField,setID) 
{ 
  var container = document.getElementById(setField); 
  container.length = 0; 
  var tempLine; 
 
  for (var x=0;x<myArray.length;x++) 
  { 
    tempLine = myArray[x].split(";"); 
    if (tempLine[0] == setID) 
    { 
      var newOpt document.createElement('option'); 
      newOpt.text = tempLine[1];    //LabelField 
      newOpt.value = tempLine[0];   //MyID 
      container.add(newOpt); 
    } 
  } 
} 
</script>

Body

<label>Select First:</label> 
<select id="parentField" name="parentField" onChange="setField('childField',this.value)"> 
<% do until rsFirst.eof %> 
<option value="<%=rsFirst("categoryID")%>"><%=rsFirst("categoryTitle")%></option> 
<% rsFirst.movenext
loop %> 
</select> 
<label>Select Second:</label> 
<select id="childField" name="childField"> 
</select>

the error is on line 21

<%="myArray[" + x + "] = '" + rsSecond("categorySubID") + ";" + rsSecond("categorySubTitle") + ";" + rsSecond("categorySubCategory") + "';"%>

the error is

Type mismatch '[string: "myarray["]

any help would be greatly appreciated. thanks in advance.