In this
article we will show how to filter Gridview using textbox value. Here we will
pass value using textbox and based on that value we will filter Gridview. We
can check either both value must be apply or any one of them. So we can bind
using AND operator or else OR operator in Filter Expression.
Here we
will Bind Gridview using SqlDataSource so we can directly get data from
database and when we run then we can directly filter on Gridview using SqlDataSource.
We can
also create paging, select, edit, delete link in Gridview. Using SqlDataSource
we can create command inline in SqlDataSource or delete, update, select. SqlDataSource
are very useful data tool for binding data in controls.
Here we
will create table and bind it with Gridview using SqlDataSource.
CREATE TABLE [dbo].[Customers](
[ContactName] [varchar](20) NOT NULL,
[City] [varchar](20) NOT NULL,
[PostalCode] [int] NOT NULL,
[Country] [varchar](20) NOT NULL,
[Phone] [bigint] NOT NULL,
[Fax] [bigint] NOT NULL,
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT
[PK_Customers] PRIMARY KEY
CLUSTERED
(
[CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Now using SqlDataSource
we can bind this table in Gridview. For filter expression using textbox value.
We can set filter expression using AND || OR Operator in SqlDataSource Filter
Expression. Here we will check for AND and check for Country value like what we
pass in country textbox. And also same for city value in city textbox.
HTML CODE:
<div>
City:<asp:TextBox ID="txtcity"
runat="server"></asp:TextBox>
Country:<asp:TextBox ID="txtcountry"
runat="server"></asp:TextBox>
<asp:Button ID="btnSearch"
runat="server"
Text="Search"
/>
<hr
/>
<asp:GridView ID="GridView2"
HeaderStyle-BackColor="#3AC0F2"
HeaderStyle-ForeColor="White"
runat="server"
AutoGenerateColumns="False"
DataSourceID="SqlDataSource1"
AllowPaging="True"
BackColor="White"
BorderColor="#CCCCCC"
BorderStyle="None"
BorderWidth="1px"
CellPadding="4"
ForeColor="Black"
GridLines="Horizontal">
<Columns>
<asp:BoundField DataField="ContactName"
HeaderText="ContactName"
ItemStyle-Width="150"
SortExpression="ContactName">
<ItemStyle Width="150px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="City"
HeaderText="City"
ItemStyle-Width="150"
SortExpression="City">
<ItemStyle Width="150px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="Country"
HeaderText="Country"
ItemStyle-Width="150"
SortExpression="Country">
<ItemStyle Width="150px"></ItemStyle>
</asp:BoundField>
</Columns>
<FooterStyle BackColor="#CCCC99"
ForeColor="Black"
/>
<HeaderStyle BackColor="#333333"
ForeColor="White"
Font-Bold="True"></HeaderStyle>
<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F7F7F7" />
<SortedAscendingHeaderStyle BackColor="#4B4B4B" />
<SortedDescendingCellStyle BackColor="#E5E5E5" />
<SortedDescendingHeaderStyle BackColor="#242121" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:UHConnection %>"
SelectCommand="SELECT
[ContactName], [City], [Country] FROM [Customers]" FilterExpression="Country
LIKE '%{0}%' and City LIKE '%{1}%'">
<FilterParameters>
<asp:ControlParameter Name="Country"
ControlID="txtcountry"
PropertyName="Text"
/>
<asp:ControlParameter Name="City" ControlID="txtcity"
PropertyName="Text"
/>
</FilterParameters>
</asp:SqlDataSource>
</div>
Thanks for comments.....