Handling Null Database Values Using Data Source Controls

  • Article

In many cases, columns in database tables return null when no value is stored in that column. However, a null value can present challenges when you are working with ASP.NET code or with data-bound Web controls. For example, an exception is thrown if you try to bind the SelectedValue of a DropDownList control to null.

ASP.NET provides some built-in functionality for working with null values. If your scenario is not solved using the built-in ASP.NET capabilities, there are some additional techniques you can use for working with null values.

The NullDisplayText Property

You can set the NullDisplayText property of bound fields in data source controls (such as the BoundField, CheckBoxField, and ImageField objects) to substitute a specific value (such as a string) for null values returned from the data source. Controls will then display that value as the text of the bound control. When a data-bound row is modified during an edit operation, if the value for a bound field matches the NullDisplayText (whether the value was not modified or the user entered the same value as the NullDisplayText), the field passes null to the data source as the field value. If the NullDisplayText property is not set, null values are displayed as empty strings ("").

For example, if you set the NullDisplayText property for a BoundField object to "(no value)", and if the bound data column is null, the Text property for Label or TextBox objects rendered by the BoundField object is set to "(no value)". If the user edits the row and changes the value from "(no value)" to "Custom Value", the value "Custom Value" is passed to the data source as the value for the field. But if the value of the bound control is still "(no value)", the data control passes null to the data source as the field value.

The ConvertEmptyStringToNull Property

Parameter objects, TemplateField objects, and bound fields (BoundField, CheckBoxField, ImageField, and AutoGeneratedField objects) support a ConvertEmptyStringToNull property that determines how the object will treat empty string ("") values during update, insert, or delete operations. If the ConvertEmptyStringToNull property is true for an object, and if the value for that object is an empty string, the object passes null to the data source as the object value. If the ConvertEmptyStringToNull property is false for an object, and the value of the object is an empty string, an empty string is passed to the data source as the object value.

Converting Null in Template Fields

The TemplateField object does not have a NullDisplayText property, because the template might contain multiple bound fields. However, you can create your own null handling procedure for a bound field and then pass the data-bound value to the procedure with one-way data-binding syntax, using the Eval method. For two-way data-binding scenarios, using the Eval and Bind methods, you can use a DropDownList control with the AppendDataBoundItems property set to true, as shown in the following example.

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Northwind Employees</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID"
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
                    ReadOnly="True" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:TemplateField HeaderText="ReportsTo" SortExpression="ReportsTo">
                    <ItemTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server" Enabled="False" DataSourceID="SqlDataSource2"
                            DataTextField="Name" DataValueField="EmployeeID" SelectedValue='<%# Eval("ReportsTo") %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="">(none)</asp:ListItem>
                        </asp:DropDownList>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
                            DataTextField="Name" DataValueField="EmployeeID" SelectedValue='<%# Bind("ReportsTo") %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="">(none)</asp:ListItem>
                        </asp:DropDownList>
                    </EditItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"
            SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [ReportsTo] FROM [Employees]"
            UpdateCommand="UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName, [ReportsTo] = @ReportsTo WHERE [EmployeeID] = @EmployeeID">
            <UpdateParameters>
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="ReportsTo" Type="Int32" />
                <asp:Parameter Name="EmployeeID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"
            SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName As Name From Employees">
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Northwind Employees</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID"
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
                    ReadOnly="True" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:TemplateField HeaderText="ReportsTo" SortExpression="ReportsTo">
                    <ItemTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server" Enabled="False" DataSourceID="SqlDataSource2"
                            DataTextField="Name" DataValueField="EmployeeID" SelectedValue='<%# Eval("ReportsTo") %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="">(none)</asp:ListItem>
                        </asp:DropDownList>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
                            DataTextField="Name" DataValueField="EmployeeID" SelectedValue='<%# Bind("ReportsTo") %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="">(none)</asp:ListItem>
                        </asp:DropDownList>
                    </EditItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"
            SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [ReportsTo] FROM [Employees]"
            UpdateCommand="UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName, [ReportsTo] = @ReportsTo WHERE [EmployeeID] = @EmployeeID">
            <UpdateParameters>
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="ReportsTo" Type="Int32" />
                <asp:Parameter Name="EmployeeID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"
            SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName As Name From Employees">
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

When the AppendDataBoundItems property is set to true, the DropDownList control is populated with both static items and data generated from the data source. The static list item that is added to the DropDownList control has the Value property set to an empty string. With that, a data item that contains a null value is bound to the static list item.

For more information on the Eval and Bind methods, see Data-Binding Expressions Overview.

Techniques for Working with Null Using the ObjectDataSource Control

When you create an object to act as the source for an ObjectDataSource control, you can manage the conversion of null values within the object's code. Two options are available: nullable types and annotations for strongly typed objects such as datasets.

Note

For more information about the ObjectDataSource control, see ObjectDataSource Web Server Control Overview.

Using Nullable Types

When you create the select, insert, update, and delete methods of the source object for a ObjectDataSource control, if the parameter value can be null in the data source, you can define parameters and return values for those methods as nullable types. Nullable types are value types such as an integer or a Boolean that can be either a regular value or a null value.

For information on using nullable types with Visual Basic, see Nullable Value Types. For information on using nullable types with C#, see Using Nullable Types (C# Programming Guide).

Annotating a Strongly Typed Dataset

A common source object for the ObjectDataSource control is a strongly typed DataSet object. To have a strongly typed DataSet convert null values returned from a data source to a value that you specify, you can annotate the strongly typed DataSet using the nullValue annotation. For more information, see Annotating Typed DataSets (ADO.NET).

See Also

Concepts

Data Source Controls Overview

ASP.NET Data-Bound Web Server Controls Overview

Data-Binding Expressions Overview

Change History

Date

History

Reason

July 2008

Fixed the sample.

Customer feedback.