Accessing and Updating Data in ASP.NET: Handling Database NULL Values
By Scott Mitchell
A Multipart Series on ASP.NET's Data Source Controls |
---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |
Introduction
In the last two installments of this article series - Updating Basics and Customizing the Editing Interface - we saw how to configure the SqlDataSource control to issue
UPDATE
statements to a
database and how to then configure the GridView control to work in tandem with the SqlDataSource control to provide a
web-based editing interface. By default, the GridView's editing interface renders a TextBox for each editable column in the
grid. However, in certain scenarios we may want to customize the editing interface by including validation controls or by
using an alternative Web control to collect the user's input. In particular, we customized the editing interface so that
when editing a product from the Northwind database, a user could select the product's category through a DropDownList
control rather than having to enter the actual CategoryID
value into a TextBox.
While the Customizing the Editing Interface article provided a complete, working example of creating a customized
editing interface, it had one potentially criticial shortcoming - the approach did not work with products that had a
NULL
database value for their CategoryID
. For starters, the SELECT
query used to
populate the GridView used an INNER JOIN
, which only returned those products with a matching category record.
But even if we update the query to use a LEFT JOIN
, the DropDownList in the customized editing interface did
not include a list item representing the NULL
value, so a user would be unable to edit a product and change
the category from an existing value to NULL
.
In this article we will look at how to customize the DropDownList in the editing interface such that it can correctly
handle database NULL
values. Read on to learn more!
Please Read Customizing the Editing Interface First |
---|
This article builds on top of the demo presented in the Customizing the Editing Interface article, so please be sure that you have read, implemented, and understand the demo from the previous installment before continuing on here. As with previous installments, a complete working demo is available for download at the end of this article. |
Returning Products that have a NULL
CategoryID
Value
The SqlDataSource control used in the Customizing the Editing Interface article returns information about all of the products in the
Products
table, including the corresponding category name from the Categories
table. This is accomplished using the following SelectCommand
statement:
SELECT [ProductID], [ProductName], [Products].[CategoryID],
|
The INNER JOIN
returns the matching Categories
row, enabling us to retrieve the CategoryName
for the product's associated CategoryID
value.
The Products
table's CategoryID
column may contain database NULL
values, but
INNER JOIN
s omit rows that have a NULL
value on the joined column. In short, the INNER JOIN
used in the query above returns only those products that have a non-NULL
CategoryID
value.
In order to return all products, regardless of whether they have a NULL
CategoryID
value,
we need to use a LEFT JOIN
instead.
SELECT [ProductID], [ProductName], [Products].[CategoryID],
|
If you need to touch up on SQL JOIN
syntax, check out the SQL JOIN
tutorials at W3 Schools, as well as the SQL
JOIN
entry at Wikipedia.
By changing the JOIN
syntax to a LEFT JOIN
, those products that have a NULL
CategoryID
value are included in the grid. By default, the NULL
values are displayed as a
blank string in the ItemTemplate. For example, in the screen shot below the Boston Crab Meat product has a NULL
CategoryID
value, as evidenced by the empty string displayed in its Category Name column.

Changing the JOIN
to a LEFT JOIN
is only the first step. We also need to update the
CategoriesDDL
DropDownList in the Category Name column's editing interface. Right now, CategoriesDDL
is bound to the set of cateogries in the Categories
table. There is, at this point, no list item that
corresponds to a database NULL
value. Consequently, if we attempt to edit a product with a NULL
CategoryID
value, we'll get an error. The error arises because in the EditItemTemplate we are binding the
edited product's CategoryID
value to the SelectedValue
property of the CategoriesDDL
DropDownList, but
the CategoriesDDL
DropDownList does not contain such a value.

Updating the CategoriesDDL
DropDownList to Handle NULL
Values
We need to update the list of items captured in the
CategoriesDDL
DropDownList to include both the full set
of possible categories and an item corresponding to the database NULL
value. The Parameter objects
in the SqlDataSource (or ObjectDataSource, for that matter), will natively translate database NULL
values
to empty strings and vice-a-versa. Therefore, all we need to do to get this NULL
issue working is add a
list item to the CategoriesDDL
DropDownList that has as its value an empty string.
Once we have an empty string list item in the CategoriesDDL
DropDownList, the two-way databinding works its magic.
When a row whose CategoryID
is NULL
is being edited, the NULL
value is translated into
an empty string and the corresponding drop-down list item is selected. Likewise, if at save the selected drop-down item is
the empty string item then it is translated into a database NULL
value, and that is what is saved in the database.
In order to add a list item to the DropDownList with an empty string value we need to do two things:
- Set the DropDownList's
AppendDataBoundItems
property to True. - Add an
<asp:ListItem>
to the control's declarative markup, settingValue=""
.
<asp:TemplateField HeaderText="CategoryName" SortExpression="CategoryName">
|
The markup from the two steps are colored red. First, note the AppendDataBoundItems
property assignment. If the AppendDataBoundItems
property is set to False (the default), the categories bound
to the DropDownList via the SqlDataSource in the EditItemTemplate will overwrite the list item for the NULL
database
value.
The second step manually adds a list item to the DropDownList. The list item's Text
property may be
assigned any value (here we use "(None)"), but it is vital that the Value
property be explicityly
set to an empty string. That is, it is imperative that the ListItem's markup includes Value=""
. If you omit this,
the Value
property will return the same value as the Text
property. Also be forewarned that the
Value
property cannot be assigned to an empty string through the Designer. Doing so will simply omit the
Value
property from the declarative syntax. You must enter this markup through the declarative syntax.
At this point the GridView both displays products with a NULL
CategoryID
value and allows a
such products to be edited and the ability to assign a product's category to the NULL
value.

Conclusion
This article was the third installment in this series that examined editing data with the data source controls in ASP.NET 2.0. The Updating Basics installment examined the core concepts of editing, while Customizing the Editing Interface explored how to enhance the GridView's editing experience. In this installment we built upon the demo created in the Customizing the Editing Interface article and saw how to handle
NULL
database values.
Happy Programming!
Attachments:
Further Readings:
A Multipart Series on ASP.NET's Data Source Controls |
---|
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.
Parameter controls for use in the data source controls' parameters collections.IDENTITY column value for the just-inserted record. |