Accessing and Updating Data in ASP.NET 2.0: Handling Database NULL Values
By Scott Mitchell
A Multipart Series on ASP.NET 2.0'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 2.0's new data
source controls.
Data Source Control Basics - explores the concepts
and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
Accessing Database Data - shows how to use the
SqlDataSource and AccessDataSource controls to query data from a relational database.
Filtering Database Data with Parameters - learn how to
retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page,
session variables, and so on.
Creating Custom Parameter Controls - learn how
to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
Inserting Data - learn how to insert data using
a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
Deleting Data - see how to delete data using
a SqlDataSource control. Also looks at how to programmatically cancel a delete.
Updating Basics - learn the basics of updating database data using a
SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
Customizing the Editing Interface - see how to
customize the GridView's columns to provide a customized editing interface that includes input validation and alternative
user interface elements.
Handling Database NULL Valuese - explore how to
extend the GridView's customized editing interface to handle database NULL values.
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 NULLCategoryID 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],
[CategoryName], [UnitPrice], [Discontinued]
FROM [Products]
INNER JOIN [Categories] ON
[Products].[CategoryID] = [Categories].[CategoryID]
ORDER BY [ProductName]
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-NULLCategoryID value.
In order to return all products, regardless of whether they have a NULLCategoryID value,
we need to use a LEFT JOIN instead.
SELECT [ProductID], [ProductName], [Products].[CategoryID],
[CategoryName], [UnitPrice], [Discontinued]
FROM [Products] LEFT JOIN [Categories] ON
[Products].[CategoryID] = [Categories].[CategoryID]
ORDER BY [ProductName]
By changing the JOIN syntax to a LEFT JOIN, those products that have a NULLCategoryID 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 NULLCategoryID 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 NULLCategoryID 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:
<asp:SqlDataSource ID="CategoriesDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("CategoryName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
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 NULLCategoryID 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.
A Multipart Series on ASP.NET 2.0'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 2.0's new data
source controls.
Data Source Control Basics - explores the concepts
and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
Accessing Database Data - shows how to use the
SqlDataSource and AccessDataSource controls to query data from a relational database.
Filtering Database Data with Parameters - learn how to
retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page,
session variables, and so on.
Creating Custom Parameter Controls - learn how
to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
Inserting Data - learn how to insert data using
a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
Deleting Data - see how to delete data using
a SqlDataSource control. Also looks at how to programmatically cancel a delete.
Updating Basics - learn the basics of updating database data using a
SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
Customizing the Editing Interface - see how to
customize the GridView's columns to provide a customized editing interface that includes input validation and alternative
user interface elements.
Handling Database NULL Valuese - explore how to
extend the GridView's customized editing interface to handle database NULL values.
