Delete multiple records from asp.net gridview with checkbox selection | Preserving checkbox state on paging | Check/uncheck all checkbox in gridview

Delete multiple records from asp.net gridview with checkbox selection | Preserving checkbox state on paging | Check/uncheck all checkbox in gridview

Introduction: In this article i will explain How to delete multiple selected records/items from GridView based on Checkbox selection while paging is enabled in asp.net with both the C# and VB.Net language and using stored procedure and Sql Server as a back end database.

Delete multiple records from gridview example
Click on the image to view enlarged demo

Description: Basically you will learn the following through this article.
  • How to bind GridView from sql server database table using stored procedure
  • How to implement Check/Uncheck All checkbox feature using javascript to delete all the records from the gridview
  • How to show/implement checkbox in gridview rows to delete the selected records.
  • How to preserve checkbox state on paging using viewstate
  • How to maintain checkbox selections when paging is enabled using viewstate.


Implementation: Let's create an asp.net application to see the work in action.
  • First of all create a database in Sql server e.g. Emp_DB and 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
decimal(10, 2)
City
varchar(100)
Address
varchar(500)
               
  • Create a stored procedure to fetch the employee records and bind the Grid View 

CREATE PROCEDURE BindEmpGrid_Sp            
AS
BEGIN
                SELECT * FROM Emp_Tb
END

  • Create a stored procedure to delete the employee record.

CREATE PROCEDURE DeleteEmpRecord_Sp  
                @EmpId INT
AS
BEGIN
                DELETE FROM Emp_Tb WHERE Emp_Id=@EmpId
END
  • In the web.config file create the connection string to connect the asp.net application with the Sql server database.

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

  • In the <Head> tag of the design page e.g. default.aspx, create or copy paste the following java script function to implement the check all/uncheck all checkbox on Gridview header.

<script type = "text/javascript">
        function grdHeaderCheckBox(objRef) {
            var grd = objRef.parentNode.parentNode.parentNode;
            var inputList = grd.getElementsByTagName("input");
            for (var i = 0; i < inputList.length; i++) {
                var row = inputList[i].parentNode.parentNode;
                if (inputList[i].type == "checkbox" && objRef != inputList[i]) {
                    if (objRef.checked) {
                        inputList[i].checked = true;
                    }
                    else {                      
                        inputList[i].checked = false;
                    }
                }
            }
        }
</script>

  • In the <Body> tag of the default.aspx ,design the page as:

Source Code:

<fieldset style="width:380px;">
    <legend>Delete multiple records from gridview example</legend>  
    <asp:GridView ID="grdEmp" runat="server" AllowSorting="True" ShowFooter="true" EmptyDataText="No records found"
            DataKeyNames="Emp_Id"  CssClass="rowHover" RowStyle-CssClass="rowHover" ShowHeader="true"
            AutoGenerateColumns="False" 
            AllowPaging="True"
             onpageindexchanging="grdEmp_PageIndexChanging"
            PageSize="5"         
            CellPadding="4" ForeColor="#333333"
            GridLines="None">          
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
                   
         <Columns>
         <asp:TemplateField HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">
        <HeaderTemplate>
            <asp:CheckBox ID="chkAll" runat="server" Text="All"
             onclick = "grdHeaderCheckBox(this);" />
        </HeaderTemplate>
        <ItemTemplate>
            <asp:CheckBox ID="chkRow" runat="server" />
        </ItemTemplate>
        <FooterTemplate>
            <asp:Button ID="btnDelete" runat="server" Text="Multiple Delete"
            OnClientClick="return confirm('Are you sure you want to delete selected records?')"
            onclick="btnDelete_Click" />
         </FooterTemplate>
    </asp:TemplateField>                      
         <asp:BoundField HeaderText="Emp Name" DataField="EmpName" />
         <asp:BoundField HeaderText="Age" DataField="Age" />
         <asp:BoundField HeaderText="Salary" DataField="Salary" />
         <asp:BoundField HeaderText="Address" DataField="Address" />        
        </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>

Note: In the very first line of the default.aspx page set the EnableEventValidation="false"
So that it look like <%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>

How to maintain the state of the checkbox while paging is enabled?
Suppose paging is enabled in the gridview and there are 10 records per page and when you select some records from first page and got to next page and select some more records. Now when you come back on first page then the selected checkbox are unchecked i.e. the state of the checkbox are lost. This is due to fact that checkbox does not maintain state on navigation if paging is enabled. So to save the selected checkbox of all the pages i have created two functions:

  • SaveSelectedData: This function is to retrieve the selected records i.e. the records that are checked by the checkbox, adds them to an ArrayList and then saves the ArrayList to ViewState.
  • SetSelectedData: This function is to restore the saved state of the checkboxes from the ViewState.


C#.Net code to Delete multiple records from asp.net grid view with check box selection
  • In the page behind file (default.aspx.cs) write the code as:

First of all include the required namespaces and write the code as:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

  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)
        {
            SaveSelectedData();           
        }
        BindEmpGrid();         
    }

    #region "Bind Employee records in GridView"
    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(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);                     
        }
        finally
        {          
            con.Close();
            adp.Dispose();
        }
    }
    #endregion

    #region "GridView Paging"
    protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {     
        grdEmp.PageIndex = e.NewPageIndex;     
        grdEmp.DataBind();
        SetSelectedData();    
    }
    #endregion

    private void SaveSelectedData()
    {
        ArrayList arrLst;
        //Check the ViewState. If it has data then add to arraylist
        if (ViewState["CheckedRecords"] != null)
            arrLst = (ArrayList)ViewState["CheckedRecords"];
        else
            arrLst = new ArrayList();
        //Find the "All" checkbox from the GridView Header
        CheckBox chkAll = (CheckBox)grdEmp.HeaderRow.Cells[0].FindControl("chkAll");
        for (int i = 0; i < grdEmp.Rows.Count; i++)
        {
            Int32 Emp_Id;
            //check if the "All" checkbox is checked or not
            if (chkAll.Checked)
            {
                // If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
                 Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
                if (!arrLst.Contains(Emp_Id))
                {
                    arrLst.Add(Emp_Id);
                }
            }
                // When "All" checkbox not checked
            else
            {
                //Find the checkbox from the GridView rows
                CheckBox chkRow = (CheckBox)grdEmp.Rows[i].Cells[0].FindControl("chkRow");
                //check if the checkbox inside the gridview rows is checked or not
                if (chkRow.Checked)
                {
                    // If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
                     Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
                    if (!arrLst.Contains(Emp_Id))
                    { 
                        //add Emp_id in Array list
                        arrLst.Add(Emp_Id);
                    }
                }
                // When checkbox inside gridview rows is not checked
                else
                {
                     Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
                    // if array list contains the Emp_Id
                    if (arrLst.Contains(Emp_Id))
                    {
                        //Remove Emp_id from Array list
                        arrLst.Remove(Emp_Id);
                    }
                }
            }
        }
        //Storing the contents of array list i.e. Emp_id into ViewState.
        ViewState["CheckedRecords"] = arrLst;
    }

    private void SetSelectedData()
    {
      //  int currentCount = 0;
        //Find the "All" checkbox from the GridView Header
        CheckBox chkAll = (CheckBox)grdEmp.HeaderRow.Cells[0].FindControl("chkAll");
        chkAll.Checked = true;
        // adding Checked records from ViewState to Arraylist
        ArrayList arrLst = (ArrayList)ViewState["CheckedRecords"];
        for (int i = 0; i < grdEmp.Rows.Count; i++)
        {
            //Find the checkbox from the GridView Rows
            CheckBox chkRow = (CheckBox)grdEmp.Rows[i].Cells[0].FindControl("chkRow");
            if (chkRow != null)
            {
                Int32 Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
                chkRow.Checked = arrLst.Contains(Emp_Id);
                if (!chkRow.Checked)
                {
                    chkAll.Checked = false;
                }
            }
        }
    }

    #region "Delete multiple selected records"
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand();
        try
        {
            SetSelectedData();
            grdEmp.AllowPaging = false;
            grdEmp.DataBind();
            ArrayList arrLst = (ArrayList)ViewState["CheckedRecords"];
            for (int i = 0; i < grdEmp.Rows.Count; i++)
            {
                Int32 Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
                if (arrLst.Contains(Emp_Id))
                {                   
                    cmd = new SqlCommand("DeleteEmpRecord_Sp", con);
                    cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = Emp_Id;
                    cmd.CommandType = CommandType.StoredProcedure;              
                    con.Open();                
                    cmd.ExecuteNonQuery();
                    arrLst.Remove(Emp_Id);
                    con.Close();
                }
            }
            ViewState["CheckedRecords"] = arrLst;
            grdEmp.AllowPaging = true;
            ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('Selected Records has been deleted successfully');", true);
            BindEmpGrid();           
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
        }
        finally
        {
            cmd.Dispose();
        }
    }
    #endregion    

VB.Net Code to Delete multiple records from asp.net gridview with checkbox selection
  • In the code behind file (default.aspx.vb) write the code as:

First import the following required namespaces and write the code as:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections

   Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

        If Page.IsPostBack Then
            SaveSelectedData()
        End If
        BindEmpGrid()
    End Sub

#Region "Bind Employee records in GridView"
    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(Me, Me.[GetType](), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", True)
        Finally
            con.Close()
            adp.Dispose()
        End Try
    End Sub
#End Region

#Region "paging in GridView"
    Protected Sub grdEmp_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdEmp.PageIndexChanging
        grdEmp.PageIndex = e.NewPageIndex
        grdEmp.DataBind()
        SetSelectedData()
    End Sub
#End Region

    Private Sub SaveSelectedData()
        Dim arrLst As ArrayList
        'Check the ViewState. If it has data then add to arraylist
        If ViewState("CheckedRecords") IsNot Nothing Then
            arrLst = DirectCast(ViewState("CheckedRecords"), ArrayList)
        Else
            arrLst = New ArrayList()
        End If
        'Find the "All" checkbox from the GridView Header
        Dim chkAll As CheckBox = DirectCast(grdEmp.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
        For i As Integer = 0 To grdEmp.Rows.Count - 1
            Dim Emp_Id As Int32
            'check if the "All" checkbox is checked or not
            If chkAll.Checked Then
                ' If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
                Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
                If Not arrLst.Contains(Emp_Id) Then
                    arrLst.Add(Emp_Id)
                End If
            Else
                ' When "All" checkbox not checked
                'Find the checkbox from the GridView rows
                Dim chkRow As CheckBox = DirectCast(grdEmp.Rows(i).Cells(0).FindControl("chkRow"), CheckBox)
                'check if the checkbox inside the gridview rows is checked or not
                If chkRow.Checked Then
                    ' If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
                    Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
                    If Not arrLst.Contains(Emp_Id) Then
                        'add Emp_id in Array list
                        arrLst.Add(Emp_Id)
                    End If
                Else
                    ' When checkbox inside gridview rows is not checked
                    Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
                    ' if array list contains the Emp_Id
                    If arrLst.Contains(Emp_Id) Then
                        'Remove Emp_id from Array list
                        arrLst.Remove(Emp_Id)
                    End If
                End If
            End If
        Next
        'Storing the contents of array list i.e. Emp_id into ViewState.
        ViewState("CheckedRecords") = arrLst
    End Sub

    Private Sub SetSelectedData()
        '  int currentCount = 0;
        'Find the "All" checkbox from the GridView Header
        Dim chkAll As CheckBox = DirectCast(grdEmp.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
        chkAll.Checked = True
        ' adding Checked records from ViewState to Arraylist
        Dim arrLst As ArrayList = DirectCast(ViewState("CheckedRecords"), ArrayList)
        For i As Integer = 0 To grdEmp.Rows.Count - 1
            'Find the checkbox from the GridView Rows
            Dim chkRow As CheckBox = DirectCast(grdEmp.Rows(i).Cells(0).FindControl("chkRow"), CheckBox)
            If chkRow IsNot Nothing Then
                Dim Emp_Id As Int32 = Convert.ToInt32(grdEmp.DataKeys(i).Value)
                chkRow.Checked = arrLst.Contains(Emp_Id)
                If Not chkRow.Checked Then
                    chkAll.Checked = False
                End If
            End If
        Next
    End Sub

    Protected Sub btnDelete_Click(sender As Object, e As System.EventArgs)
        Dim cmd As New SqlCommand()
        Try
            SetSelectedData()
            grdEmp.AllowPaging = False
            grdEmp.DataBind()
            Dim arrLst As ArrayList = DirectCast(ViewState("CheckedRecords"), ArrayList)
            For i As Integer = 0 To grdEmp.Rows.Count - 1
                Dim Emp_Id As Int32 = Convert.ToInt32(grdEmp.DataKeys(i).Value)
                If arrLst.Contains(Emp_Id) Then
                    cmd = New SqlCommand("DeleteEmpRecord_Sp", con)
                    cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = Emp_Id
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()
                    cmd.ExecuteNonQuery()
                    arrLst.Remove(Emp_Id)
                    con.Close()
                End If
            Next
            ViewState("CheckedRecords") = arrLst
            grdEmp.AllowPaging = True
            ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('Selected Records has been deleted successfully');", True)
            BindEmpGrid()
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", True)
        Finally
            cmd.Dispose()
        End Try

    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."
Read More
Next
« Prev Post
Previous
Next Post »

8 comments

Click here for comments
Unknown
admin
September 23, 2013 ×

very nice article..
can you please upload some snippets regarding various WCF hosting like IIS,WS,WAS hosting methods and on WCF RIA,REST services

Reply
avatar
Lalit Raghuvanshi
admin
September 23, 2013 ×

Hello asif khan..thanks for appreciating this article..read the following article as per your requirement:

How to create and consume WCF Services in asp.net ?
http://www.webcodeexpert.com/2013/04/how-to-create-and-consume-wcf-services.html

WCF Service to bind,insert,edit,update,delete from sql server database in asp.net C#
http://www.webcodeexpert.com/2013/08/wcf-service-to-bindinserteditupdatedele.html

Reply
avatar
Sloop John B 2013
admin
September 27, 2013 ×

Solution sis great but you get error message

Invalid postback or callback argument. Event validation is enabled using in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.


Set the following in the page directive

EnableEventValidation="false"

Thus in effect disabling security to prevent code injection , etc.

Do you have an option in VB whith code validation = true?

Reply
avatar
Unknown
admin
October 12, 2013 ×

hello sir i m fresher and i just started learning .net
i want to ask you that how to move from one page to another page in asp.net

Reply
avatar
Unknown
admin
October 12, 2013 ×

hello sir m new to asp.net
i want to ask you that how to move from one page to another page in asp.net using C#

Reply
avatar
Lalit Raghuvanshi
admin
October 13, 2013 ×

Hello Aniket ..you can use Response.Redirect("Anotherpage.aspx"); or Server.Transfer("Anotherpage.aspx"); for that purpose.

Read the article to know more about them:
Difference between Response.Redirect and Server.Transfer in asp.net
http://www.webcodeexpert.com/2013/03/difference-between-responseredirect-and.html

Reply
avatar
Abhimanyu Singh Rajput
admin
January 15, 2014 ×

Dear sir,
i want to create Dyanmic menu with sub menu.plz help me .
Thank u

Reply
avatar
Anonymous
admin
February 20, 2014 ×

use a hyperlink tool and give the href="page.aspx "

Reply
avatar

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

Subscribe to: Post Comments (Atom)