Updating, Deleting records using GridView control

SheoNarayan
Posted by in ASP.NET category on for Beginner level | Views : 584623 red flag
Rating: 4.4 out of 5  
 10 vote(s)

In this article, I am going to explain how to manipulate data using GridView control. This article scope is limited to Updating and Deleting records using GridView and I am not using any readymade Data controls for that but manually writing all event methods. I will be using Sql objects directly into methods to keep the example simple and straight forward. In practical scenario you should use your existing architecture to populate and update the data.
In this article, I am going to take a simple database table that has following fields:

AutoID - Int, AutoIncrement, Primary Key
PageName - Varchar(50)
PageDescription - Varchar(500)
Active - Bit(1)
In this article, I am not going to explain the workings of Sql objects but focusing on the events and methods of the GridView that will help us in updating and deleting the records. Now let’s start with putting the GridView on .aspx page. Following is the code for the GridView.

GridView Code


<asp:Label ID="lblMessage" runat="Server" ForeColor="Red"></asp:Label>
<asp:GridView ID="GridView1" runat="Server" AutoGenerateColumns="False" BorderWidth="1"
DataKeyNames="AutoID" AutoGenerateEditButton="True" OnRowEditing="EditRecord"
OnRowCancelingEdit="CancelRecord" OnRowUpdating="UpdateRecord" CellPadding="4"
HeaderStyle-HorizontalAlign="left" OnRowDeleting="DeleteRecord" RowStyle-VerticalAlign="Top"
ForeColor="#333333" GridLines="None">
<Columns>
<asp:BoundField DataField="AutoID" HeaderText="AutoID" ReadOnly="True" />
<asp:TemplateField HeaderText="Page Name">
<ItemTemplate>
<%# Eval("PageName") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtPageName" runat="Server" Text='<%# Eval("PageName") %>' Columns="30"></asp:TextBox>
<asp:RequiredFieldValidator ID="req1" runat="Server" Text="*" ControlToValidate="txtPageName"></asp:RequiredFieldValidator>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Page Description">
<ItemTemplate>
<%# Eval("PageDescription") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtPageDesc" runat="Server" TextMode="MultiLine" Rows="10" Columns="50"
Text='<%# Eval("PageDescription") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="req2" runat="Server" Text="*" ControlToValidate="txtPageDesc"></asp:RequiredFieldValidator>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Active">
<ItemTemplate>
<%# Eval("Active") %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="dropActive" runat="server" SelectedValue='<%# Eval("Active").ToString().ToLower().Equals("true") ? "True" : "False" %>'>
<asp:ListItem Text="Yes" Value="True"></asp:ListItem>
<asp:ListItem Text="No" Value="False"></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete?">
<ItemTemplate>
<span onclick="return confirm('Are you sure to Delete the record?')">
<asp:LinkButton ID="lnkB" runat="Server" Text="Delete" CommandName="Delete"></asp:LinkButton>
</span>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" VerticalAlign="Top" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>




In the above code, I have kept AutoGenerateColumns as false so that GridView will not render all the fields of the data source automatically. I have specified DataKeyNames as AutoID, the primary key of the database table. Then I have several methods attached with following events

Get video of hundreds of ASP.NET Tips and Tricks -http://www.itfunda.com/aspnet-how-to-tips-and-tricks/Show/50 .

Events - Methods
OnRowEditing - EditRecord
OnRowCancelEdit - CancelRecord
OnRowUpdating - UpdateRecord
OnRowDeleting - DeleteRecord


As I set AutoGenerateColumns property to false so I am going to manually write the fields name that I need to appear in the way I want. So I have to specify how I want them to appear in the normal view and edit view. That’s why I have placed different templates like ItemTemplate (for normal view) and EditItemTemplate (for edit view). You can notice that I have kept RequiredFieldValidator too inside the EditItemTemplate as I want the textbox to be validated for empty data. In the same way you can put any kind of Validation controls inside EditItemTemplate to validate the form element while user will click Update link. For PageName and PageDescription field I want them to appear in the TextBox in edit view, so I have specified text property of TextBox as the field value in EditItemTemplate and for Active field, I want it to appear as the DropDownList so I have specified the SelectedValue property as the value of the Active field.

You may notice that the last column of the GridView is a Delete column that let user delete the record from database, so I have placed Delete link button inside the <span> tag and have specified onClick property as JavaScript confirm return value, so that when user clicks on it he/she sees a confirmation box whether he/she really wants to delete the record.

Till now our GridView is ready to be populated, now let’s write a method to populate the GridView.


Populating the GridView Control


protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}

/// <summary>
/// Bind data to the grid
/// </summary>
private void BindData()
{
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter dAd = new SqlDataAdapter("select * from mySampleTable", conn);
DataSet dSet = new DataSet();
try
{

dAd.Fill(dSet, "PagesData");
GridView1.DataSource = dSet.Tables["PagesData"].DefaultView;
GridView1.DataBind();
}
catch (Exception ee)
{
lblMessage.Text = ee.Message.ToString();
}
finally
{
dSet.Dispose();
dAd.Dispose();
conn.Close();
conn.Dispose();
}
}

To populate the GridView, I am calling BindData() method from Page_Load event after checking IsPostBack property of the page, so the GridView will only be populated when there is no postback on the page (when the page loads for the first time). This will make sure that BindData() method will not fire when you are going to edit, update or delete the record.

In BindData() method, I am getting the data from database using Sql objects and specifying the DataSource property of the GridView to the PagesData table of the DataSet.


Editing GridView Control


/// <summary>
/// fires when edit link is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void EditRecord(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}


When Edit link of the GridView will be clicked then OnRowEditing event will fire that will call EditRecord method. In this method, I am specifying the EditIndex property of the GridView to the NewEditIndex of the GridView and then calling BindData() method again to bind the data. Please note that when you will not bind the data again, GridView will not change in edit mode.



Cancel Editing GridView Control


/// <summary>
/// fires when cancel link is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void CancelRecord(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}

When Cancel link is clicked on GridView in edit mode, OnRowCancelingEdit event will fire that will call CancelRecord method. In this method, I am specifying the EditIndex property of the GridView to -1. As there is no row at -1 position so GridView will be shown in normal mode.


Updating GridView Control





/// <summary>
/// fires when update link is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void UpdateRecord(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];

int autoid = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
TextBox tPageName = (TextBox)row.FindControl("txtPageName");
TextBox tPageDesc = (TextBox)row.FindControl("txtPageDesc");
DropDownList dActive = (DropDownList)row.FindControl("dropActive");

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());
SqlCommand dCmd = new SqlCommand();
try
{
conn.Open();
dCmd.CommandText = "spUpdateData";
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.Add("@AutoID", SqlDbType.Int).Value = autoid;
dCmd.Parameters.Add("@PageName", SqlDbType.VarChar, 50).Value = tPageName.Text.Trim();
dCmd.Parameters.Add("@PageDescription", SqlDbType.VarChar, 500).Value = tPageDesc.Text.Trim();
dCmd.Parameters.Add("@Active", SqlDbType.Bit).Value = bool.Parse(dActive.SelectedValue);
dCmd.Connection = conn;
dCmd.ExecuteNonQuery();

lblMessage.Text = "Record Updated successfully.";


// Refresh the data
GridView1.EditIndex = -1;
BindData();
}
catch (SqlException ee)
{
lblMessage.Text = ee.Message;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}

}



When Update link of the GridView will be clicked (in edit mode), OnRowUpdating event will fire that will call UpdateRecord method. In this method, we have to get changed values of TextBox and DropDown list and also we need the primary key value for the record that is being edited. To get the Primary key, I have used DataKeys property of the GridView with the index of the row, being edited and stored into a integer variable after parsing. Now I have declared a GridViewRow variable named row and stored the current row being edited into it. With the help row variable, I am going to find all the controls placed under that row in edit mode using FindControl property. After finding the controls I am using them to pass their values to the parameter of the SqlCommand object that is ultimately updating the records into database. After that again, I am doing the same thing that I had done in CancelRecord method, I mean setting EditIndex property of the GridView to -1 and rebinding the data again by calling BindData() method.

Deleting records from GridView Control





/// <summary>
/// Delete records from database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
{
string autoid = GridView1.DataKeys[e.RowIndex].Value.ToString();

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());
SqlCommand dCmd = new SqlCommand();
try
{
conn.Open();
dCmd.CommandText = "spDeleteData";
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.Add("@AutoID", SqlDbType.Int).Value = Int32.Parse(autoid);
dCmd.Connection = conn;
dCmd.ExecuteNonQuery();

lblMessage.Text = "Record Deleted successfully.";

// Refresh the data
BindData();

}
catch (SqlException ee)
{
lblMessage.Text = ee.Message;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}

When Delete link of the GridView will be clicked, it will confirm the user whether they really want to Delete the record, if they will click OK, it will fire OnRowDeleting event of the GridView that will call DeleteRecord method. In this method again, I am getting the primary key value using DataKeys property of the GridView and using it to delete the records from the database. After successfully deleting records, again I am calling BindData() method to rebind the GridView.

Hope this article helps to begin working with GridView control. If you have a feedback or suggestion, please do write to me.

Enjoy and Happy Coding !!!
Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com

Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Syedshakeer on: 8/24/2009
Hi Sheo,
Where you had wrote the code for confirmation Message Box as 'Are you sure to delete the Record'.How you had called that one.
Waiting for your reply
Thank you
Posted by: SheoNarayan on: 8/24/2009
Hello Syed,

The code for Are you sure to Delete the record? in the first code block.

<span onclick="return confirm('Are you sure to Delete the record?')"><asp:LinkButton ID="lnkB" runat="Server" Text="Delete" CommandName="Delete"></asp:LinkButton>                            </span>


Hope this answer your question.
Posted by: Syedshakeer on: 8/24/2009
yes,it helps me lot......
Thank you
Posted by: Sampath549 on: 10/19/2010 | Points: 10
hello sir.. can u tel me where u have written the code for store procedures
Posted by: ThisIsMe! on: 3/18/2011 | Points: 25
Hello....Thanks for the really good article.
My question....
What would you do if you wanted to have an event trigger when you click on the "cancel" button during the delete process.
Example...I would like a label to clear. Right now I have a label turn to visible true and a message when someone either edits or deletes something successfully. When a person starts to delete another item and decides to cancel the delete, the old message from the previously edited or deleted item still shows.

thanks..
Posted by: Ztew on: 9/13/2011 | Points: 25
ya,its worked.but how can I use the button field for the edit and delete in the template field.suppose i used that controls,what kind of events could I used for edit successfully?
plz help me!
advance thanks for you:-]
Posted by: Vinay13mar on: 10/14/2012 | Points: 25
Posted by: Bennu on: 7/9/2014 | Points: 25
Hi Vincent De Paul,
Eval in the sense it allows to Read and Write operations,but when ever we use Bind like" <%# Bind("PageDescription") %>" it allows Read only modifications(Write) will not work.

Login to post response

Comment using Facebook(Author doesn't get notification)