Dependant Drop Down Boxes

[bairdb]

I have put together the code below to create dependant dropdown boxes. Can anyone help me with some javascript so the page works the same but doesn't refresh every time a dropdown box changes? I am relatively new to javascript so the more spefic the better. ThanksBairdb

<%set object=Server.CreateObject("ADODB.recordset")sql="SELECT DISTINCT REGN_NM FROM 2005_SSP ORDER BY REGN_NM"object.Open sql,connREGN_NM=Trim(request("regn_nm"))%><a href="Test.asp">Refresh</a><form method="get" action="Test.asp">Region  <select name="regn_nm" onchange="submit();"><% do until object.EOF   response.Write("<option")   If object.fields("regn_nm") = regn_nm then   response.write(" selected")   end if   response.Write(">")   response.Write(object.fields("Regn_NM"))   object.Movenextloopobject.Closeset object=Nothing%>    </select><%If regn_nm<>"" thensql="SELECT DISTINCT SALES_ST_NM FROM 2005_SSP WHERE REGN_NM='"& regn_nm &"' ORDER BY SALES_ST_NM"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,connSALES_ST_NM=Trim(request("sales_st_nm"))%>Sales St<select name="sales_st_nm" onchange="submit();"><% do until object.EOF   response.Write("<option")   If object.fields("sales_st_nm") = sales_st_nm then   response.write(" selected")   end if   response.Write(">")   response.Write(object.fields("sales_st_nm"))   object.Movenextloopobject.Closeset object=Nothing%></select><%End If%><%If sales_st_nm<>"" thensql="SELECT DISTINCT DST_CD FROM 2005_SSP WHERE SALES_ST_NM='"& sales_st_nm &"' ORDER BY DST_CD"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,connDST_CD=Trim(request("dst_cd"))%>Distict <select name="dst_cd" onchange="submit();"><% do until object.EOF   response.Write("<option")   If object.fields("dst_cd") = dst_cd then   response.write(" selected")   end if   response.Write(">")   response.Write(object.fields("dst_cd"))   object.Movenextloopobject.Closeset object=Nothing%></select><%End If%></form><%If dst_cd<>"" thensql="TRANSFORM Sum([2005_SSP].PREMIUM_TOTAL) AS SumOfPREMIUM_TOTAL SELECT [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD, [2005_SSP].LGCY_LINE_TYP_DESC FROM 2005_SSP WHERE Dst_Cd='"& dst_cd &"' GROUP BY   [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD, [2005_SSP].LGCY_LINE_TYP_DESC PIVOT [2005_SSP].YR_MO;"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,conn%><table width="100%"><tr><th>Region</th><th>Sales State</th><th>District</th><th>Line</th><th>Jan</th><th>Feb</th><th>Mar</td><th>Apr</td><th>May</td><th>Jun</td><th>Jul</td><th>Aug</td><th>Sep</td></tr><%do until object.EOF%><tr><%for each x in object.Fields%><td><%Response.Write Ucase(x.value)%></td><%nextobject.MoveNext%></tr><%loopobject.close%></table>

[Jerome]

Hi,I don't get what you want... If you want dependant dropdown boxes, you need to refresh your page using the javascript submit function... Just explain a little more. Thanks.

[bairdb]

The problem I am having is, when you select the first dropdown box and the second dropdown populates from depending first selection, the first dropdown doesn't keep the selection that was made it goes back to the first option in the dropdown. I am looking for a way to keep each selection that is made.

[sorex]

The problem I am having is, when you select the first dropdown box and the second dropdown populates from depending first selection, the first dropdown doesn't keep the selection that was made it goes back to the first option in the dropdown.  I am looking for a way to keep each selection that is made.

<{POST_SNAPBACK}>

that's because you didn't supply the value to the option so the result will always be nothing/empty, try the fix below<% do until object.EOF  response.Write("<option value='"&object.fields("regn_nm")&"'")  If object.fields("regn_nm") = regn_nm then  response.write(" selected")  end if  response.Write(">")  response.Write(object.fields("Regn_NM"))  object.Movenextloopobject.Closeset object=Nothing%>

[bairdb]

I tryed that fix but it didn't keep the first selection after the page refreshed.

[sorex]

What does it spit out as html? is there something SELECTED or nothing at all?and are the values numbers or text?

[bairdb]

<body><form method="get" action="Test.asp">Region  <select name="regn_nm" onchange="submit();"><option>GREAT LAKES REGION   <option>MIDLAND REGION       <option>MOUNTAIN REGION      <option>NORTHWEST REGION     <option>VALLEY REGION            </select>Sales St<select name="sales_st_nm" onchange="submit();"><option>ARIZONA NORTH        <option>ARIZONA SOUTH        <option>MIDLAND CENTRAL      <option>MIDLAND EAST         <option>MIDLAND NORTH        <option>MIDLAND WEST         </select></form></body></html>

This is what the html looks like after I make a selection. the first dropdown value starts as Great Lakes Region when a page loads. If I select an option in the Region box it will create the second dropdown with the Sales States that correspond with the region that was selected, but after the Sales States dropdown loads, the region dropdown value goes back to Great Lakes Region even if I selected something different. I just want the Region dropdown to keep the value I selected after the Sales State dropdown loads.

[sorex]

I don't have your database but adapted your code with data coming from an array and here it works fine.<%regions=array("reg1","reg2","reg3","reg4","reg5","reg6","reg7")REGN_NM=Trim(request("regn_nm"))%><form method="get">Region <select name="regn_nm" onchange="submit();"><% for x=0 to ubound(regions) response.Write("<option") If regions(x) = regn_nm then response.write(" selected") end if response.Write(">") response.Write(regions(x))next%></select>

[bairdb]

Is there anyway populate the array with values from a database?

[bairdb]

Nevermind, I figured it out.

[bairdb]

I am having a problem though with the dependancy between the two drop down boxes. When I select the first option every option from the Sales State box loads not just the ones that correspond to the first selection.

[sorex]

well, you need to adapt your query altho that thise one looks rightsql="SELECT DISTINCT SALES_ST_NM FROM 2005_SSP WHERE REGN_NM='"& regn_nm &"' ORDER BY SALES_ST_NM"replace the regn_nm with a valid value and see what happends.

[bairdb]

I have a little bit of a different question about this now. For each time that a drop down box gets submitted I am creating a table with the results from the selection along with the dependant dropdown box. I have at the moment three dropdown boxes that are dependant on each other and all of them also create a table with the results from the selection. The problem I am having is, each time a table is created the headers from the previous table remains so I end up having three sets of headers on one table by the time I get to the third dropdown box and I only want the most current table and it's header. It looks like anything before do until object.EOF gets left on the screen after the page refreshes but everything after does not.

<%set object=Server.CreateObject("ADODB.recordset")sql="SELECT DISTINCT REGN_NM FROM 2005_SSP ORDER BY REGN_NM"object.Open sql,connREGN_NM=request.Form("regn_nm")%><a href="Test.asp">Refresh</a><form method="post"><select name="regn_nm" onchange="submit();"><option>Select a Region</option><% do until object.EOF   response.Write("<option")   If object.fields("regn_nm") = regn_nm then   response.write(" selected")   end if   response.Write(">")   response.Write(object.fields("regn_nm"))   object.Movenextloopset object=Nothing%></select></select><%If regn_nm<>"" thensql="SELECT DISTINCT SALES_ST_NM FROM 2005_SSP WHERE REGN_NM='"& regn_nm &"' ORDER BY SALES_ST_NM"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,connsales_st_nm=Trim(request("sales_st_nm"))%><select name="sales_st_nm" onchange="submit()"><option>Select a Sales State</option><% do until object.EOF   response.Write("<option")   If object.fields("sales_st_nm") = sales_st_nm then   response.write(" selected")   end if   response.Write(">")   response.Write(object.fields("sales_st_nm"))   object.Movenextloopset object=Nothing%></select><%END IF%><%If sales_st_nm<>"" thensql="SELECT DISTINCT DST_CD FROM 2005_SSP WHERE SALES_ST_NM='"& sales_st_nm &"' ORDER BY DST_CD"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,connDST_CD=request.Form("dst_cd")%><select name="dst_cd" onchange="submit();"><option>Choose a District</option><% do until object.EOF   response.Write("<option")   If object.fields("dst_cd") = dst_cd then   response.write(" selected")   end if   response.Write(">")   response.Write(object.fields("dst_cd"))   object.Movenextloopset object=Nothing%></select><%If dst_cd<>"" thensql="TRANSFORM Sum([2005_SSP].PREMIUM_TOTAL) AS SumOfPREMIUM_TOTAL SELECT [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD FROM 2005_SSP WHERE Dst_Cd='"& dst_cd &"' GROUP BY   [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD PIVOT [2005_SSP].YR_MO;"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,conn%><table border="1" width="100%"> <tr>	<%for each x in object.Fields  response.write("<th>" & x.name & "</th>")	next%>	</tr><%do until object.EOF%> 	 <tr>	<%for each x in object.Fields%>  <td id="TD"><%Response.Write(x.value)%></td>	<%next	object.MoveNext%>	</tr>	<%loop	object.close	%></table><%  end if %><%End IF%><%If sales_st_nm<>"" thensql="TRANSFORM Sum([2005_SSP].PREMIUM_TOTAL) AS SumOfPREMIUM_TOTAL SELECT [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD FROM 2005_SSP WHERE Sales_st_nm='"& Sales_st_nm &"' GROUP BY   [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD PIVOT [2005_SSP].YR_MO;"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,conn%><table border="1" width="100%"> <tr>	<%for each x in object.Fields  response.write("<th>" & x.name & "</th>")	next%>	</tr><%do until object.EOF%> 	 <tr>	<%for each x in object.Fields%>  <td id="TD"><%Response.Write(x.value)%></td>	<%next	object.MoveNext%>	</tr>	<%loop	object.close	%></table><%  end if %><%If regn_nm<>"" thensql="TRANSFORM Sum([2005_SSP].PREMIUM_TOTAL) AS SumOfPREMIUM_TOTAL SELECT [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD FROM 2005_SSP WHERE Regn_nm='"& Regn_nm &"' GROUP BY   [2005_SSP].REGN_NM, [2005_SSP].SALES_ST_NM, [2005_SSP].DST_CD PIVOT [2005_SSP].YR_MO;"set object=Server.CreateObject("ADODB.Recordset")object.Open sql,conn%><table border="1" width="100%"> <tr>	<%for each x in object.Fields  response.write("<th>" & x.name & "</th>")	next%>	</tr><%do until object.EOF%> 	 <tr>	<%for each x in object.Fields%>  <td id="TD"><%Response.Write(x.value)%></td>	<%next	object.MoveNext%>	</tr>	<%loop	object.close	%></table><%  end if %></form></body></html>