Handling Null values in the SqlDataSource Control
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Handling Null values in the SqlDataSource Control</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="categoriesSource"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="Select ProductSubcategoryID, Name from Production.ProductSubcategory">
</asp:SqlDataSource>
Categories:
<asp:DropDownList AppendDataBoundItems="true"
runat="server"
DataSourceID="categoriesSource"
DataValueField="ProductSubcategoryID"
DataTextField="Name"
AutoPostBack="true"
ID="lstCategories">
<asp:ListItem Value="">ALL</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="productsSource"
runat="server"
ProviderName="System.Data.SqlClient"
CancelSelectOnNullParameter="false"
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
SelectCommand="Select ProductID, Name, ProductNumber, StandardCost from Production.Product WHERE ProductSubcategoryID = IsNull(@ProductSubcategoryID, ProductSubcategoryID)">
<SelectParameters>
<asp:ControlParameter ControlID="lstCategories" Name="ProductSubcategoryID"
ConvertEmptyStringToNull="true" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView runat="server" DataSourceID="productsSource"
HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="blue" HeaderStyle-ForeColor="White" />
</div>
</form>
</body>
</html>
Related examples in the same category