To read the article online, visit http://www.4GuysFromRolla.com/articles/112807-1.aspx

Accessing and Updating Data in ASP.NET: Handling Database NULL Values

By Scott Mitchell


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],
       [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-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],
       [CategoryName], [UnitPrice], [Discontinued]
FROM [Products]
       LEFT JOIN [Categories] ON
              [Products].[CategoryID] = [Categories].[CategoryID]
ORDER BY [ProductName]

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.

The Boston Crab Meat product has a NULL CategoryID value.

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.

An ArgumentOutOfRangeException exception is raised.

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:

  1. Set the DropDownList's AppendDataBoundItems property to True.
  2. Add an <asp:ListItem> to the control's declarative markup, setting Value="".
After applying these steps, the markup for the Category Name TemplateField should look similar to the following:

<asp:TemplateField HeaderText="CategoryName" SortExpression="CategoryName">
    <EditItemTemplate>
       <asp:DropDownList ID="CategoriesDDL" runat="server" DataSourceID="CategoriesDataSource" AppendDataBoundItems="True"
            DataTextField="CategoryName" DataValueField="CategoryID" SelectedValue='<%# Bind("CategoryID") %>'>
                <asp:ListItem Text="(None)" Value=""></asp:ListItem>
       </asp:DropDownList>


       <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 NULL CategoryID value and allows a such products to be edited and the ability to assign a product's category to the NULL value.

The CategoryDDL DropDownList includes a list item to capture NULL values.

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!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

    Further Readings:


  • Tutorials on Editing, Inserting, and Deleting Data (using the ObjectDataSource)
  • Inserting, Updating, and Deleteing Data with the SqlDataSource (VB Version) (C# version)
  • Adding Validation Controls to the Editing and Inserting Interfaces (VB Version) (C# version)
  • Customizing the Data Modification Interface (VB Version) (C# version)
  • Article Information
    Article Title: ASP.NET.Accessing and Updating Data in ASP.NET: Handling Database NULL Values
    Article Author: Scott Mitchell
    Published Date: November 28, 2007
    Article URL: http://www.4GuysFromRolla.com/articles/112807-1.aspx


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers