Edit Update Multiple Records/Rows In Gridview With Checkbox ASP.NET

2:21:00 am 0 Comments

In this example i am going to explain 2 different approaches to Edit update and delete multiple records or rows in Gridiew with checkboxes 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

Edit Update Multiple Records/Rows In Gridview With Checkbox ASP.NET


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

Unknown

0 comments:

Subscribe to: Post Comments (Atom)