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 JOINs 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
AppendDataBoundItemsproperty 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. |
