When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, November 28, 2007

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.

  • 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.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • 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.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )

    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!

    - continued -

    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)
  • 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.

  • 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.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • 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.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article