Deleting Multiple Rows in a GridView


If you have used Hotmail or any other similar email client, you might have observed that  we have the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them. In this article, we will replicate this scenario for a gridview.

A gridview allows us to delete only a single row at a time. We will extend this functionality to select multiple rows and delete all of the selected rows in a single stroke. In this article, I assume that you are aware of creating asp.net web applications and have worked with gridview.

The sample makes use of the Northwind database. We will be pulling data from the Employee table. For this sample to work, drop all the Foreign Key relationships on the Employee Table. To do so, in Sql Server Management Studio, browse to the Northwind database and open the Employee table in design view. Right click in the Table designer on the right hand side and choose ‘Relationships’. Select all the relationships like FK_Orders_Employees,  FK_EmployeeTerritories_Employees etc and delete them. This step is necessary as we will get a constraint violation exception if we do not do so.

Once we are through with the task of removing the relationships in the Employee table, let us explore the steps to create a gridview with functionality to delete multiple rows at a time.

Perform the following steps :

Step 1: Create an .aspx page and add a GridView and a SqlDataSource control to it.

Step 2: Configure the connection of SqlDataSource to point to the Northwind database.  Create queries for the Select and Delete commands.

The resultant code will look similar as given below :

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"

    SelectCommand="SELECT EmployeeID, LastName, City FROM Employees"

    DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID"

    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" >

 

    <DeleteParameters>

        <asp:Parameter Name="EmployeeID" />

    </DeleteParameters>

</asp:SqlDataSource>

Step 3: Once the SqlDataSource has been configured, bind the gridview with this data source.

Step 4: To create a checkbox in each row, follow these steps:

1. Create a TemplateField inside the <Columns> to add custom content to each column.

2. Inside the TemplateField, create an ItemTemplate with a CheckBox added to it.

<asp:TemplateField> 

    <ItemTemplate> 

        <asp:CheckBox ID="chkRows" runat="server"/> 

    </ItemTemplate> 

</asp:TemplateField>

This will add a checkbox to each row in the grid.

Step 5: Add a button control, and rename it to btnMultipleRowDelete.The resultant markup in the design view will look similar to the code below :

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">

    <Columns> 

        <asp:TemplateField> 

            <ItemTemplate> 

                <asp:CheckBox ID="cbRows" runat="server"/> 

            </ItemTemplate> 

        </asp:TemplateField>

        <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" /> 

        <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" /> 

        <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /> 

    </Columns> 

</asp:GridView>

 

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" 

    SelectCommand="SELECT EmployeeID, LastName, City FROM Employees" 

    DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID" 

    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" > 

 

    <DeleteParameters> 

        <asp:Parameter Name="EmployeeID" /> 

    </DeleteParameters> 

</asp:SqlDataSource>

 

<asp:Button ID="btnMultipleRowDelete"  OnClick="btnMultipleRowDelete_Click" runat="server"  Text="Delete Rows" />

 

Our code will first loop through all the rows in the GridView. If a row is checked, the code retrieves the EmployeeID and passes the selected value to the Delete Command.

protected void btnMultipleRowDelete_Click(object sender, EventArgs e) 

{ 

    // Looping through all the rows in the GridView 

    foreach (GridViewRow row in GridView1.Rows) 

    { 

        CheckBox checkbox = (CheckBox)row.FindControl("cbRows");

 

        //Check if the checkbox is checked. 

        //value in the HtmlInputCheckBox's Value property is set as the

 

        //value of the delete command's parameter. 

        if (checkbox.Checked) 

        { 

            // Retreive the Employee ID 

            int employeeID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);

    

            // Pass the value of the selected Employye ID to the Delete //command. 

            SqlDataSource1.DeleteParameters["EmployeeID"].DefaultValue = employeeID.ToString(); 

            SqlDataSource1.Delete(); 

        }

    } 

} 

Hope this will Help you !!!

Advertisements

13 thoughts on “Deleting Multiple Rows in a GridView

  1. This code is very effective, but when i click checkbox and click delete Button it shows error message ‘Object reference not set to an instance of an object’

    Kindly help me out.

    Thanks & Regards

  2. when i click checkbox and click delete Button it shows error message

    “Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index”

  3. when i select all the check box in grid and click on delete command button then it shows
    ““Index was out of range. Must be non-negative and less than the size of the collection.”

    my code is
    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;

    public partial class Default4 :BaseUI
    {
    DataUtility objDUT = null;
    Utility objUT = null;
    DataTable dt = null;
    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {
    BindGrid();
    }
    }
    private void BindGrid()
    {
    dt = new DataTable();
    dt = getUsers(dt);
    if (dt.Rows.Count > 0)
    {

    //GridView1.DataSource = dt;
    //GridView1.DataBind();
    //GridView1.AllowPaging = true;
    users.DataSource = dt;
    users.DataBind();
    users.AllowPaging = true;

    }
    }

    //protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
    //{

    // objDUT = new DataUtility();
    // string strSql = null;
    // objUT = new Utility();
    // int result;

    // string autoid = GridView1.DataKeys[e.RowIndex].Value.ToString();
    // strSql = “delete from users where autoid='”+autoid+”‘”;
    // result = objDUT.ExecuteDML(strSql);

    // if (result > 0)
    // {
    // BindGrid();
    // Label1.Text = “data deleted from database”;
    // }
    // else
    // {
    // }

    // //string autoid = GridView1.DataKeys[e.RowIndex].Value.ToString();

    // //SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“ConnStr”].ToString());

    // //SqlCommand dCmd = new SqlCommand();

    //}

    private void UpdateEmployee(int userid, string username, string password)
    {
    //SqlCommand cmd = new SqlCommand(“UPDATE Employees SET EmpName='” + EmpName + “‘,Designation='” + Designation + “‘ WHERE EmpId=” + EmpId, conn);

    //try
    //{
    // if (conn.State == ConnectionState.Closed) conn.Open();
    // cmd.ExecuteNonQuery();
    //}
    //catch { }
    //finally
    //{
    // cmd.Dispose();
    // conn.Close();
    // conn.Dispose();
    //}
    objDUT = new DataUtility();
    string strSql = null;
    objUT = new Utility();
    string sql = “update users set username='” + username + “‘,password='” + password + “‘ where userid='” + userid + “‘”;
    int result = objDUT.ExecuteDML(sql);
    if (result > 0)
    //Label3.Text = “update successfull !!!”;
    lblMessage.Text = “update successfull !!!”;
    else
    //Label3.Text = “Failed !!!!!!”;
    lblMessage.Text = “Failed !!!”;

    }

    protected void users_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {

    }

    protected void users_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
    //dgEmployees.EditIndex = -1;
    //dgEmployees.ShowFooter = true;
    //BindGrid();
    users.EditIndex = -1;
    users.ShowFooter = true;
    BindGrid();
    }

    protected void users_RowEditing(object sender, GridViewEditEventArgs e)
    {
    //dgEmployees.EditIndex = e.NewEditIndex;
    //dgEmployees.ShowFooter = false;
    //BindGrid();
    users.EditIndex = e.NewEditIndex;
    users.ShowFooter = true;
    BindGrid();
    }

    protected void users_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
    //int empId = Convert.ToInt32(dgEmployees.DataKeys[e.RowIndex].Value);
    ////Find Text boxex
    //TextBox txtname = dgEmployees.Rows[e.RowIndex].FindControl(“txtEmpName”) as TextBox;
    //TextBox txtdesign = dgEmployees.Rows[e.RowIndex].FindControl(“txtDesignation”) as TextBox;
    //if (txtname != null && txtdesign != null)
    // UpdateEmployee(empId, txtname.Text.Trim(), txtdesign.Text.Trim());
    //dgEmployees.EditIndex = -1;
    //dgEmployees.ShowFooter = true;
    //BindGrid();
    int userid = Convert.ToInt32(users.DataKeys[e.RowIndex].Value);
    TextBox txtname = users.Rows[e.RowIndex].FindControl(“txtUserName”) as TextBox;
    TextBox txtpassword = users.Rows[e.RowIndex].FindControl(“txtPassword”) as TextBox;
    if (txtname != null && txtpassword != null)
    UpdateEmployee(userid, txtname.Text.Trim(), txtpassword.Text.Trim());
    users.EditIndex = -1;
    users.ShowFooter = true;
    BindGrid();
    }

    protected void btnMultipleRowDelete_Click(object sender, EventArgs e)
    {

    foreach (GridViewRow row in users.Rows)
    {
    CheckBox checkbox = (CheckBox)row.FindControl(“chkSelect”);
    if(checkbox.Checked && checkbox!=null)
    {
    objDUT = new DataUtility();
    string strSql = null;
    objUT = new Utility();
    int result;
    //int autoid = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);
    int userid = Convert.ToInt32(users.DataKeys[row.RowIndex].Value);
    userid = userid -1;
    //string id = GridView1.DataKeys[row.RowIndex].ToString();
    strSql = “delete from users where userid='” + userid + “‘”;
    result = objDUT.ExecuteDML(strSql);

    if (result > 0)
    {
    BindGrid();
    //Label1.Text = “data deleted from database”;
    }
    else
    {
    }

    }
    }
    Label1.Text = “data deleted…..”;
    }

    }

    1. hi deepak,

      ” Index was out of range. Must be non-negative and less than the size of the collection ” this error come in cenario whn

      int userid = Convert.ToInt32(users.DataKeys[row.RowIndex].Value);

      points to the rows tht out of range… so debug yr application n check which rows gerenare the error..

      n also what is the datafield value of datakeynames… why u hv subtracted -1 from it.. does tht cause error..

      ok hv a nice programming.. if any confusion.. send me yr code with aspx and .cs..

  4. public partial class GridViewStudent : System.Web.UI.Page
    {
    DBManager objDBManager = new DBManager();
    int Result = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {
    BindStudent();
    }
    }
    private void BindStudent()
    {
    DataSet ds = objDBManager.GetStudentRecord();
    gvStudent.DataSource = ds.Tables[0];
    gvStudent.DataBind();

    }
    protected void gvStudent_OnRowEditing(object sender, GridViewEditEventArgs e)
    {

    //Session[“GetStudentID”] = null;
    //objDBManager.StudentId = Convert.ToInt32(gvStudent.DataKeys[e.NewEditIndex].Value.ToString());
    //Session[“GetStudentID”] = objDBManager.StudentId;
    //Response.Redirect(“AddEditNewStudent.aspx”);

    }
    protected void gvStudent_OnSelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
    //Session[“GetStudentID”] = null;
    //objDBManager.StudentId = Convert.ToInt32(gvStudent.DataKeys[e.NewSelectedIndex].Value.ToString());
    //Result = objDBManager.DeleteStudentRecordbyID(objDBManager);
    //BindStudent();

    }
    protected void gvStudent_OnRowCommand(object sender, GridViewCommandEventArgs e)
    {
    if (e.CommandName.ToUpper() == “EDIT”)
    {
    Session[“GetStudentID”] = null;
    int index = Convert.ToInt32(e.CommandArgument);
    //GridViewRow row = (GridViewRow)gvStudent.DataKeys[index].Value;
    objDBManager.StudentId = Convert.ToInt32(index);
    Session[“GetStudentID”] = objDBManager.StudentId;
    Response.Redirect(“AddEditNewStudent.aspx”);
    // objDBManager.StudentId = Convert.ToInt32(gvStudent.DataKeys[e.CommandArgument]);

    }
    else if (e.CommandName.ToUpper() == “SELECT”)
    {
    Session[“GetStudentID”] = null;
    int index = Convert.ToInt32(e.CommandArgument);
    objDBManager.StudentId = Convert.ToInt32(index);
    Session[“GetStudentID”] = objDBManager.StudentId;
    Result = objDBManager.DeleteStudentRecordbyID(objDBManager);
    BindStudent();
    }
    else
    {

    }

    }
    protected void gvStudent_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
    if (e.Row.RowType == DataControlRowType.DataRow)

    if (e.Row.Cells[8].HasControls())
    {

    ImageButton imgBtnDelete = ((ImageButton)e.Row.Cells[8].Controls[1]);
    imgBtnDelete.Attributes.Add(“onclick”, “return confirm(‘Are You Sure To Delete This Student Information?’);”);

    }
    //if (e.Row.RowType == DataControlRowType.DataRow)
    //{

    // if (e.Row.Cells[6].HasControls())
    // {

    // ImageButton imgBtnMessage = ((ImageButton)e.Row.Cells[6].Controls[1]);
    // imgBtnMessage.Attributes.Add(“onclick”, “javascript:newWindowFocus();”);

    // }
    //}
    }

    protected void imgBtnMessage_Click(object sender, ImageClickEventArgs e)
    {

    }
    }

    1. u hv submitted this code for any reference or u hv some error in it.. specify some thing… i.e yr comments so tht i can understd why u hv submitted this code.. ok Happy Programming..

  5. I get the same error “Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index”.

    I’ve seen this error at other sites. Apparently when you use this, the gridview re-sorts the data after you have checked some or all of the records. I have not found a work around.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s