Another Dynamic Droplist question

[cwhite]

Hi

I have another drop list question.

I have a table like this:


computer dell
computer ibm
computer hp
laptop toshiba
laptop sony
laptop dell


the first drop list should display only two items:

computer
laptop


and the item selected in the first drop list will generate the 2nd
list, if you select computer then the 2nd list will display:

dell
ibm
hp

once both choices are made they are submitted to a new table, my
problem is this, I can't get it to generate the 2nd list, here's my
code:

<script language="JavaScript">
<!--
function display(what){
document.getElementById(what).selected = true;
}

//-->
</script>
<select name="Family" onchange=submit()>
<option value=""></option>
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsFamily 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("products.mdb")

'Create an ADO recordset object
Set rsFamily = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the
database
strSQL = "SELECT DISTINCT Family FROM Model;"

'Open the recordset with the SQL query
rsFamily.Open strSQL, adoCon

'Loop through the recordset
Do While not rsFamily.EOF
%>
<option value="<% Response.Write (rsFamily("Family")) %>">
<% Response.Write (rsFamily("Family")) %>
</option>
<%
'Move to the next record in the recordset
rsFamily.MoveNext

Loop

'Reset server objects
rsFamily.Close
Set rsFamily = Nothing

%>
</select>
<br>
<select name="Model">
<option value=""></option>
<%
'Dimension variables
Dim rsModel 'Holds the recordset for the records in the database

'Create an ADO recordset object
Set rsModel = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the
database
strSQL = "SELECT DISTINCT Model FROM Model= WHERE Family = '" & Family
& "'"

'Open the recordset with the SQL query
rsModel.Open strSQL, adoCon

'Loop through the recordset
Do While not rsModel.EOF
%>
<option value="<% Response.Write (rsModel("Model")) %>">
<% Response.Write (rsModel("Model")) %>
</option>
<%
'Move to the next record in the recordset
rsModel.MoveNext

Loop

'Reset server objects
rsModel.Close
Set rsModel = Nothing
Set adoCon = Nothing
%>
</select>


the problem is with the line:

strSQL = "SELECT DISTINCT Model FROM Model= WHERE Family = '" & Family
& "'"

if I change it to:

strSQL = "SELECT DISTINCT Model FROM Model= WHERE Family = 'computer'"

then the error goes away, but obviously it won't show the laptops when
selected as it will always show just the computers

what have I done wrong here? can anyone help or provide me with a link
to explain how it should be done?


thanks

 

[Agoston Bejo]

Or, if you don't want to do that (it is by far the most convenient and
robust way, though), you may generate a client-side javascript code based on
your recordsets that adds/removes the appropriate elements in the second
select when the first one changes. But that would really be a pain to write
and maintain.

 

[Bullschmidt]

Perhaps this may hopefully give you some ideas. It's got a downloadable
sample and does retain values after the post back:

Classic ASP Design Tips - Dependent Listboxes
http://www.bullschmidt.com/devtip-dependentlistboxes.asp

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

 

[cwhite]

That looks like what I am looking for, and it explains what's going on.

I don't think that I need to ask how you found it

Thanks