Published: Wednesday, July 11, 2001
Creating Databound DropDown Lists in ASP.NET
By 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.
|
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:
- 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 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>
|
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!
By Scott Mitchell
Related Links
Read ASP.NET DropDownList Controls by Steve Smith
Read Dynamically Setting Text and Value Fields for a DropDownList Control
