Another Dynamic Droplist question

Discussion in 'ASP General' started by cwhite@theatomicmoose.ca, Apr 6, 2005.

  1. Guest

    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
     
    , Apr 6, 2005
    #1
    1. Advertisements

  2. Agoston Bejo Guest

    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_darkfalz.com> wrote in message
    news:...
    > 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
    >
    >
    > <> wrote in message
    > news:...
    > > 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, Apr 7, 2005
    #2
    1. Advertisements

  3. Bullschmidt Guest

    Bullschmidt, Apr 8, 2005
    #3
  4. Guest

    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
     
    , Apr 8, 2005
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Meir Rotfleisch

    Newbie Question about DropList and Page Load

    Meir Rotfleisch, Nov 2, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    361
    Thomas F.B.
    Nov 2, 2003
  2. Lasse Edsvik

    Filelist -> Droplist?

    Lasse Edsvik, Oct 4, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    441
    =?Utf-8?B?c3Jpbmk=?=
    Oct 4, 2004
  3. TJS
    Replies:
    4
    Views:
    3,016
  4. TJS
    Replies:
    2
    Views:
    745
  5. TJS
    Replies:
    5
    Views:
    2,118
    =?Utf-8?B?amZsZWVzb24=?=
    Mar 13, 2005
Loading...

Share This Page