How to Filter data using VB.NET

FilterExpression property used to filter rows returned by the control.FilterExpression reducing the amount of code needed to build condition and logical filter expression.
  • 13503
 

SqlDataSource provide FilterExpression property used to filter rows returned by the control. FilterExpression reducing the amount of code needed to build condition and logical filter expression. Many advantage of filter expression is that you can search data from multiple tables. SqlDataSqource control use Select Command statement to retrieve data from database.

In Default.aspx, I have used FilterExpression manually in SqlDataSource and in Dynamic.aspx page I dynamically set the FilterExpression and FilterParameters. FilterParameters is read only property. If you click on search button, GridView shows the filter data. Both Default.aspx and Dynamic.aspx return same result.

Default.aspx

<%@ Page Title="Home Page" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="false"    CodeFile="Default.aspx.vb" Inherits="_Default" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</
asp:Content>
<
asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:Button ID="Button1" runat="server" Text="Search" />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:Master %>"
    SelectCommand="SELECT [EMP_FNAME], [EMP_LNAME], [EMP_COUNTRY] FROM [EMPLOYEE1]"
    FilterExpression="EMP_FNAME like '{0}%' or EMP_LNAME like '{0}%'">
        <FilterParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="Name" PropertyName="Text" />
        </FilterParameters>
</
asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="EMP_FNAME" HeaderText="EMP_FNAME"
                SortExpression="EMP_FNAME" />
            <asp:BoundField DataField="EMP_LNAME" HeaderText="EMP_LNAME"
                SortExpression="EMP_LNAME" />
            <asp:BoundField DataField="EMP_COUNTRY" HeaderText="EMP_COUNTRY"
                SortExpression="EMP_COUNTRY" />
        </Columns>
</
asp:GridView>
</
asp:Content>

Output

Untitled-1.gif

Dynamic.aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Dynamic.aspx.vb" Inherits="Dynamic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<
html xmlns="http://www.w3.org/1999/xhtml">
<
head runat="server">
    <title></title>
</
head>
<
body>
    <form id="form1" runat="server">
    <div>
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:Button ID="Button1" runat="server" Text="Search" />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:Master %>"
    SelectCommand="SELECT EMP_FNAME, EMP_LNAME, EMP_COUNTRY FROM EMPLOYEE1" >
    </
asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="EMP_FNAME" HeaderText="EMP_FNAME"
                SortExpression="EMP_FNAME" />
            <asp:BoundField DataField="EMP_LNAME" HeaderText="EMP_LNAME"
                SortExpression="EMP_LNAME" />
            <asp:BoundField DataField="EMP_COUNTRY" HeaderText="EMP_COUNTRY"
                SortExpression="EMP_COUNTRY" />
        </Columns>
</
asp:GridView>
    </div>
    </form>
</
body>
</
html>

Dynamic.aspx.vb

Partial Class Dynamic
    Inherits System.Web.UI.Page
    Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
        SqlDataSource1.FilterExpression = "EMP_FNAME like '{0}%' or EMP_LNAME like'{0}%'"
        SqlDataSource1.FilterParameters.Add("EMP_FNAME", TextBox1.Text)
    End Sub
End
Class

Output

Untitled-1.gif

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.