Filter ASP.Net GridView with TextBox using SqlDataSource in C# and VB.Net

Last Reply 4 months ago By pandeyism

Posted 4 months ago

I want to be able to search the project name as I type in the letters.

For example, if the user type in "M", it should list all the project name that contains "M" in it.

If the user type in "The", it will list all the project name that contains the word "The". 

<form id="form1" runat="server">
<div runat="server">
    <p>Search Students By
    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
        <asp:ListItem Value="">--Select All--</asp:ListItem>          
        <asp:ListItem Value="ProjectName">Project Name</asp:ListItem>
    </asp:DropDownList>
    : <asp:TextBox ID="txtSearch" runat="server" Width="200px"></asp:TextBox>
    &nbsp;<asp:Button ID="btnSearch" runat="server" Text="Seach" />
</div>
</form>

 

Protected Sub btnSearch_Click(sender As Object, e As System.EventArgs) Handles btnSearch.Click
    Select Case DropDownList1.SelectedValue
        Case "ProjectName"
            SqlDataSource1.SelectCommand = "SELECT * FROM [DNP_ProjDefense] WHERE ProjectName like '%" + txtSearch.Text + "%'"
        Case Else
            txtSearch.Text = ""
            SqlDataSource1.SelectCommand = "SELECT * FROM [DNP_ProjDefense] order by LastName desc"
    End Select
    GridView1.DataBind()
End Sub

Thank you!

You are viewing reply posted by: pandeyism 4 months ago.
Posted 4 months ago Modified on 4 months ago

Hi kelsen1989,

I checked code is working.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

HTML

<div id="Div1" runat="server">
    <p>
        Search Name By
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
            <asp:ListItem Value="">--Select All--</asp:ListItem>
            <asp:ListItem Value="ProjectName">Contact Name</asp:ListItem>
        </asp:DropDownList>
        :
        <asp:TextBox ID="txtSearch" runat="server" Width="200px"></asp:TextBox>
        &nbsp;<asp:Button ID="btnSearch" runat="server" Text="Seach"  OnClick="btnSearch_Click"/>
        <asp:GridView runat="server" ID="GridView1" />
</div>

Code

C#

SqlDataSource SqlDataSource1 = new SqlDataSource();
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        SqlDataSource1.ID = "SqlDataSource1";
        this.Page.Controls.Add(SqlDataSource1);
        SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country from Customers";
        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
    }
}

protected void btnSearch_Click(object sender, System.EventArgs e)
{
    SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    switch (DropDownList1.SelectedValue)
    {
        case "ProjectName":
            {                    
                SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country from Customers WHERE ContactName like '%" + txtSearch.Text + "%'";
                break;
            }
        default:
            {
                SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country FROM [Customers] order by ContactName DESC";
                break;
            }
    }
    GridView1.DataSource = SqlDataSource1;
    GridView1.DataBind();
}

VB.Net

Private SqlDataSource1 As SqlDataSource = New SqlDataSource()
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)Handle Me.Load
        If Not Me.IsPostBack Then
            SqlDataSource1.ID = "SqlDataSource1"
            Me.Page.Controls.Add(SqlDataSource1)
            SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString
            SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country from Customers"
            GridView1.DataSource = SqlDataSource1
            GridView1.DataBind()
        End If
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Select Case DropDownList1.SelectedValue
            Case "ProjectName"
                SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country from Customers WHERE ContactName like '%" & txtSearch.Text & "%'"
                Exit Select
            Case Else
                SqlDataSource1.SelectCommand = "SELECT top 10 ContactName, City, Country FROM [Customers] order by ContactName DESC"
                Exit Select
        End Select

        GridView1.DataSource = SqlDataSource1
        GridView1.DataBind()
    End Sub

Screenshot