In this article I will explain with an example, how to use the CancelSelectOnNullParameter property in ASP.Net SqlDataSource.
The CancelSelectOnNullParameter property is used to cancel the SELECT query i.e. return no result when a parameter is NULL.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
StoredProcedure
The following StoredProcedure accepts a NULL parameter @CustomerID and its value is used to fetch the Customer from the Customers table of the Northwind database.
It is termed as NULL parameter, since if the value of the parameter is passed blank or empty string then the parameter value will be passed as NULL and it will return all records present in the table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEPROCEDURE [dbo].[Customers_GetCustomer]
@CustomerID NVARCHAR(30)
AS
BEGIN
SETNOCOUNTON;
SELECT CustomerId
,ContactName
,City
,Country
FROM Customers
WHERE (CustomerID = @CustomerID OR @CustomerID ISNULL)
END
Using CancelSelectOnNullParameter in ASP.Net SqlDataSource
The following HTML Markup consists of an ASP.Net GridView control, a TextBox and a Button.
The GridView is populated with records from the Customers table of the Northwind database using SqlDataSource.
The SqlDataSource has been set with following properties.
SelectCommand – Name of the StoredProcedure to be executed.
SelectCommandType – Value set as StoredProcedure. Default value is Text.
CancelSelectOnNullParameter – If not set to False, then the SELECT query will be cancelled for NULL parameter values. Default value is True.
The SqlDataSource also has a SelectParameter. Here SelectParameter of type ControlParameter is used as the value of an ASP.Net control i.e. TextBox needs to be passed as parameter.
The ControlParameter is set with the following properties.
Name – Name of the parameter being passed to StoredProcedure. The name of the parameter must be exact same as the StoredProcedure parameter name.
ControlID – ID of the control whose value will be passed as Parameter.
DefaultValue – The StoredProcedure used in this article displays all records when the parameter value is blank or empty string and the default value is set to blank.
ConvertEmptyStringToNull – As the name suggests it will pass blank or empty string as NULL if set to True. Since here blank or empty string needs to be passed, the value is set to False.
Customer ID:
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" />
<hr />
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
runat="server" AutoGenerateColumns="false" DataSourceID="GridDataSource" AllowPaging="true">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="GridDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConStr %>"
SelectCommand="Customers_GetCustomer" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="false">
<SelectParameters>
<asp:ControlParameter Name="CustomerID" ControlID="txtSearch" PropertyName="Text" DefaultValue="" ConvertEmptyStringToNull="false" />
</SelectParameters>
</asp:SqlDataSource>
Screenshots
GridView displaying all records
![CancelSelectOnNullParameter property in ASP.Net SqlDataSource]()
GridView records filtered using StoredProcedure using NULL Parameter
![CancelSelectOnNullParameter property in ASP.Net SqlDataSource]()
Demo
Downloads
