ASP.net 2.0 advanced data processing null value

Data controls support a variety of methods for handling null or missing data. For example, the GridView, FormView, and DetailsView both support the Emptydatatext or EmptyDataTemplate properties, which you can use to specify what the control displays when the data source does not return rows of data. We just need to set one of the Emptydatatext and EmptyDataTemplate (if all two are set, EmptyDataTemplate will be overloaded). You can also specify the ConvertEmptyStringToNull property on the bound field (and derived field type), template field, or data source parameter object, indicating that the String.Empty value from the client must be converted to a null value before the associated data source operation is invoked.

ObjectDataSource also supports Convertnulltodbnull properties, when the associated method requires that DBNull be substituted for null values (the TableAdapter class in the Visual studio dataset 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 what is displayed when the value of a field returned by the data source is empty. If this value does not change in edit mode, this value is returned to the backend data source as a null value in the update operation. Finally, you can specify the DefaultValue property of the data source parameter, and if a passed in parameter value is empty, the property assigns a default value to the parameter. These properties are "chain reaction", for example, if both ConvertEmptyStringToNull and DefaultValue are set, then the String.Empty value is first converted to null (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
</empty Datatemplate>
</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 null-handling properties to implement a Drop-down list filter, so that it initially displays all the values of the data source until a value in the filter is selected. That's how we do it: 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) associated with the Drop-down list in the data source.

Then, in the SelectCommand of the data source, you can return all values by detecting null values. The following example illustrates this technique, which uses a simple SQL command, but you can also perform null detection in the implementation of a stored procedure. Note the use of the AppendDataBoundItems property of the Drop-down list, which allows the values from the Drop-down list data source to be added to "all" (this item is statically added) after the data item. At the same time, we should note that, by default, SqlDataSource does not perform a select operation if one of the values of the related parameters passed to SelectCommand is empty. When a null value is passed, you can set its Cancelselectonnullparameter property to false in order to force the select operation to execute.

<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>