2 or more dynamic combo boxes from database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: 2 or more dynamic combo boxes from database

  1. #1
    asri Guest

    2 or more dynamic combo boxes from database


    Anybody can assist me pls.
    I have 3 or 4 combo boxes each depending on one another if selected would
    give different resultset based on the upper combo. It's like when u select
    first combo (State) then the second combo would only display city within
    that states and then if u select a particular city then the third combo would
    display all colleges within that city only. I don't want to use array because
    the data changed by user so most of the time need to refill the combo each
    time user select the combo.
    Any idea/samples. Thanks


  2. #2
    Andrew King Guest

    Re: 2 or more dynamic combo boxes from database

    This sample uses the Northwinds Db. Note it also uses adovbs.inc
    Normally the CreateDropDownList function would be in an include file for
    reuse rather than part of this page.

    Andrew

    <% OPTION EXPLICIT %>
    <%
    Dim DbConn, rs, rsProducts
    Dim CategoryID, ProductID

    Set DBConn = Server.CreateObject("ADODB.Connection")
    DbConn.Open "Provider=SQLOLEDB;Data Source=servername;Initial
    Catalog=northwind;User ID=sa;Password=password"

    ' Subroutine: CreateDropDownList
    '
    ' Arguments: szControlName - The name of the select list
    ' rsList - RecordSet containing values
    ' szValue - Column name of value column
    ' szShow - Column name of column to show
    ' szSelected - Value of default selection
    '
    ' Comments: Creates an option list from a recordset
    '
    Sub CreateDropDownList(szControlName, rsList, szValue, szShow, szSelected)
    on error resume next
    Dim szSelectedTag

    Response.Write "<SELECT NAME=""" & szControlName & """>"
    Do While Not rsList.EOF
    If CStr(szSelected) = CStr(rsList(szValue)) Then
    szSelectedTag = " SELECTED "
    Else
    szSelectedTag = ""
    End If
    Response.Write "<OPTION " & szSelectedTag & "VALUE=""" & rsList(szValue) &
    """>" & rsList(szShow) & Chr(13) & Chr(10)
    rsList.MoveNext
    Loop
    Response.Write "</SELECT>"
    End Sub

    %>

    <html>
    <head>
    <title>Multiple Drop Downs</title>
    </head>

    <!--#include virtual="/adovbs.inc"-->

    <body>

    <h2>Multiple Drop Downs</h2>

    <!--Select a Category-->
    <form action="dropdown.asp" method="post">
    <b>Select Category:</b>

    <%
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open "SELECT CategoryID, CategoryName FROM Categories ORDER BY
    CategoryName", DbConn, adOpenKeyset, adLockReadOnly
    CreateDropDownList "CategoryID", rs, "CategoryID", "CategoryName",
    Request("CategoryID")
    rs.Close
    %>
    <input type="submit" value="Select Category">
    </form>

    <% If Request("CategoryID") <> "" Then %>

    <!--Select a Product-->
    <form action="dropdown.asp?categoryid=<% =("categoryid") %>" method="post">
    <b>Select Product:</b>

    <%
    Set rs = Server.CreateObject("ADODB.Recordset")

    rs.Open "SELECT ProductID, ProductName FROM Products WHERE CategoryID = " &
    Request("CategoryID") & " ORDER BY ProductName", DbConn, adOpenForwardOnly,
    adLockReadOnly
    CreateDropDownList "ProductID", rs, "ProductID", "ProductName",
    Request("ProductID")
    rs.Close
    %>
    <input type="submit" value="Select Product">
    </form>

    <hr>
    <% If Request("ProductID") <> "" Then %>

    <br>
    <table border="1">
    <tr>
    <th>ID</th><th>Product Name</th><th>Unit Price</th>
    </tr>

    <%
    'List the products based on the selection in the list.
    Set rsProducts = Server.CreateObject("ADODB.Recordset")
    rsProducts.Open "SELECT ProductID, ProductName, UnitPrice FROM Products
    WHERE ProductID = " & Request("ProductID") & " ORDER BY ProductName",
    DbConn, adopenforwardonly, adlockreadonly

    If rsProducts.EOF Then
    Response.Write "Sorry, There are no articles in the database."
    Else
    Do While Not rsProducts.EOF
    Response.Write "<tr><td>" & rsProducts("ProductID") & "</td>"
    Response.Write "<td>" & rsProducts("ProductName") & "</td>"
    Response.Write "<td>" & rsProducts("UnitPrice") & "</td>"
    rsProducts.MoveNext
    Loop
    Response.Write "</table>"
    End If
    rsProducts.Close

    End If
    End If
    DbConn.Close
    %>
    </body>
    </html>




  3. #3
    asri Guest

    Re: 2 or more dynamic combo boxes from database


    Thanks Andrew. It worked but I was trying to do without the button which you
    have to resubmit each time you select a particular combo. Is there a way
    to use jscript to activate the code dynamically when user click on particular
    combo and the data would just reload? Onchange or OnClick event ??? The reason
    because my user currently used the VB version and it works like that and
    I try to do the same feature as possible with the web apps as well.
    Thanks


    "Andrew King" <aking@devx.com> wrote:
    >This sample uses the Northwinds Db. Note it also uses adovbs.inc
    >Normally the CreateDropDownList function would be in an include file for
    >reuse rather than part of this page.
    >
    >Andrew
    >
    ><% OPTION EXPLICIT %>
    ><%
    >Dim DbConn, rs, rsProducts
    >Dim CategoryID, ProductID
    >
    >Set DBConn = Server.CreateObject("ADODB.Connection")
    >DbConn.Open "Provider=SQLOLEDB;Data Source=servername;Initial
    >Catalog=northwind;User ID=sa;Password=password"
    >
    >' Subroutine: CreateDropDownList
    >'
    >' Arguments: szControlName - The name of the select list
    >' rsList - RecordSet containing values
    >' szValue - Column name of value column
    >' szShow - Column name of column to show
    >' szSelected - Value of default selection
    >'
    >' Comments: Creates an option list from a recordset
    >'
    >Sub CreateDropDownList(szControlName, rsList, szValue, szShow, szSelected)
    > on error resume next
    > Dim szSelectedTag
    >
    > Response.Write "<SELECT NAME=""" & szControlName & """>"
    > Do While Not rsList.EOF
    > If CStr(szSelected) = CStr(rsList(szValue)) Then
    > szSelectedTag = " SELECTED "
    > Else
    > szSelectedTag = ""
    > End If
    > Response.Write "<OPTION " & szSelectedTag & "VALUE=""" & rsList(szValue)

    &
    >""">" & rsList(szShow) & Chr(13) & Chr(10)
    > rsList.MoveNext
    > Loop
    > Response.Write "</SELECT>"
    >End Sub
    >
    >%>
    >
    ><html>
    ><head>
    ><title>Multiple Drop Downs</title>
    ></head>
    >
    ><!--#include virtual="/adovbs.inc"-->
    >
    ><body>
    >
    ><h2>Multiple Drop Downs</h2>
    >
    ><!--Select a Category-->
    ><form action="dropdown.asp" method="post">
    ><b>Select Category:</b>
    >
    ><%
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > rs.Open "SELECT CategoryID, CategoryName FROM Categories ORDER BY
    >CategoryName", DbConn, adOpenKeyset, adLockReadOnly
    > CreateDropDownList "CategoryID", rs, "CategoryID", "CategoryName",
    >Request("CategoryID")
    > rs.Close
    >%>
    ><input type="submit" value="Select Category">
    ></form>
    >
    ><% If Request("CategoryID") <> "" Then %>
    >
    ><!--Select a Product-->
    ><form action="dropdown.asp?categoryid=<% =("categoryid") %>" method="post">
    ><b>Select Product:</b>
    >
    ><%
    > Set rs = Server.CreateObject("ADODB.Recordset")
    >
    > rs.Open "SELECT ProductID, ProductName FROM Products WHERE CategoryID =

    " &
    >Request("CategoryID") & " ORDER BY ProductName", DbConn, adOpenForwardOnly,
    >adLockReadOnly
    > CreateDropDownList "ProductID", rs, "ProductID", "ProductName",
    >Request("ProductID")
    > rs.Close
    >%>
    ><input type="submit" value="Select Product">
    ></form>
    >
    ><hr>
    ><% If Request("ProductID") <> "" Then %>
    >
    ><br>
    ><table border="1">
    > <tr>
    > <th>ID</th><th>Product Name</th><th>Unit Price</th>
    > </tr>
    >
    > <%
    > 'List the products based on the selection in the list.
    > Set rsProducts = Server.CreateObject("ADODB.Recordset")
    > rsProducts.Open "SELECT ProductID, ProductName, UnitPrice FROM Products
    >WHERE ProductID = " & Request("ProductID") & " ORDER BY ProductName",
    >DbConn, adopenforwardonly, adlockreadonly
    >
    > If rsProducts.EOF Then
    > Response.Write "Sorry, There are no articles in the database."
    > Else
    > Do While Not rsProducts.EOF
    > Response.Write "<tr><td>" & rsProducts("ProductID") & "</td>"
    > Response.Write "<td>" & rsProducts("ProductName") & "</td>"
    > Response.Write "<td>" & rsProducts("UnitPrice") & "</td>"
    > rsProducts.MoveNext
    > Loop
    > Response.Write "</table>"
    > End If
    > rsProducts.Close
    >
    > End If
    >End If
    >DbConn.Close
    >%>
    ></body>
    ></html>
    >
    >
    >



  4. #4
    Andrew King Guest

    Re: 2 or more dynamic combo boxes from database

    >>Is there a way to use jscript to activate the code dynamically when user
    click on particular
    combo and the data would just reload?<<

    Sure, just add a OnChange event to the Select
    Response.Write "<SELECT NAME=""" & szControlName & """
    ONCHANGE=""HandleChange();"">"">"

    and a one line JavaScript Function

    <SCRIPT LANGUAGE="JavaScript">
    function HandleChange() {
    document.location.href="dropdown.asp?categoryid=" +
    document.forms[0].CategoryID.value;
    }
    </SCRIPT>

    Of course you would need to add a function to handle each list box.

    Andrew

    Here is the update code
    *********
    <% OPTION EXPLICIT %>
    <%
    Dim DbConn, rs, rsProducts
    Dim CategoryID, ProductID

    Set DBConn = Server.CreateObject("ADODB.Connection")
    DbConn.Open "Provider=SQLOLEDB;Data Source=servname-lt;Initial
    Catalog=northwind;User ID=sa;Password=password"

    ' Subroutine: CreateDropDownList
    '
    ' Arguments: szControlName - The name of the select list
    ' rsList - RecordSet containing values
    ' szValue - Column name of value column
    ' szShow - Column name of column to show
    ' szSelected - Value of default selection
    '
    ' Comments: Creates an option list from a recordset
    '
    Sub CreateDropDownList(szControlName, rsList, szValue, szShow, szSelected)
    on error resume next
    Dim szSelectedTag

    'Response.Write "<SELECT NAME=""" & szControlName & """>"
    Response.Write "<SELECT NAME=""" & szControlName & """
    ONCHANGE=""HandleChange();"">"">"
    Do While Not rsList.EOF
    If CStr(szSelected) = CStr(rsList(szValue)) Then
    szSelectedTag = " SELECTED "
    Else
    szSelectedTag = ""
    End If
    Response.Write "<OPTION " & szSelectedTag & "VALUE=""" & rsList(szValue) &
    """>" & rsList(szShow) & Chr(13) & Chr(10)
    rsList.MoveNext
    Loop
    Response.Write "</SELECT>"
    End Sub

    %>

    <html>
    <head>
    <title>Multiple Drop Downs</title>
    <SCRIPT LANGUAGE="JavaScript">
    function HandleChange() {
    document.location.href="dropdown.asp?categoryid=" +
    document.forms[0].CategoryID.value;
    }
    </SCRIPT>
    </head>

    <!--#include virtual="/adovbs.inc"-->

    <body>

    <h2>Multiple Drop Downs</h2>

    <!--Select a Category-->
    <form action="dropdown.asp" method="post">
    <b>Select Category:</b>

    <%
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open "SELECT CategoryID, CategoryName FROM Categories ORDER BY
    CategoryName", DbConn, adOpenKeyset, adLockReadOnly
    CreateDropDownList "CategoryID", rs, "CategoryID", "CategoryName",
    Request("CategoryID")
    rs.Close
    %>
    </form>

    <% If Request("CategoryID") <> "" Then %>

    <!--Select a Product-->
    <form action="dropdown.asp?categoryid=<% =("categoryid") %>" method="post">
    <b>Select Product:</b>

    <%
    Set rs = Server.CreateObject("ADODB.Recordset")

    rs.Open "SELECT ProductID, ProductName FROM Products WHERE CategoryID = " &
    Request("CategoryID") & " ORDER BY ProductName", DbConn, adOpenForwardOnly,
    adLockReadOnly
    CreateDropDownList "ProductID", rs, "ProductID", "ProductName",
    Request("ProductID")
    rs.Close
    %>
    <input type="submit" value="Select Product">
    </form>

    <hr>
    <% If Request("ProductID") <> "" Then %>

    <br>
    <table border="1">
    <tr>
    <th>ID</th><th>Product Name</th><th>Unit Price</th>
    </tr>

    <%
    'List the products based on the selection in the list.
    Set rsProducts = Server.CreateObject("ADODB.Recordset")
    rsProducts.Open "SELECT ProductID, ProductName, UnitPrice FROM Products
    WHERE ProductID = " & Request("ProductID") & " ORDER BY ProductName",
    DbConn, adopenforwardonly, adlockreadonly

    If rsProducts.EOF Then
    Response.Write "Sorry, There are no articles in the database."
    Else
    Do While Not rsProducts.EOF
    Response.Write "<tr><td>" & rsProducts("ProductID") & "</td>"
    Response.Write "<td>" & rsProducts("ProductName") & "</td>"
    Response.Write "<td>" & rsProducts("UnitPrice") & "</td>"
    rsProducts.MoveNext
    Loop
    Response.Write "</table>"
    End If
    rsProducts.Close

    End If
    End If
    DbConn.Close
    %>
    </body>
    </html>







  5. #5
    Andrew King Guest

    Re: 2 or more dynamic combo boxes from database

    Also if you are going to use the OnChange event you should add a prompt or
    blank line to the listbox/select list. Otherwise you can not select the
    first item in the list.

    Andrew
    DevX



  6. #6
    Ramana Guest

    Re: 2 or more dynamic combo boxes from database


    You can do, but you need to keep all the Data in an array on the client side
    or something and play with that. I suggest XML islands (using client side
    XML) and load the other combos.. You need have some knowledge on XML..

    "asri" <asri@tm.net.my> wrote:
    >
    >Anybody can assist me pls.
    >I have 3 or 4 combo boxes each depending on one another if selected would
    >give different resultset based on the upper combo. It's like when u select
    >first combo (State) then the second combo would only display city within
    >that states and then if u select a particular city then the third combo

    would
    >display all colleges within that city only. I don't want to use array because
    >the data changed by user so most of the time need to refill the combo each
    >time user select the combo.
    >Any idea/samples. Thanks
    >



  7. #7
    Kris Eiben Guest

    Re: 2 or more dynamic combo boxes from database

    Actually, can do this without arrays if you submit the page every time
    one of the upper combos changes. Check out this article:
    http://www.aspfaq.com/show.asp?id=2270

    "Ramana" <Ramana.Sakamuri@gecapital.com> wrote in message
    news:3d59559a$1@10.1.10.29...
    >
    > You can do, but you need to keep all the Data in an array on the

    client side
    > or something and play with that. I suggest XML islands (using client

    side
    > XML) and load the other combos.. You need have some knowledge on XML..
    >
    > "asri" <asri@tm.net.my> wrote:
    > >
    > >Anybody can assist me pls.
    > >I have 3 or 4 combo boxes each depending on one another if selected

    would
    > >give different resultset based on the upper combo. It's like when u

    select
    > >first combo (State) then the second combo would only display city

    within
    > >that states and then if u select a particular city then the third

    combo
    > would
    > >display all colleges within that city only. I don't want to use array

    because
    > >the data changed by user so most of the time need to refill the combo

    each
    > >time user select the combo.
    > >Any idea/samples. Thanks




  8. #8
    James Barbetti Guest

    Re: 2 or more dynamic combo boxes from database


    Hi Asri.

    [re a previous suggestion, you said...]
    >It worked but I was trying to do without the button which you
    >have to resubmit each time you select a particular combo. Is there
    >a way to use jscript to activate the code dynamically when user click
    >on particular combo and the data would just reload?


    Yes. Either trap the onchange event, or trap the onclick event (but if you
    trap onclick, you'll need to check that the new value is actually different
    from the previous value before acting on it).

    >Onchange or OnClick event ??? The reason
    >because my user currently used the VB version and it works like that
    >and I try to do the same feature as possible with the web apps as
    >well.


    To rebuild the choices for a combo box you can use something along these
    lines (if you want it to be portable ; the following works for Netscape as
    well as for IE):

    function addChoice(elt,value,text)
    {
    var o = elt.options;
    var n = document.createElement("OPTION");
    n.value = value;
    n.text = text;
    o.add (n);
    }
    function clearChoices(elt)
    {
    var o = elt.options;
    while (o.length>0) o.remove(0);
    }

    ...where elt is the SELECT element. But... If you wish to avoid sending
    requests back to the server on individual combo selects you'll have to send
    *all* of the data to the web page (say you're allowing choice of country,
    city, suburb. Then you need to send *all* of the country, city and suburb
    data - ouch!). Before trying to build a "no server trip" version of the
    form, try to figure out how much data it will have to be sent. It might
    well be hundreds of K. If it is... forget the idea. You'll need server
    round-trips. If you can afford to send all the data, but need some more
    help, respond to this post (or email me off-line).

    Hope this helps,
    James

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center