Data controls support several methods for handling null values or missing data. For example, GridView, FormView and DetailsView all support EmptyDataText or EmptyDataTemplate properties. When the data source does not return data rows, you can use these properties to specify the content displayed by the control. We only need to set one of EmptyDataText and EmptyDataTemplate (if both are set, EmptyDataTemplate will be overloaded). You can also specify the ConvertEmptyStringToNull property on a bound field (and derived field type), template field, or data source parameter object to indicate that the String.Empty value from the client must be converted to a null value before the associated data source operation is called. .
ObjectDataSource also supports the ConvertNullToDbNull property. When the relevant method requires using DbNull to replace the null value (the TableAdapter class in the Visual Studio data set has this requirement), we can set this property to true. You can also specify the NullDisplayText property of the bound field (and derived field type), which specifies the content to be displayed when the value of a field returned by the data source is null. If the value does not change in edit mode, the value will be returned to the backend data source as a null value during the update operation. Finally, you can also specify the DefaultValue attribute of the data source parameter. If a parameter value passed in is empty, this attribute will assign a default value to the parameter. These properties are "chain-reactive", for example, if ConvertEmptyStringToNull and DefaultValue are both set, the String.Empty value will first be converted to null, and then converted to the default value.
<asp:DetailsView……>
＜Fields＞
＜asp:BoundField DataField="Phone" HeaderText="Phone" NullDisplayText="not listed" SortExpression="Phone" /＞
＜asp:BoundField DataField="Fax" HeaderText="Fax" NullDisplayText="not listed" SortExpression="Fax" /＞
＜/Fields＞
<EmptyDataTemplate>
＜asp:Image ID="Image1" runat="server" ImageUrl="~/Images/warning.gif" /＞There are no records to display
＜/EmptyDataTemplate＞
＜/asp:DetailsView＞
<asp:SqlDataSource……>
＜UpdateParameters＞
＜asp:Parameter Name="ContactTitle" Type="String" DefaultValue="Owner" ConvertEmptyStringToNull="true" /＞
＜asp:Parameter Name="Region" Type="String" ConvertEmptyStringToNull="true" /＞
＜asp:Parameter Name="Phone" Type="String" ConvertEmptyStringToNull="true" /＞
＜asp:Parameter Name="Fax" Type="String" ConvertEmptyStringToNull="true" /＞
＜asp:Parameter Name="CustomerID" Type="String" /＞
＜/UpdateParameters＞
…
</asp:SqlDataSource>
You can use these properties to handle null values to implement a drop-down list filter that initially displays all values from the data source until a value in the filter is selected. We implement it like this: first add a data item with an empty string value to the drop-down list, and set the ConvertEmptyStringToNull property of the ControlParameter (control parameter) related to the drop-down list in the data source.
Then in the SelectCommand of the data source, you can return all (unfiltered) values by detecting null values. The following example demonstrates this technique using a simple SQL command, but you can also perform null checking in the implementation of a stored procedure. Please note the use of the AppendDataBoundItems property of the drop-down list, which allows values from the drop-down list data source to be added to the "ALL" (this item is added statically by us) data items. At the same time, we should note that by default, if one of the values of the relevant parameters passed to SelectCommand is empty, SqlDataSource will not perform the Select operation. To force the Select operation to execute when a null value is passed, you can set its CancelSelectOnNullParameter property to false.
<asp:DropDownList AppendDataBoundItems="True" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="state" DataValueField="state" ID="DropDownList1" runat="server">
＜asp:ListItem Value=""＞ALL＜/asp:ListItem＞
＜/asp:DropDownList＞
＜asp:SqlDataSource ConnectionString="＜%$ ConnectionStrings:Pubs %＞" ID="SqlDataSource2" runat="server" SelectCommand="SELECT DISTINCT [state] FROM [authors]"＞
＜/asp:SqlDataSource＞
＜asp:SqlDataSource ConnectionString="＜%$ ConnectionStrings:Pubs %＞" ID="SqlDataSource1" runat="server" SelectCommand="SELECT au_id, au_lname, au_fname, state FROM authors WHERE state = IsNull( @state, state)" CancelSelectOnNullParameter="False"＞
＜SelectParameters＞
＜asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue" Type="String" /＞
＜/SelectParameters＞
＜/asp:SqlDataSource＞