ASP Snippets

Categories






Alerts

Free Alerts

Your email will always be
private and will not be shared.




Follow us on twitter.




Zoom In | Zoom Out


Author is awarded Most Valuable Professional award by Microsoft ASP/ASP.Net

Select and delete multiple rows in ASP.Net Gridview control

Author:Mudassar Khan

Here I am explaining how to delete multiple selected rows or records from ASP.net GridView. On many occasions there’s a need where one has to provide a user facility to delete multiple rows or records selected by the user using a single button.

This example takes into account ASP.Net GridView with paging enabled which means you can provide user to select records on multiple rows on multiple pages also and delete all selected rows or records on all the pages all at once.

So let us start with the tutorial initially you’ll have to download the Microsoft’s Northwind Sample Database using the link given below

Download SQL Server NorthWind Database

GridView Mark-up

Below is the HTML Mark-up of the ASP.Net GridView used for this tutorial.

<asp:GridView ID="gvAll" runat="server"

    AutoGenerateColumns = "false" Font-Names = "Arial"

    Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

    HeaderStyle-BackColor = "green" AllowPaging ="true"  

    OnPageIndexChanging = "OnPaging" DataKeyNames = "CustomerID"

    PageSize = "10" >

   <Columns>

    <asp:TemplateField>

        <HeaderTemplate>

            <asp:CheckBox ID="chkAll" runat="server"

             onclick = "checkAll(this);" />

        </HeaderTemplate>

        <ItemTemplate>

            <asp:CheckBox ID="chk" runat="server"

             onclick = "Check_Click(this)"/>

        </ItemTemplate>

    </asp:TemplateField>

    <asp:BoundField ItemStyle-Width = "150px" DataField = "ContactName"

       HeaderText = "Contact Name"/>

    <asp:BoundField ItemStyle-Width = "150px" DataField = "Country"

       HeaderText = "Country"/>

    <asp:BoundField ItemStyle-Width = "150px" DataField = "City"

       HeaderText = "City"/>

   </Columns>

   <AlternatingRowStyle BackColor="#C2D69B"  />

</asp:GridView>

<asp:HiddenField ID="hfCount" runat="server" Value = "0" />

<asp:Button ID="btnDelete" runat="server" Text="Delete Checked Records"

   OnClientClick = "return ConfirmDelete();" OnClick="btnDelete_Click" />

 

You’ll notice above I have added a template field with checkboxes also I have added a check all checkbox in the Header Template of the ASP.net GridView Control. There’s a hidden field to store the count of the selected records and a delete button to trigger the delete operation. Paging is enabled for the GridView and there’s OnPageIndexChanging event that will be described later. CustomerID is assigned to the DataKeyNames property of the GridView which will allow us to uniquely identify each row

 

Data Binding the ASP.Net GridView control

I have used the Customers table from the Northwind Database for this example. The Customers table has some relationships with other tables hence it will not allow delete directly without removing the foreign key references. Hence to make it simple will create a new table called TestCustomers and use that instead. To create a clone table execute the following query.

SELECT * INTO TestCustomers

FROM Customers

 

The above query will create a new table TestCustomers and copy all the records from Customers table into TestCustomers. Thus now we can use it for out tutorial. The following function is used to databind the ASP.Net GridView control

C#

private void BindGrid()

{

    string constr = ConfigurationManager

                .ConnectionStrings["conString"].ConnectionString;

    string query = "select * from TestCustomers";

    SqlConnection con = new SqlConnection(constr);

    SqlDataAdapter sda = new SqlDataAdapter(query, con);

    DataTable dt = new DataTable();

    sda.Fill(dt);

    gvAll.DataSource = dt;

    gvAll.DataBind();

}

 

VB.Net

Private Sub BindGrid()

    Dim constr As String = ConfigurationManager _

                    .ConnectionStrings("conString").ConnectionString()

    Dim query As String = "select * from TestCustomers"

    Dim con As New SqlConnection(constr)

    Dim sda As New SqlDataAdapter(query, con)

    Dim dt As New DataTable()

    sda.Fill(dt)

    gvAll.DataSource = dt

    gvAll.DataBind()

End Sub

 

Maintaining the state of Checkboxes while paging

This is the core part of this example since its job is to keep track of the selected rows irrespective of which page it belongs and also maintain the state of the checkboxes while pagination so that user selections are not lost when he navigates from one page to another.

To facilitate this i have made use of the following two functions

1. GetData

The GetData function simply retrieves the records for which the user has checked the checkbox, adds them to an ArrayList and then saves the ArrayList to ViewState

C#

private void GetData()

{

    ArrayList arr;

    if (ViewState["SelectedRecords"] != null)

        arr = (ArrayList)ViewState["SelectedRecords"];

    else

        arr = new ArrayList();

    CheckBox chkAll = (CheckBox)gvAll.HeaderRow

                        .Cells[0].FindControl("chkAll");

    for (int i = 0; i < gvAll.Rows.Count; i++)

    {

        if (chkAll.Checked)

        {

            if (!arr.Contains(gvAll.DataKeys[i].Value))

            {

                arr.Add(gvAll.DataKeys[i].Value);

            }

        }

        else

        {

            CheckBox chk = (CheckBox)gvAll.Rows[i]

                               .Cells[0].FindControl("chk");

            if (chk.Checked)

            {

                if (!arr.Contains(gvAll.DataKeys[i].Value))

                {

                    arr.Add(gvAll.DataKeys[i].Value);

                }

            }

            else

            {

                if (arr.Contains(gvAll.DataKeys[i].Value))

                {

                    arr.Remove(gvAll.DataKeys[i].Value);

                }

            }

        }

    }

    ViewState["SelectedRecords"] = arr;

}

 

VB.Net

Private Sub GetData()

    Dim arr As ArrayList

    If ViewState("SelectedRecords") IsNot Nothing Then

         arr = DirectCast(ViewState("SelectedRecords"), ArrayList)

    Else

         arr = New ArrayList()

    End If

    Dim chkAll As CheckBox = DirectCast(gvAll.HeaderRow _

                    .Cells(0).FindControl("chkAll"), CheckBox)

    For i As Integer = 0 To gvAll.Rows.Count - 1

       If chkAll.Checked Then

             If Not arr.Contains(gvAll.DataKeys(i).Value) Then

                 arr.Add(gvAll.DataKeys(i).Value)

             End If

       Else

             Dim chk As CheckBox = DirectCast(gvAll.Rows(i).Cells(0) _

                                            .FindControl("chk"), CheckBox)

             If chk.Checked Then

                If Not arr.Contains(gvAll.DataKeys(i).Value) Then

                     arr.Add(gvAll.DataKeys(i).Value)

                End If

             Else

                If arr.Contains(gvAll.DataKeys(i).Value) Then

                     arr.Remove(gvAll.DataKeys(i).Value)

                End If

             End If

       End If

    Next

    ViewState("SelectedRecords") = arr

End Sub

 

GetData is called in the Page Load event of the ASP.Net web page in the following way

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (IsPostBack)

        GetData();

    BindGrid();

}

 

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

    If IsPostBack Then

         GetData()

    End If

    BindGrid()

End Sub

 

2. SetData

The SetData function simply restores the saved state of the checkboxes from the ViewState

C#

private void SetData()

{

    int currentCount = 0;

    CheckBox chkAll = (CheckBox)gvAll.HeaderRow

                            .Cells[0].FindControl("chkAll");

    chkAll.Checked = true;

    ArrayList arr = (ArrayList)ViewState["SelectedRecords"];

    for (int i = 0; i < gvAll.Rows.Count; i++)

    {

        CheckBox chk = (CheckBox)gvAll.Rows[i]

                        .Cells[0].FindControl("chk");

        if (chk != null)

        {

            chk.Checked = arr.Contains(gvAll.DataKeys[i].Value);

            if (!chk.Checked)

                chkAll.Checked = false;

            else

                currentCount++;

        }

    }

    hfCount.Value = (arr.Count - currentCount).ToString(); 

}

 

VB.Net

Private Sub SetData()

  Dim currentCount As Integer = 0

  Dim chkAll As CheckBox = DirectCast(gvAll.HeaderRow _

                        .Cells(0).FindControl("chkAll"), CheckBox)

  chkAll.Checked = True

  Dim arr As ArrayList = DirectCast(ViewState("SelectedRecords") _

                                        , ArrayList)

  For i As Integer = 0 To gvAll.Rows.Count - 1

     Dim chk As CheckBox = DirectCast(gvAll.Rows(i).Cells(0) _

                                         .FindControl("chk"), CheckBox)

     If chk IsNot Nothing Then

         chk.Checked = arr.Contains(gvAll.DataKeys(i).Value)

         If Not chk.Checked Then

                chkAll.Checked = False

         Else

                currentCount += 1

         End If

     End If

  Next

  hfCount.Value = (arr.Count - currentCount).ToString()

End Sub

 

The SetData method is called up in the OnPageIndexChanging event of the ASP.Net GridView in the following way

C#

protected void OnPaging(object sender, GridViewPageEventArgs e)

{

    gvAll.PageIndex = e.NewPageIndex;

    gvAll.DataBind();

    SetData();

}

 

VB.Net

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)

    gvAll.PageIndex = e.NewPageIndex

    gvAll.DataBind()

    SetData()

End Sub

 

Deleting multiple selected rows

Now when the delete button is clicked by the user the following event if triggered

C#

protected void btnDelete_Click(object sender, EventArgs e)

{

    int count = 0;

    SetData();

    gvAll.AllowPaging = false;

    gvAll.DataBind(); 

    ArrayList arr = (ArrayList)ViewState["SelectedRecords"];

    count = arr.Count;

    for (int i = 0; i < gvAll.Rows.Count; i++)

    {

        if (arr.Contains(gvAll.DataKeys[i].Value))

        {

            DeleteRecord(gvAll.DataKeys[i].Value.ToString());

            arr.Remove(gvAll.DataKeys[i].Value);

        }

    }

    ViewState["SelectedRecords"] = arr;

    hfCount.Value = "0";

    gvAll.AllowPaging = true;

    BindGrid();

    ShowMessage(count);

}

 

VB.Net

Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As EventArgs)

    Dim count As Integer = 0

    SetData()

    gvAll.AllowPaging = False

    gvAll.DataBind()

    Dim arr As ArrayList = DirectCast(ViewState("SelectedRecords") _

                                    , ArrayList)

    count = arr.Count

    For i As Integer = 0 To gvAll.Rows.Count - 1

        If arr.Contains(gvAll.DataKeys(i).Value) Then

              DeleteRecord(gvAll.DataKeys(i).Value.ToString())

              arr.Remove(gvAll.DataKeys(i).Value)

        End If

    Next

    ViewState("SelectedRecords") = arr

    hfCount.Value = "0"

    gvAll.AllowPaging = True

    BindGrid()

    ShowMessage(count)

End Sub

 

In the above event handler I am simply looping through the GridView and checking if the record exists in the ArrayList, if the record exists then it is simply deleted using the DeleteRecord method. Finally I display the a JavaScript Alert Box to notify the user that the delete operation is finished using the ShowMessage method

 

DeleteRecord Method

The delete record method simply deletes the record from the database table based on CustomerID.

C#

private void DeleteRecord(string CustomerID)

{

    string constr = ConfigurationManager

                .ConnectionStrings["conString"].ConnectionString;

    string query = "delete from TestCustomers " +

                    "where CustomerID=@CustomerID";

    SqlConnection con = new SqlConnection(constr);

    SqlCommand cmd = new SqlCommand(query, con);

    cmd.Parameters.AddWithValue("@CustomerID", CustomerID);

    con.Open();

    cmd.ExecuteNonQuery();

    con.Close();

}

 

VB.Net

Private Sub DeleteRecord(ByVal CustomerID As String)

    Dim constr As String = ConfigurationManager _

                        .ConnectionStrings("conString").ConnectionString

    Dim query As String = "delete from TestCustomers where" & _

                                " CustomerID=@CustomerID"

    Dim con As New SqlConnection(constr)

    Dim cmd As New SqlCommand(query, con)

    cmd.Parameters.AddWithValue("@CustomerID", CustomerID)

    con.Open()

    cmd.ExecuteNonQuery()

    con.Close()

End Sub

 

ShowMessage Method

 The ShowMessage method simply displays the amount of records deleted through a JavaScript Alert

C#

private void ShowMessage(int count)

{

    StringBuilder sb = new StringBuilder();

    sb.Append("<script type = 'text/javascript'>");

    sb.Append("alert('");

    sb.Append(count.ToString());

    sb.Append(" records deleted.');");

    sb.Append("</script>");

    ClientScript.RegisterStartupScript(this.GetType(),

                    "script", sb.ToString());

}

 

VB.Net

Private Sub ShowMessage(ByVal count As Integer)

     Dim sb As New StringBuilder()

     sb.Append("<script type = 'text/javascript'>")

     sb.Append("alert('")

     sb.Append(count.ToString())

     sb.Append(" records deleted.');")

     sb.Append("</script>")

     ClientScript.RegisterStartupScript(Me.GetType(), _

                        "script", sb.ToString())

End Sub

 

Client Side Scripting

I have made use of some JavaScript methods for the following methods

1.  Check-all checkbox functionality

2.  Confirmation before delete.

 

Check-all checkbox functionality

Check-all checkbox functionality has already explained by me in past. You can simply refer my article

Using JavaScript with ASP.Net GridView Control

 

Confirmation before delete

It would be a great feature to display a confirm box to the user stating that these many rows or records selected by you will be deleted. If the user presses OK delete operation will be performed else not. The following JavaScript function does it for us.

<script type = "text/javascript">

    function ConfirmDelete()

    {

       var count = document.getElementById("<%=hfCount.ClientID %>").value;

       var gv = document.getElementById("<%=gvAll.ClientID%>");

       var chk = gv.getElementsByTagName("input");

       for(var i=0;i<chk.length;i++)

       {

            if(chk[i].checked && chk[i].id.indexOf("chkAll") == -1)

            {

                count++;

            }

       }

       if(count == 0)

       {

            alert("No records to delete.");

            return false;

       }

       else

       {

            return confirm("Do you want to delete " + count + " records.");

       }

    }

</script>

 

Screenshots

The following screenshots describe the working of the example we just finished.

1. Select multiple records on multiple pages for deletion


Select multiple rows, records on multiple pages in ASP.Net GridView for delete



2. Multiple Records deletion confirmation


Delete multiple rows in ASP.Net gridView. Confirmation before delete



3. Successful message after records deletion


Confirmation message after successful deletion of records in ASP.Net GridView



Downloads

With this the article comes to an end. Hope you liked it. You can download the sample code in VB.Net and C# using the download link below

DeleteSelectedRecordsFromGridView.zip (7.35 kb)

 

Posted: Oct 17 2009, 21:46 by Mudassar Khan | Comments (0) RSS comment feed |
Filed under: ASP.Net | C# | GridView | VB.Net

Views: 7917
Page copy protected against web site content infringement by Copyscape


If you like this article, help us grow by bookmarking this page on any social bookmarking site.
Bookmark and Share





Comments









Community News





Web Hosting SpotLight


Consulting


For consulting and work related queries click here.



Advertise


Advertise with us. For more details click here.


Suggestions


Please provide your valuable suggesstions here.

This Site is hosted on

Lunarpages.com Web Hosting