Dropdown lists in HTML are very useful for multiple-option choices, especially within a web form that user needs to fill out.
Producing them dynamically from a database can saves a lot of time and energy.
In this example, we will use ASP 3.0 to generate dynamic dropdown lists from a Microsoft Access database. The database table in question contains fruit and vegetable types.
Fig 1: The data used to build the first list
The ID field is set to an autonumber, in this instance, which is a propietary Microsoft Access data type which auto increments. A simple number or integer field for other database types, such as SQL Server, MySQL or similar will work just as well. The other two fields are set as text fields, with Title having a limit of 50 characters and Comment having a limit of 255 characters.
Now, the code to build a page with a HTML dropdown list populated with the above data.
<%@LANGUAGE="VBSCRIPT" %> <!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" --> <% Option Explicit '-- Variables we are using dim objConn, objRS, objRS2, strSQL '-- Define our objects Set objConn = Server.CreateObject("ADODB.Connection") Set objRS = Server.CreateObject("ADODB.Recordset") Set objRS2 = Server.CreateObject("ADODB.Recordset") '-- Database path and Data provider objConn.ConnectionString = Server.MapPath("dropdowns.mdb") objConn.Provider = "Microsoft.Jet.OLEDB.4.0" objConn.Open '-- Sub Procedure that builds the dropdown list. '-- Parameters c, r and table are passed when the sub is built. Sub showDropDownList(c, r, table) '-- SQL Statement strSQL = "SELECT * FROM " & table & " ORDER BY Title ASC" '-- Execute our SQL statement and store the recordset Set r = c.Execute(strSQL) '-- START MAIN CODE BLOCK '-- If we have records to return if not r.eof = false then '-- Open a form/select tag '-- This particular example has no action attribute, which would normally... '-- ...be required in order to post the data to another page for processing. Response.Write "<form name=""form1"">" Response.Write "<select name=""dropdownlist"">" & vbCrLf Response.Write "<option selected=""selected"" value="""">Choose....</option>" '-- loop and build each database entry as a selectable option While r.EOF = false Response.Write "<option value=""dynamic_dropdown_lists.asp"">" _ & r.Fields("Title").Value & "</option>" & vbCrLf '-- Move recordset to the next value r.movenext Wend end if '--END OF MAIN CODE BLOCK '-- close select/form tags Response.Write "</select></form>" & vbCrLf End Sub %> <html> <head> <title>Dynamic dropdown lists in ASP(VBScript) - designplace.org</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> </head> <body> <h1>Dynamic dropdown lists using Active Server Pages and Microsoft Access</h1> <p>Below is an example of a drop down list, dynamically populated from a database table.</p> <p> <% ' -- Call the sub procedure to build the drop down list, with the connection and recordset objects and the source table (In quotes). call showDropDownList(objConn, objRS, "tblDropDownTest") %> </p> </body> </html>
Thats the code done with, now lets have a look at what the result is.
Fig 2.) Dropdown list output
As you can see, the dropdown list is populated with the database values, ordered A-Z.
We are showing fruit and vegetables in this list, without any separation. So, what if you wanted to distinguish between fruit and vegetables within the dropdown list, to break the list up into 2 sections?. We need to alter our code a little to accomplish this and bring in some new concepts, which will filter the recordset for Fruit and display the results, then filter for Veg and display the results.
Firstly, we'll add a new field to our database table, called type. Our data now looks like this:
Fig 3.) Database table with "Type" field added
The "Type" field is a text field, with a limit of 5 characters (For this example)
We need to amend our While..Wend loop next, in order to produce a dropdown list with 2 distinct sections, one for fruit and one for veg. Each section will be ordered A-Z.
'-- START MAIN CODE BLOCK '-- If we have records to return if r.eof = false then '-- Open a form/select tag Response.Write "<form name=""form1"">" Response.Write "<select name=""dropdownlist"">" & vbCrLf '-- loop and build each database entry as a selectable option '-- FILTER r.Filter = ("Type = 'fruit'") Response.Write "<option selected=""selected"" value="""">Choose....</option>" Response.Write "<optgroup label=""Fruit""></optgroup>" While r.EOF = false Response.Write "<option value=""dynamic_dropdown_lists.asp"">" _ & r.Fields("Title").Value & "</option>" & vbCrLf '-- Move recordset to the next value r.movenext Wend r.Filter = adFilterNone r.Filter = ("Type = 'veg'") Response.Write "<optgroup label=""Veg""></optgroup>" While r.EOF = false Response.Write "<option value=""dynamic_dropdown_lists.asp"">" _ & r.Fields("Title").Value & "</option>" & vbCrLf '-- Move recordset to the next value r.movenext Wend end if '-- END OF MAIN CODE BLOCK
The new code that has been written is indicated in bold. Lets breakdown what its doing.
For our 2 types, (Fruit and Veg) we display as headings within the list, using the <optgroup> HTML tag. The label attribute defines what will display as text in the list.
The ADO recordset function called "filter" is being used and we are filtering our recordset object (r), for the type "Fruit". Type is the name of the field within the database that we are referring to.
Once we have filtered our recordset for all Fruit, the recordset now only contains records where the type is Fruit, so we can loop through the whole recordset and display all the results. Once we are finished, we remove the filter by using the predefined ADO constant called adFilterNone.
Then, the process beings again on our full recordset but this time we filter for "veg". Then, we loop through and display all the records that are veg.
Fig 4,5.) Resulting output of filtered dropdown list
That pretty much wraps up this tutorial on dynamic dropdown lists. To download the example code, discuss this article in the forums or gather any further information, see the links below.
Want to build multiple dynamic dropdown lists in ASP which change values depending on user input? Check out this article from programmers resource.
Remember, if you get stuck or need to ask any questions, register with the forums and tell us about it!