Handling Null Database Values Using Data Source Controls

Article
10/15/2014

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 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. You can then pass the data-bound value to the procedure with data-binding syntax, using the Eval or Bind methods, 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">

<script runat="server">
Public Function GetReportsTo(reportsTo As Object) As Integer
    If reportsTo Is DBNull.Value Then
        Return 0
    Else
        Return CInt(reportsTo)
    End If
End Function
</script>
<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='<%# GetReportsTo(Eval("ReportsTo")) %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="0">(none)</asp:ListItem>
                        </asp:DropDownList>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
                            DataTextField="Name" DataValueField="EmployeeID" SelectedValue='<%# GetReportsTo(Eval("ReportsTo")) %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="0">(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">

<script runat="server">
public int GetReportsTo(object reportsTo)
{
    if (reportsTo == DBNull.Value)
        return 0;
    else
        return (int)reportsTo;
}
</script>
<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='<%# GetReportsTo(Eval("ReportsTo")) %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="0">(none)</asp:ListItem>
                        </asp:DropDownList>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
                            DataTextField="Name" DataValueField="EmployeeID" SelectedValue='<%# GetReportsTo(Eval("ReportsTo")) %>' AppendDataBoundItems="True">
                            <asp:ListItem Selected="True" Value="0">(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>

For more information on the Eval and Bind methods, see Data-Binding Expression Syntax.

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 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 Value Types that Might Not Have a Defined Value. 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 Using Annotations with a Typed DataSet.