Creating Databound DropDown Lists in ASP.NET

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.

Introduction
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 DepartmentID int, Primary Key Name varchar(200) ...

tblEmployee EmployeeID int, Primary Key DepartmentID int, Foreign Key Name varchar(100) SSN char(9) Salary Currency ...

The 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:

<select name="lstDepartment" size="1"> <option value="1">Marketing</option> <option value="2">Sales</option> <option value="4">Executive</option> <option value="5">IT</option> ... </select>

Note that you'd need to create an OPTION tag for each department, entering the department's respective 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:

1. Creating the listbox Web control in the HTML section.
   
2. Retrieving the data from the proper database table.
   
3. 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 DataValueField property, is the database table's column value that will appear in the OPTION tag's VALUE attribute. So, for this example, we want to set DataTextField to Name (the name of the department: Sales, Marketing, etc.) and DataValueField to DepartmentID (the unique identifier of the department).

<script language="VB" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) 'TODO: Get database data and databind it to the listbox Web control End Sub </script> <html> <body> <b>Departments</b>: <asp:listbox id="lstDepartments" runat="server" Rows="1" DataTextField="Name" DataValueField="DepartmentID" /> </body> </html>

Now that we have the listbox Web control done (along with the proper DataTextField and 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 Name and 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:

<% @Import Namespace="System.Data" %> <% @Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) 'Create a connection Const sConnStr as String="Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Databases\GrocerToGo.mdb" Dim objConn as New OleDbConnection(sConnStr) 'You must open the connection before populating the DataReader objConn.Open() 'Create a command object for the query Const strSQL as String = "SELECT DepartmentID, Name " & _ "FROM tblDepartment" Dim objCmd as New OleDbCommand(strSQL, objConn) 'Create/Populate the DataReader Dim objDR as OleDbDataReader objDR = objCmd.ExecuteReader() 'TODO: Databind the DataReader to the listbox Web control End Sub </script> <html> <body> <b>Departments</b>: <asp:listbox id="lstDepartments" runat="server" Rows="1" DataTextField="Name" DataValueField="DepartmentID" /> </body> </html>

Now that we've read the data into an OleDbDataReader all that's left to do is databind the OleDbDataReader to the listbox Web control. This is painfully easy, requiring just two lines of code - setting the listbox Web control's DataSource property to the OleDbDataReader object and then calling the DataBind() method.

<% @Import Namespace="System.Data" %> <% @Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) ' ... STEP 2 CODE REMOVED FOR BREVITY ... 'Databind the DataReader to the listbox Web control lstDepartments.DataSource = objDR lstDepartments.DataBind() End Sub </script> <html> <body> <b>Departments</b>: <asp:listbox id="lstDepartments" runat="server" Rows="1" DataTextField="Name" DataValueField="DepartmentID" /> </body> </html>

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 OleDbCommand object's ExecuteReader method, pass in the parameter CommandBehavior.CloseConnection, which will automatically close the database connection as soon as the DataReader is closed.
  
• Set the listbox's DataSource property directly to the result of the ExecuteReader method, avoiding an unneeded assignment. I.e.,

'Assign the DataSource property directly to the result 'of the ExecuteReader method. lstDepartments.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection) lstDepartments.DataBind()

Thanks, Alex!

Conclusion
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!

Happy Programming!