Google How to bind and implement search gridview records in asp.net | Use of If Else If in Sql server | Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

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."

45 comments :

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

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

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

      Delete
  3. Thanks.... From thailand ^^

    ReplyDelete
  4. 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.

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

    ReplyDelete
    Replies
    1. Thanks Reddappa Chinthamani..stay connected and keep reading :)

      Delete
  6. 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...

    ReplyDelete
    Replies
    1. 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

      Delete
    2. is this missing sp ?

      BindEmpGrid_Sp

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

      Delete
  7. How can I make the Header name links and the columns sortable

    ReplyDelete
  8. 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.

    ReplyDelete
    Replies
    1. 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..

      Delete
    2. Is there a way to add sorting to the column headers?

      Delete
  9. 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?

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

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. 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

    ReplyDelete
  13. Excellent man.....Bon courage.... Merci

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

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

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

      Delete
  16. 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"

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

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

    ReplyDelete
  19. 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.

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

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

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

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

    ReplyDelete
    Replies
    1. 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..:)

      Delete
  22. 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.

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

      Delete
  24. AnonymousMay 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

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

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

      Delete

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..