How to bind and implement search gridview records in asp.net | Use of If Else If in Sql server

Introduction: In this article I am going to explain with example How to bind Asp.Net gridview and implement filter/search functionality using Sql Sever CASE WHEN  or NESTED IF-ELSE.

Bind and search records from gridview example in asp.net
Click on the image to view enlarged demo

Description: You will learn the following through this article:
  • How to Bind gridview from sql server database table using stored procedure
  • How to implement the search/filter feature in grid view to search the records based on selected criteria. In this example i have implemented the functionality to filter the records by Emp Name, Salary and City.
  • How to use nested If Else If  in stored procedure in Sql server
  • How to use CASE in stored procedure in Sql server

Implementation: Let’s create sample asp.net website to see it in action.
  • First of all create a Database in Sql server e.g. “Emp_DB” and in this database create a table with the columns and data type as shown below and name it "Emp_Tb":

Column Name
Data Type
Emp_Id
Int(Primary Key. So set Is Identity=True)
EmpName
varchar(100)
Age
int
Salary
int
City
varchar(100)
Address
varchar(500)
               
  • First create a stored procedure to bind data in GridView as:
CREATE PROCEDURE BindEmpGrid_Sp

AS
BEGIN
SELECT * FROM Emp_Tb 

END

  • Now create a Stored Procedure to get/fetch the employee records based on search criteria.
Note: In have mentioned two stored procedure for the same purpose. You can use any of the two. The first one which is simple, demonstrates the use of “IF.. ELSE IF..ELSE (Nested Else if)” to execute different queries based on conditions and the second one demonstrates the use of Case statement for the same purpose.

CREATE  PROCEDURE SearchEmpRecords_Sp
                @SearchBy        varchar(50),
                @SearchVal       varchar(50)
AS
BEGIN
  IF @SearchBy = 'Emp Name'
                  BEGIN
                                SELECT * FROM Emp_Tb WHERE EmpName like @SearchVal + '%'
                  END
  ELSE IF @SearchBy = 'City'
                  BEGIN
                                SELECT * FROM Emp_Tb WHERE City like @SearchVal + '%'
                  END
  ELSE IF @SearchBy = 'Salary'
                  BEGIN
                                SELECT * FROM Emp_Tb WHERE Salary = @SearchVal
                  END
  ELSE
                  BEGIN
                                SELECT * FROM Emp_Tb
                  END
END 
    
----------------------------------------------------------------------------------------------------------

CREATE PROCEDURE SearchEmpRecords_Sp
                @SearchBy        varchar(50),
                @SearchVal       varchar(50)
AS
BEGIN

DECLARE @sql NVARCHAR(1000)
                SELECT @sql=CASE @SearchBy
                                 WHEN 'Emp Name' THEN
                                                'SELECT * FROM Emp_Tb WHERE EmpName LIKE '''+ @SearchVal +'%'''
                                 WHEN 'City'                     THEN
                                                 'SELECT * FROM Emp_Tb WHERE City LIKE '''+ @SearchVal +'%'''
                                 WHEN 'Salary'   THEN
                                                 'SELECT * FROM Emp_Tb WHERE Salary = ' + @SearchVal + ''
                 ELSE
                                 '(SELECT * FROM Emp_Tb)'
                 END
END
EXECUTE sp_executesql @sql

  • In the web.config file create the connection string to connect the asp.net web application with the Sql server database as:
<connectionStrings>
    <add name="con" connectionString="Data Source=Lalit;Initial Catalog=Emp_DB;Integrated Security=True"/>
  </connectionStrings>

Note: Replace the Data source and Initial Catalog (i.e. Database Name) as per your application.

Source Code:
  • In the design page(.aspx) design the page as:
  <div>   
    <fieldset style="width:415px;">
    <legend>Bind and Search records example in gridview</legend>  
    <table>
    <tr><td>Search By:
        <asp:DropDownList ID="ddlSearchBy" runat="server" AutoPostBack="True"
            onselectedindexchanged="ddlSearchBy_SelectedIndexChanged">
        <asp:ListItem Text="All"></asp:ListItem>
        <asp:ListItem Text="Emp Name"></asp:ListItem>       
        <asp:ListItem Text="Salary"></asp:ListItem>
        <asp:ListItem Text="City"></asp:ListItem>
        </asp:DropDownList>
    </td><td>
            <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
        </td><td>
            <asp:Button ID="btnSearch" runat="server" Text="Search"
                onclick="btnSearch_Click" />
        </td></tr>
    </table>
    <asp:GridView ID="grdEmp" runat="server" AllowSorting="True" EmptyDataText="No records found"
            CssClass="rowHover" RowStyle-CssClass="rowHover" ShowHeader="true"
            AutoGenerateColumns="False" 
            AllowPaging="True"
             onpageindexchanging="grdEmp_PageIndexChanging"
            PageSize="5"         
            CellPadding="4" ForeColor="#333333"
            GridLines="None" Width="100%">          
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
                   
         <Columns>                        
         <asp:BoundField HeaderText="Emp Name" DataField="EmpName" ItemStyle-HorizontalAlign="Center" />
         <asp:BoundField HeaderText="Age" DataField="Age" ItemStyle-HorizontalAlign="Center" />
         <asp:BoundField HeaderText="Salary" DataField="Salary" ItemStyle-HorizontalAlign="Center" />
         <asp:BoundField HeaderText="City" DataField="City" ItemStyle-HorizontalAlign="Center" />
         <asp:BoundField HeaderText="Address" DataField="Address" ItemStyle-HorizontalAlign="Center" />
        </Columns> 
                    <EditRowStyle BackColor="#999999" />
            <FooterStyle BackColor="#ffffff" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle CssClass="rowHover" BackColor="#F7F6F3" ForeColor="#333333"></RowStyle>
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#E9E7E2" />
            <SortedAscendingHeaderStyle BackColor="#506C8C" />
            <SortedDescendingCellStyle BackColor="#FFFDF8" />
            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />  
             <EmptyDataRowStyle Width = "550px" ForeColor="Red" Font-Bold="true"
   HorizontalAlign = "Center"/>  
        </asp:GridView>
         </fieldset>
    </div>   

C#.Net Code to bind and implement searching in GridView
  • In the code behind file (.aspx.cs) write the code as:
First include the following required namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        if (!Page.IsPostBack)
        {         
            BindEmpGrid();
            txtSearch.Enabled = false;
        }
    }

    private void BindEmpGrid()
    {      
       SqlDataAdapter adp = new SqlDataAdapter();
       DataTable dt = new DataTable();
        try
        {
            adp = new SqlDataAdapter("BindEmpGrid_Sp", con);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdEmp.DataSource = dt;
                grdEmp.DataBind();
            }
            else
            {
                grdEmp.DataSource = null;
                grdEmp.DataBind();
            }
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(thisthis.GetType(), "Message""alert('Error occured : " + ex.Message.ToString() + "');"true);                     
        }
        finally
        {
            dt.Clear();
            dt.Dispose();
            adp.Dispose();
            con.Close();
        }
    }

    protected void ddlSearchBy_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddlSearchBy.SelectedItem.Text == "All")
        {
            txtSearch.Text = string.Empty;
            txtSearch.Enabled = false;
        }
        else
        {
            txtSearch.Enabled = true;
            txtSearch.Text = string.Empty;
            txtSearch.Focus();
        }
    }   

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adp=new SqlDataAdapter();
        try
        {
            if (ddlSearchBy.SelectedItem.Text == "Emp Name")
            {
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());              
            }
            else if (ddlSearchBy.SelectedItem.Text == "City")
            {
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());              
            }
            else if (ddlSearchBy.SelectedItem.Text == "Salary")
            {
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());              
            }
            else
            {
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());             
            }
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(thisthis.GetType(), "Message""alert('Error occured : " + ex.Message.ToString() + "');"true);
        }
        finally
        {
            dt.Clear();
            dt.Dispose();
            cmd.Dispose();
            con.Close();
        }
    }

    private void getEmpRecords(string searchBy, string searchVal)
    {
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adp = new SqlDataAdapter();
        try
        {
            cmd = new SqlCommand("SearchEmpRecords1_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SearchBy", searchBy);
            cmd.Parameters.AddWithValue("@SearchVal", searchVal);
            adp.SelectCommand = cmd;
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdEmp.DataSource = dt;
                grdEmp.DataBind();
            }
            else
            {
                grdEmp.DataSource = dt;
                grdEmp.DataBind();
            }
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(thisthis.GetType(), "Message""alert('Error occured : " + ex.Message.ToString() + "');"true);
        }
        finally
        {
            dt.Clear();
            dt.Dispose();
            cmd.Dispose();
            con.Close();
        }
    }   

    protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdEmp.PageIndex = e.NewPageIndex;
        BindEmpGrid();
    }


VB.Net Code to bind and implement searching in GridView
  • In the design page(.aspx) design the page as shown above in Source Code section but replace the line
<asp:DropDownList ID="ddlSearchBy" runat="server" AutoPostBack="True"
            onselectedindexchanged="ddlSearchBy_SelectedIndexChanged">
with
<asp:DropDownList ID="ddlSearchBy" runat="server" AutoPostBack="True" >

Similarly replace the line
<asp:Button ID="btnSearch" runat="server" Text="Search"
                onclick="btnSearch_Click" />
with
<asp:Button ID="btnSearch" runat="server" Text="Search" />

and also remove the onpageindexchanging="grdEmp_PageIndexChanging"  from the Grid View source .
  • In the code behind file (.aspx.vb) write the code as:
First import the following namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

  Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
    Protected Sub Page_Load(sender As Object, e As System.EventArgsHandles Me.Load
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

        If Not Page.IsPostBack Then
            BindEmpGrid()
            txtSearch.Enabled = False
        End If
    End Sub

    Private Sub BindEmpGrid()
        Dim adp As New SqlDataAdapter()
        Dim dt As New DataTable()
        Try
            adp = New SqlDataAdapter("BindEmpGrid_Sp", con)
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                grdEmp.DataSource = dt
                grdEmp.DataBind()
            Else
                grdEmp.DataSource = Nothing
                grdEmp.DataBind()
            End If
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(MeMe.[GetType](), "Message""alert('Error occured : " & ex.Message.ToString() & "');"True)
        Finally
            dt.Clear()
            dt.Dispose()
            adp.Dispose()
            con.Close()
        End Try
    End Sub

    Protected Sub ddlSearchBy_SelectedIndexChanged(sender As Object, e As System.EventArgsHandles ddlSearchBy.SelectedIndexChanged
        If ddlSearchBy.SelectedItem.Text = "All" Then
            txtSearch.Text = String.Empty
            txtSearch.Enabled = False
        Else
            txtSearch.Enabled = True
            txtSearch.Text = String.Empty
            txtSearch.Focus()
        End If
    End Sub

    Protected Sub btnSearch_Click(sender As Object, e As System.EventArgsHandles btnSearch.Click
        Dim dt As New DataTable()
        Dim cmd As New SqlCommand()
        Dim adp As New SqlDataAdapter()
        Try
            If ddlSearchBy.SelectedItem.Text = "Emp Name" Then
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
            ElseIf ddlSearchBy.SelectedItem.Text = "City" Then
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
            ElseIf ddlSearchBy.SelectedItem.Text = "Salary" Then
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
            Else
                getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
            End If
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(MeMe.[GetType](), "Message""alert('Error occured : " & ex.Message.ToString() & "');"True)
        Finally
            dt.Clear()
            dt.Dispose()
            cmd.Dispose()
            con.Close()
        End Try
    End Sub

    Private Sub getEmpRecords(searchBy As String, searchVal As String)
        Dim dt As New DataTable()
        Dim cmd As New SqlCommand()
        Dim adp As New SqlDataAdapter()
        Try
            cmd = New SqlCommand("SearchEmpRecords1_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@SearchBy", searchBy)
            cmd.Parameters.AddWithValue("@SearchVal", searchVal)
            adp.SelectCommand = cmd
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                grdEmp.DataSource = dt
                grdEmp.DataBind()
            Else
                grdEmp.DataSource = dt
                grdEmp.DataBind()
            End If
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(MeMe.[GetType](), "Message""alert('Error occured : " & ex.Message.ToString() & "');"True)
        Finally
            dt.Clear()
            dt.Dispose()
            cmd.Dispose()
            con.Close()
        End Try
    End Sub

    Protected Sub grdEmp_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgsHandles grdEmp.PageIndexChanging
        grdEmp.PageIndex = e.NewPageIndex
        BindEmpGrid()
    End Sub

Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more  technical updates."
Previous
Next Post »

47 comments

Click here for comments
Anonymous
September 16, 2013 ×

Great work

Reply
avatar
admin
September 16, 2013 ×

Thanks for the appreciation..stay connected for more technical updates like this..

Reply
avatar
admin
Anonymous
September 23, 2013 ×

thanks for this post..great!!!great!!!great!!! Work!!! =)

Reply
avatar
admin
September 23, 2013 ×

i am glad you liked this article..keep reading for more useful updates..

Reply
avatar
admin
Anonymous
September 24, 2013 ×

Thanks.... From thailand ^^

Reply
avatar
admin
September 24, 2013 ×

your welcome..keep reading :)

Reply
avatar
admin
September 27, 2013 ×

Raghuvanshi sir,
I glad to take guidance from you and this place for my final project.
right now i'm working on my final project and for which i'm designing a bloodbank website and i want to impliment SMS facility in which as soon as one donor's blood transmits to any receiver Donor will get an message that your blood had fullfilled this person's life..

but this idea is just a idea i'm not knowing anything that how to impliment this idea so please give me your suggestion and please teach me about this concept.

Reply
avatar
admin
October 10, 2013 ×

it is very useful for me ........Great work .....

Reply
avatar
admin
October 11, 2013 ×

Thanks Reddappa Chinthamani..stay connected and keep reading :)

Reply
avatar
admin
Anonymous
October 13, 2013 ×

This is very useful,, thank you very much..

i have a problem about edit the grid view.. i cannot edit this gridview after the taking data into the grid using this codes .. can you please give me a solution to edit delete update the gridview...

Reply
avatar
admin
October 14, 2013 ×

Hi, You can read my article:
Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView in asp.net C#
http://www.webcodeexpert.com/2013/07/bindsaveeditupdatecanceldeletepaging.html

Reply
avatar
admin
Anonymous
October 14, 2013 ×

is this missing sp ?

BindEmpGrid_Sp

Reply
avatar
admin
October 14, 2013 ×

Hi, Thanks for notifying me the missing stored procedure..i have updated the article with that Stored procedure..stay connected and keep reading :)

Reply
avatar
admin
November 08, 2013 ×

How can I make the Header name links and the columns sortable

Reply
avatar
admin
November 08, 2013 ×

I'm populating my GridView from 2 different tables. I'm using the Select Case When Stored Procedure (SearchEmpRecords_Sp). Could you show me how to Join 2 Tables in the SELECT line.

Reply
avatar
admin
November 08, 2013 ×

Hello Michael Nacci..send me the tables script and specify what columns are required to be displayed in gridview..i will join them as per your requirement..

Reply
avatar
admin
November 14, 2013 ×

Is there a way to add a sorting to this Data Table. I would like to click on the header of the column to sort records?

Reply
avatar
admin
November 14, 2013 ×

Is there a way to add sorting to the column headers?

Reply
avatar
admin
November 14, 2013 ×

Hi Michael..i am working on implementing sorting in GridView and publish that by tomorrow..so keep reading..:)

Reply
avatar
admin
November 14, 2013 × This comment has been removed by the author.
avatar
admin
November 16, 2013 × This comment has been removed by the author.
avatar
admin
November 16, 2013 ×

Hi, Michael Nacci..read the article as per your requirement:
Sorting in Asp.Net GridView by column header in ascending descending order
http://www.webcodeexpert.com/2013/11/sorting-in-aspnet-gridview-by-column.html

Reply
avatar
admin
Anonymous
November 18, 2013 ×

Excellent man.....Bon courage.... Merci

Reply
avatar
admin
November 18, 2013 ×

thanks...keep reading..:)

Reply
avatar
admin
November 23, 2013 ×

useful demo.. thank you...

Reply
avatar
admin
November 23, 2013 ×

Your welcome pravin..it is always nice to hear that mt articles helped anyone...keep reading for more useful articles like this...

Reply
avatar
admin
Anonymous
November 30, 2013 ×

Thanks a lot. I'hv done my work seeing this articles very easily whatever i cant before.

Reply
avatar
admin
December 01, 2013 ×

You welcome..keep reading and stay tuned for more updates..:)

Reply
avatar
admin
Anonymous
December 01, 2013 ×

Sir I got a trouble like this
Compilation Error:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS1061: 'ASP.studentsearch_aspx' does not contain a definition for 'grdEmp_SelectedIndexChanged' and no extension method 'grdEmp_SelectedIndexChanged' accepting a first argument of type 'ASP.studentsearch_aspx' could be found (are you missing a using directive or an assembly reference?)
Line 31: <asp:GridView ID="grdEmp" runat="server" AllowSorting="True" EmptyDataText="No records found"

Reply
avatar
admin
December 02, 2013 ×

remove grdEmp_SelectedIndexChanged from the gridview source code..it will solve your problem..and let me know the results..

Reply
avatar
admin
Anonymous
January 28, 2014 ×

hi sir,
can we implement it with the date also?

Reply
avatar
admin
February 15, 2014 ×

hello sir,
how to fetch the data from gridview and display those values in textbox for edit and delete,Am using vb.net web application,sql.I need coding for that.

Reply
avatar
admin
February 15, 2014 ×

hi jaya, i will create an article as per your requirement and publish very soon..so stay connected and keep reading..:)

Reply
avatar
admin
Anonymous
March 05, 2014 ×

I got a trouble like this Both DataSource and DataSourceID are defined on 'grdEmpop'. Remove one definition.

Reply
avatar
admin
March 05, 2014 ×

In the .aspx page remove the DataSourceId from the gridview source..it will solve your problem..

Reply
avatar
admin
March 19, 2014 ×

Nice work. Thank you for sharing your valuable time and knowledge.

Reply
avatar
admin
March 19, 2014 ×

Hello Gaurav..thanks for appreciating my work..it is always nice to hear that someone found my articles helpful..stay connected and keep reading for more useful updates..:)

Reply
avatar
admin
Anonymous
April 10, 2014 ×

How are you supposed edit the record that you have searched for when the edit event needs to rebind again?

Here is error:

Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request.

Reply
avatar
admin
April 10, 2014 ×

thanks a lot

Reply
avatar
admin
April 10, 2014 ×

Hi.Thanks a lot.

Reply
avatar
admin
April 10, 2014 ×

Hi.i am glad you liked my article..stay connected and keep reading...:)

Reply
avatar
admin
April 10, 2014 ×

Your welcome java..:)

Reply
avatar
admin
Anonymous
May 16, 2014 ×

how to bind the data in grid view but the thing is tat i want to get the output in a grid view by clicking on to the radio button...pls help me out on tis

Reply
avatar
admin
August 30, 2014 ×

Hello Sir, Thank You Very Much,This article is very help full for my current project.

Reply
avatar
admin
September 02, 2014 ×

Your welcome Ranjan..stay connected and keep reading for more useful updates like this..

Reply
avatar
admin
September 08, 2014 ×

Thanks Mani Teja for your feedback.

Reply
avatar
admin

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. Out Of Topic Show Conversion CodeHide Conversion Code Show EmoticonHide Emoticon

Thanks for your comment