Edit Update Delete Multiple Records/Rows Gridview With Checkbox

In this example i am going to how to Edit update delete multiple records or rows in Gridview with checkboxe to select rows to be edited with delete confirmation using JavaScript.

In my previous post i explained how to Delete multiple rows in Gridview with checkbox and delete confirmation Javascript




First Method

---

In this GridView is getting populated from database using SqlDataSource, I've put checkbox in first column of gridview using ItemTemplate and textbox in ItemTemplate to display records

Html SOURCE

<asp:GridView ID="GridView1" runat="server" 
                             AllowPaging="True" 
                             DataSourceID="SqlDataSource1" 
                             AutoGenerateColumns="false">
<Columns>
    <asp:TemplateField HeaderText="Select">
    <ItemTemplate>
    <asp:CheckBox ID="chkSelect" runat="server" 
                  AutoPostBack="true" 
             OnCheckedChanged="chkSelect_CheckedChanged"/>
    </ItemTemplate>
    
    </asp:TemplateField>
    <asp:BoundField DataField="ID" HeaderText="ID" 
                                   SortExpression="ID"/>
                                   
    <asp:TemplateField HeaderText="Name" 
                       SortExpression="Name">
    <ItemTemplate>
    <asp:TextBox ID="txtName" runat="server" 
                 Text='<%# Bind("Name") %>' ReadOnly="true" 
                 ForeColor="Blue" BorderStyle="none" 
                 BorderWidth="0px" >
    </asp:TextBox>
    </ItemTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="Location" 
                       SortExpression="Location">
    <ItemTemplate>
    <asp:TextBox ID="txtLocation" runat="server" 
                 Text='<%# Bind("Location") %>' 
                 ReadOnly="true" ForeColor="Blue" 
                 BorderStyle="none" BorderWidth="0px">
    </asp:TextBox>
    </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [ID], [Name], [Location] FROM [Details]" 
DeleteCommand="DELETE FROM Details WHERE (ID = @ID)" 
UpdateCommand="UPDATE [Details] SET [Name] = @Name, 
              [Location] = @Location WHERE [ID] = @ID">
        <DeleteParameters>
             <asp:Parameter Name="ID" />
        </DeleteParameters>
        <UpdateParameters>
                <asp:Parameter Name="Name" />
                <asp:Parameter Name="Location" />
                <asp:Parameter Name="ID" />
        </UpdateParameters>
        </asp:SqlDataSource><br />
        
<asp:Button ID="btnUpdate" runat="server" 
            OnClick="btnUpdate_Click" Text="Update" /><br />
            
<asp:Button ID="btnDelete" runat="server" 
            OnClick="btnDelete_Click" 
            OnClientClick="return DeleteConfirmation();"  
            Text="Delete" />

For Delete Confirmation write this JavaScript in head section of page . This script is called by delete button by specifying OnClientClick attribute in html code of button

<script type="text/javascript" language="javascript">
function DeleteConfirmation()
{
 if (confirm("Are you sure, 
     you want to delete selected records ?")==true)
 return true;
 else
 return false;
}
</script>

In the code behnd i've created a StringBuilder to store update commands separated by ; for records to be edited. than looping through gridview rows to find checked rows, then find the value in textbox using findControl.

C# CODE

public partial class _Default : System.Web.UI.Page 
{
 //Define global Connection String
string strConnection=ConfigurationManager.ConnectionStrings
                    ["ConnectionString"].ConnectionString;
    
protected void btnUpdate_Click(object sender, EventArgs e)
{
 //Create stringbuilder to store multiple DML statements 
 StringBuilder strSql = new StringBuilder(string.Empty);

 //Create sql connection and command
 SqlConnection con = new SqlConnection(strConnection);
 SqlCommand cmd = new SqlCommand();

//Loop through gridview rows to find checkbox 
//and check whether it is checked or not 
 for (int i = 0; i < GridView1.Rows.Count; i++)
 {
   CheckBox chkUpdate = (CheckBox)
      GridView1.Rows[i].Cells[0].FindControl("chkSelect");
   if (chkUpdate != null)
   {
    if (chkUpdate.Checked)
     {
    // Get the values of textboxes using findControl
     string strID = GridView1.Rows[i].Cells[1].Text;
     string strName = ((TextBox)
         GridView1.Rows[i].FindControl("txtName")).Text;

     string strLocation = ((TextBox)
         GridView1.Rows[i].FindControl("txtLocation")).Text;

     string strUpdate = 
         "Update Details set Name = '" + strName + "'," + 
         + " Location = '" + strLocation + "'" + 
         + " WHERE ID ='" + strID +"'" +";" ;
    //append update statement in stringBuilder 
     strSql.Append(strUpdate);
      }
   }
 }
   try
     {
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = strSql.ToString();
      cmd.Connection = con;
      con.Open();
      cmd.ExecuteNonQuery();
     }
   catch (SqlException ex)
     {
       string errorMsg = "Error in Updation";
       errorMsg += ex.Message;
       throw new Exception(errorMsg);
     }
   finally
     {
       con.Close();
     }
   UncheckAll();
      
}

This will update all records by connection to database only one time But this method is not considered good as it is vulnerable to sql injection so we can use Sql parameters instead

try
{
  string strUpdate = "Update Details set Name = @Name,"+ 
                   +" Location = @Location WHERE ID = @ID";
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = strUpdate.ToString();
  cmd.Parameters.Clear();
  cmd.Parameters.AddWithValue("@Name", strName);
  cmd.Parameters.AddWithValue("@Location", strLocation);
  cmd.Parameters.AddWithValue("@ID", strID);
  cmd.Connection = con;
  con.Open();
  cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
  string errorMsg = "Error in Updation";
  errorMsg += ex.Message;
  throw new Exception(errorMsg);
}
finally
{
   con.Close();
}

For deleting selected records at once write this code in click event of Delete button

protected void btnDelete_Click(object sender, EventArgs e)
{
//Create String Collection to store IDs of 
//records to be deleted 
StringCollection idCollection = new StringCollection();
string strID = string.Empty;

//Loop through GridView rows to find checked rows 
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
       CheckBox chkDelete = (CheckBox)GridView1.Rows[i].
                        Cells[0].FindControl("chkSelect");
            if (chkDelete != null)
            {
              if (chkDelete.Checked)
              {
               strID = GridView1.Rows[i].Cells[1].Text;
               idCollection.Add(strID);
              }
            }
        }
        if (idCollection.Count > 0)
        {
            //Call the method to Delete records 
            DeleteMultipleRecords(idCollection);

            // rebind the GridView
            GridView1.DataBind();   
        }
        else
        {
         lblMessage.Text = "Please select any row to delete";
        }
        
    }
private void DeleteMultipleRecords(StringCollection idCollection)
{
 //Create sql Connection and Sql Command
SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand();
string IDs = "";

    foreach (string id in idCollection)
    {
      IDs += id.ToString() + ",";
    }

    try
    {
     string test = IDs.Substring
                   (0, IDs.LastIndexOf(","));
     string sql = "Delete from Details"+ 
                  +" WHERE ID in (" + test + ")";
     cmd.CommandType = CommandType.Text;
     cmd.CommandText = sql;
     cmd.Connection = con;
     con.Open();
     cmd.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
     string errorMsg = "Error in Deletion";
     errorMsg += ex.Message;
     throw new Exception(errorMsg);
    }
    finally
    {
      con.Close();
    }
    }

Write this code in the CheckedChanged Event of CheckBox

protected void chkSelect_CheckedChanged
                            (object sender, EventArgs e)
    {
      CheckBox chkTest = (CheckBox)sender;
      GridViewRow grdRow = (GridViewRow)chkTest.NamingContainer;
      TextBox txtname = (TextBox)grdRow.FindControl
                                          ("txtName");
      TextBox txtlocation = (TextBox)grdRow.FindControl
                                        ("txtLocation");
      if (chkTest.Checked)
        {
            txtname.ReadOnly = false;
            txtlocation.ReadOnly = false;
            txtname.ForeColor = System.Drawing.Color.Black;
            txtlocation.ForeColor = System.Drawing.Color.Black;
        }
        else
        {
            txtname.ReadOnly = true;
            txtlocation.ReadOnly = true;
            txtname.ForeColor = System.Drawing.Color.Blue;
            txtlocation.ForeColor = System.Drawing.Color.Blue;
         }
    }

private void UncheckAll()
{
  foreach (GridViewRow row in GridView1.Rows)
  {
   CheckBox chkUncheck = (CheckBox)
                row.FindControl("chkSelect");
   TextBox txtname = (TextBox)
                  row.FindControl("txtName");
   TextBox txtlocation = (TextBox)
              row.FindControl("txtLocation");
   chkUncheck.Checked = false;
   txtname.ReadOnly = true;
   txtlocation.ReadOnly = true;
   txtname.ForeColor = System.Drawing.Color.Blue;
   txtlocation.ForeColor = System.Drawing.Color.Blue;
  }
}

Second Method:

---

This is better described here

public partial class _Default : System.Web.UI.Page 
{
    //Define global Connection String
string strConnection = ConfigurationManager.ConnectionStrings
                      ["ConnectionString"].ConnectionString;
     
    private bool tableCopied = false;
    private DataTable originalTable;

    protected void GridView1_RowDataBound
                  (object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            if (!tableCopied)
            {
                originalTable = ((System.Data.DataRowView)
                         e.Row.DataItem).Row.Table.Copy();
                ViewState["originalValues"] = originalTable;
                tableCopied = true;
            }
        }
    }
  protected void btnUpdate_Click(object sender, EventArgs e)
  {
   originalTable = (DataTable)ViewState["originalValues"];
   foreach (GridViewRow row in GridView1.Rows)
        if(IsRowModified(row))
        {
            GridView1.UpdateRow(row.RowIndex,false);
        }
        tableCopied = false;
        GridView1.DataBind();
    }

    protected bool IsRowModified(GridViewRow row)
    {
        int currentID;
        string currentName;
        string currentLocation;

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

        currentName = ((TextBox)row.FindControl
                                      ("txtName")).Text;
        currentLocation = ((TextBox)row.FindControl
                                  ("txtLocation")).Text;

        System.Data.DataRow newRow = originalTable.Select
                (String.Format("ID = {0}", currentID))[0];

    if (!currentName.Equals(newRow["Name"].ToString())) 
    { return true; }
    if (!currentLocation.Equals(newRow["Location"].ToString())) 
    { return true; }

    return false;

    }

For records deletion

protected void btnDelete_Click(object sender, EventArgs e)
{
 originalTable = (DataTable)ViewState["originalValues"];
 foreach (GridViewRow row in GridView1.Rows)
   {
    CheckBox chkDelete = (CheckBox)row.FindControl
                                        ("chkSelect");
    if(chkDelete.Checked)
      {
      GridView1.DeleteRow(row.RowIndex);
      }
   }
       tableCopied = false;
       GridView1.DataBind();
}

Download the sample code attached



Other Posts:
1. Populating dropdown based on the selection of first drop down in DetailsView using FindControl and ItemTemplate
2. Data Transfer using Query String Session cookies and cross page posting
3. Data Transfer Using Cookies Session Variables Cross page posting and QueryStrings in ASP.NET
4. Cross page posting, Submit Form and Server.Transfer methods
5. Pouplating Multiple DetailsView based on single GridView using DataKeyNames

Tweet

If you like this post than join us or share