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

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 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
If you like this post than join us or share

56 comments:

Bubbles2 said...

Great article but the delete confirmation button does not display.
And the download code button does not work.


Bubbles2 said...

Hi

I love the article but I am tearing my hair out trying to get the confirm delete box to work.

can you send me the Code , the javascript to display the message box does not seem to run.

Thanks

Donncha.Finlay@gmail.com


computer said...

where's sample code attached


amiT jaiN said...

@bubbles2 , @Computer :

I've fixed the download link ,you can now download the source code
Do let me know if ur having further problems


Anonymous said...

Hi,

I am trying to do this, but my datasource is a view (which comes from 2 tables). When I get to the srting strUpdate= how should I proceed? There are no update parameters on my datasource. If I want to update the parent tables I have two datasources I must use. !? Help


vaseem said...

the download link for sample code is still not working ,can u plz fix it


amiT jaiN said...

@vaseem:

Download link is working now


Krishna said...

Amit ,

How did you generate the gif image ?
I've seen animated gif's before, but my question is, how do you capture your screen activity to gif?


amiT jaiN said...
This comment has been removed by the author.

Nishant said...

Hi Amit,

I needed this thing in my project and I really appreciate that I got it working because of you. I need one more help like can we put a Select all checkbox on the top so that we can update all the fields or delete them at single click.


Nishant said...

This comment has been removed by a blog administrator.


Nishant said...

This comment has been removed by a blog administrator.


Nishant said...

Hi Amit,
I really thank you for your help. I have a doubt, how can we add check all and Uncheck all button in this component so that we can edit or delete all the records in single click? Like I have added them but they are editing or deleting all the selected values. Please help me.


amiT jaiN said...

@Nishant:

Sorry for the late reply

You can read this for implementing CheckAll functionality in GridView


Anonymous said...

Awsome articles.....Very good work


amiT jaiN said...

@above:

Thanks for the appreciation :)


Anonymous said...

Thank you very much,it's exactly what i am looking for for long time.i converted the code in vb.net and when i run the project i receive this error
"Input string incorrect format" in the line of the update statement
----"Dim strUpdate As String = (("Update Details set Name = '" & strName & "',") + +" Location = '" + strLocation & "'") + +" WHERE ID ='" + strID & "'" & ";"---
please if you can help me


amiT jaiN said...

@Above:

Write this line as


Dim strUpdate As String = (("Update Details set Name = '" & strName & "', Location = '") + strLocation & "' WHERE ID ='") + strID & "'" & ";"

This whole line should be written in one line noly


Anonymous said...

thank you very much,i resolved the problem by working with parametres using simple update syntax.i found it in source code attached wich is named "3methodes and it seems working fine
thank you for your help.


Anonymous said...

This comment has been removed by a blog administrator.


Anonymous said...

Please how We Could Prevent Duplicate Record Insertion on Page Refresh ?


amiT jaiN said...

@Above:

Read my article at link given below to prevent duplicate record insertion on page refresh

Detect Browser refresh in ASP.NET to prevent duplicate record insertion


Anonymous said...

This comment has been removed by a blog administrator.


Anonymous said...

This comment has been removed by a blog administrator.


Anonymous said...

thank you very mmuch for all this work.

im trying to insert records from gridview to another table in my database
the question is : how we can before inserting verify wich records are already exist in that table and
if exists any one so we update it with new values extracted from the gridview ,and if there is any others wich no exist
so we add it to that table.


amiT jaiN said...

@Above:

For inserting records in database from gridview you can read my post mentioned below

Insert Edit Delete Records in GridView

You can write a method to check whether record exists ot not

Let me know if you need exact code for doing this


Anonymous said...

Could anybody tell me how to update the gridview where the column names are dynamic i.e. the column name varies according to the condition given


hanuman said...

AMit
It's an excellent post , Actually i am inserting on check change not updating .So everything is working fine.
I need the following:
1. Before inserting i need to give user a popup window or inline frame with multiline textbox stating "please give a specific reason".without reason it should not insert in the database.
which i am giving but not working absolutly perfect.
2.If record is already inserted then it should give check for the inserted record :"This record is already inserted".
which i am getting but only for the first row of gridview.
3.Once checked the checked boxes in the grid view they should be grayed or disabled.
for exsisting record i wrote stored proc with return value -1 & 1
so when i wrote function
private int insertdata()
{
int result=0;
for (int i = 0; i < GridView1.Rows.Count; i++)
{

where i am passing parameter value gridview.rows[i].text;
for existing record
}

}
it gives not all rows returns values.
please help...
it is v. imp. for me ,if possible please provide solution in .zip file.

Hanuman


Ido Shahar said...

Hello Amit, i odnt know why but the results come twice, one of theme i can edit and the other is a read only and doesn't updated.

how can i remove one of them ?? i did step by step as your code is shown.


Ido Shahar said...

Hello Amit,
I did step by step and it's working but on the browser are representable two tables that one of them editable and the second is not. how can i remove the second table?


William Jordan said...

Thank you for this post. You have saved me some time and effort.


Anonymous said...

Thanx for this article. It really helps.
But could you give an example with linqdatasource?
coz I seems to get some error in this part:

originalTable = ((System.Data.DataRowView)e.Row.DataItem).Row.Table.Copy();


Thanx Before!


Anonymous said...

hi
my update is not working any how i hv to manage it still monday.. send me code behind in c# and my error is on this line:
currentUserName = ((TextBox)row.FindControl
("txtName")).Text;
its saying Object reference not set to an instance of an object. how to get out of it. pls pls pls pls reply... use new keyword to create object instance and check to determine null before calling method.

pls send answer on this blog or on mail akanksha.pathak86@gmail.com


Anonymous said...

i forgot to say thanks in advance as my delete is working with multiple record selection pls help me for update button to work.


Anonymous said...

Object reference not set to an instance of an object. update button not working pls pls pls pls help.


Anonymous said...

Object reference not set to an instance of an object.







PLEASE help me and respond on my mail id

akanksha.pathak86@gmail.com


jackindas said...

I want to update Only updated feilds from Textbox by checking it to the exexting GridView to database


Anonymous said...

Hello Amitji i need your favour do let me know that is how to update selected rows in database using Gridview checkbox ? when i refresh the page then selected and updated to database that must be checked in checkbox , please suggest me

regards
Santa
santalai@rediffmail.com


Anonymous said...

Hi Amitji,
I need your favour please do let me know that is how to update selected rows in database using Gridview checkbox. and also i want to get seleted checkbox should be checked when i get refresh the page.
i shall be ever remain your grateful if u help out.

regards
Santa
santalai@rediffmail.com


kinjal said...

i cant do multiple record update in gridview plz help me. plz,plz


sreejani said...

i want to check multiple rows at a time in a certain table. the selected rows will be inserted into another table. can you help me how to code this??


Anonymous said...

it is good


rsvore said...

This is a great post, I was able to make it work in VB.net too. One thing how can I hide the ID column and still get the value for the update?


amiT jaiN said...

@rsvore : Please read post mentioned below to know how to hide gridview columns and show them in edit mode

Hide Gridview Columns


Ahmed said...

Hi, I came across this article and it is very helpful as I'm creating a page where there can be multiple updates or deletes in a gridview. There can also be the option to copy a row and insert a new one with the copied data. Do you happen to have an example of this? Thanks!


Anonymous said...

Hii,

Amit ur post is very useful bt iam facing 1 problem.
1.) Whenever i click on delete its give me exception of incorrect syntex near (,) , this is generating from for each loop and values of idcollection is not getting pass to string id in foreach loop . please tell me how to fix this.

Thanks is advance


amiT jaiN said...

@Above :

Can you please post your code? or mail me at csharpdotnetfreak@gmail.com , so that i can look into it.


Anonymous said...

Hi,
Really awsome post.
Sir can u help for 1 thing.

how do i check the gridview values before updating database . for both the columns "Name" and "Location".

Thanks.


Anonymous said...

Hii Amit thankyou for the reply,

iam not able to post code here it shows "Your HTML cannot be accepted:Tag is not allowed:"
hence ill mail you bt please rep me in this blog also ...
Thanks in advance.


Anonymous said...

Hii Amit i mailed u the code ,
please rep me .

Thanks in advance.


Anonymous said...

I am a beginner to .net.....i found this article very useful. Thanks


Sri Ranjani said...

Hi
Thank u so much. it was very usefull for me and worked well.am very happy.


Prabakaran said...

hi..

how to Edit on Grid into Grid...


Anonymous said...

Hi,
Good article....
But can you edit the data in name and location without any use of check box checked event.


amiT jaiN said...

@Above: I have not used checked event in second method, u can use method 2 if u don't wanna use checkbox.checked event


Anonymous said...

Hey thanx buddy
You did great job.. ;)


Find More Articles