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="JavaS cript">
<!--
function display(what){
document.getEle mentById(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.CreateOb ject("ADODB.Con nection")

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

'Create an ADO recordset object
Set rsFamily = Server.CreateOb ject("ADODB.Rec ordset")

'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("Fami ly")) %>">
<% Response.Write (rsFamily("Fami ly")) %>
</option>
<%
'Move to the next record in the recordset
rsFamily.MoveNe xt

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.CreateOb ject("ADODB.Rec ordset")

'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.MoveNex t

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

[Curt_C [MVP]]

the first list you can get by doing a SELECT DISTINCT.
For the second list you will have to have the page POST back to the server
and rebuild the page, passing in the first list value

--
Curt Christianson
Site & Scripts: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
<cw****@theatom icmoose.ca> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

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="JavaS cript">
<!--
function display(what){
document.getEle mentById(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.CreateOb ject("ADODB.Con nection")

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

'Create an ADO recordset object
Set rsFamily = Server.CreateOb ject("ADODB.Rec ordset")

'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("Fami ly")) %>">
<% Response.Write (rsFamily("Fami ly")) %>
</option>
<%
'Move to the next record in the recordset
rsFamily.MoveNe xt

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.CreateOb ject("ADODB.Rec ordset")

'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.MoveNex t

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.
"Curt_C [MVP]" <software_AT_da rkfalz.com> wrote in message
news:ul******** ******@TK2MSFTN GP10.phx.gbl...

the first list you can get by doing a SELECT DISTINCT.
For the second list you will have to have the page POST back to the server
and rebuild the page, passing in the first list value

--
Curt Christianson
Site & Scripts: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
<cw****@theatom icmoose.ca> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

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="JavaS cript">
<!--
function display(what){
document.getEle mentById(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.CreateOb ject("ADODB.Con nection")

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

'Create an ADO recordset object
Set rsFamily = Server.CreateOb ject("ADODB.Rec ordset")

'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("Fami ly")) %>">
<% Response.Write (rsFamily("Fami ly")) %>
</option>
<%
'Move to the next record in the recordset
rsFamily.MoveNe xt

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.CreateOb ject("ADODB.Rec ordset")

'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.MoveNex t

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

[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-de...tlistboxes.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...
*** Sent via Developersdex http://www.developersdex.com ***

[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