Creating Databound DropDown Lists in ASP.NETBy Scott Mitchell
|For More Information on ASP.NET|
|This article examines how to create a databound listbox using ASP.NET. For more information on ASP.NET be sure to check out the articles in the ASP.NET Article Index. The code in this article is based on the Beta 2 version of ASP.NET. To learn more about the free ASP.NET Beta 2, be sure to check out this FAQ.|
A very common, everyday task for Web developers is to create an HTML listbox populated with the values from a database table. Such a requirement is most common when attempting to display subsets of information that is spread across multiple, related database tables. For example, imagine that you were working on a database system that maintained information about each employee of the company. You might have the following database tables (among many others, of course):
tblDepartment table would contain a row for each department in the company
(i.e., Sales, Marketing, Executive, IT, etc.); the
tblEmployee table would contain
a row for each employee in the company with the
DepartmentID column representing
what department each particular employee works for. (Note that this database design is limited
in the sense that each employee can work for, at most, only one department.)
On a report-type screen, your boss may want to be able to list all of the employees from a particular
department. One way to do this is create an HTML listbox (
<SELECT NAME="listboxName"> ...
</SELECT>) and hard-code in the name and
DepartmentID of each of the
company's departments, like:
Note that you'd need to create an
OPTION tag for each department, entering the
DepartmentID in the
VALUE parameter. Of course
this hard-coding approach would be short-sighted, since if new departments are added or old ones
retired, you (or some other unlucky soul) will need to go into the ASP page and edit the
HTML by hand.
Ideally, the HTML listbox would populate itself automagically with the results from the database's
tblDepartment table. In this article we will look at how to accomplish this common
task using ASP.NET. We'll also (briefly) look at how this would be accomplished in classic ASP,
so that we can note the awesome reduction in the code needed for ASP.NET! (For information on
creating a list of databound radio buttons, be sure to read: Creating
a DataBound List of Radio Buttons in ASP.NET!)
Databinding an HTML Listbox in Classic ASP
There are a number of articles and FAQs that have been written that illustrate how to populate an HTML listbox with the contents from a database. For a quick read, check out Populating a Drop-Down List the Easy Way using SQL and VBScript and the FAQ: How do I populate an HTML dropdown box from the results of a database table?
While these approaches don't take an extraordinary amount of time to code, they do shamelessly mix the HTML script and ASP code, which makes the page harder to read and understand. Also, extra ugly code needs to be applied to have the item the user selected stay selected when the form is submitted.
With ASP.NET, as we will see shortly, only a few lines of code are needed and the HTML and code sections are kept separate. Additionally, ASP.NET automatically persists the user's selection across postbacks.
Databinding an HTML Listbox with ASP.NET
Binding a database table to a listbox with ASP.NET involves three steps:
- Creating the listbox Web control in the HTML section.
- Retrieving the data from the proper database table.
- Databinding the resulting database data to the listbox Web control.
Let's look at each of these three steps, one at a time. First, creating the listbox Web control
is simple enough, all it takes is one line of code. Note that for our databinding purposes you
should specify the column names of the text and value fields for the listbox. The text field,
specified via the
DataTextField property, is the database table's column value that
will appear in the Web page. The value field, specified via the
is the database table's column value that will appear in the
VALUE attribute. So, for this example, we want to set
Name (the name of the department: Sales, Marketing, etc.)
DepartmentID (the unique identifier of the department).
Now that we have the listbox Web control done (along with the proper
DataValueField settings), we need to move onto step 2: grabbing the database
results. We need to grab a snapshot of the
tblDepartment table, specifically of the
DepartmentID columns. To accomplish this, we will use the
OleDb set of classes and an
OleDbDataReader object. This code will
appear in our
Page_Load event handler:
Now that we've read the data into an
OleDbDataReader all that's left to do is
OleDbDataReader to the listbox Web control. This is painfully easy,
requiring just two lines of code - setting the listbox Web control's
property to the
OleDbDataReader object and then calling the
|You can view a live demo of this technique. The live demo also illustrates how to add a default option to the listbox (something like a "-- Choose an Option --" type entry in the listbox. For more information, be sure to read: Adding a Default ListItem in a Databound Listbox in ASP.NET.|
Some subtle improvements could be made to the above code. Alert reader Alex L. offerred the following suggestions:
- When calling the
ExecuteReadermethod, pass in the parameter
CommandBehavior.CloseConnection, which will automatically close the database connection as soon as the DataReader is closed.
- Set the listbox's
DataSourceproperty directly to the result of the
ExecuteReadermethod, avoiding an unneeded assignment. I.e.,
Creating a databound listbox using ASP.NET is, in my opinion, is both easier and produces more readable code. Note that in this article's example that we've only shown how to databind a listbox Web control. Obviously, a more practical task one would need to complete is to be able to submit the form once a listbox item was selected, and then the Web page could reload and produce output based upon that listbox selection. I'll save that task, though, for a future article!