Bind, Update and delete data from Gridview in Asp.Net using C# and VB.net

In this article I will perform Edit, update and delete operation in Gridview. First of all I will bind the gridview with database. Then we will add event handler to perform tasks.

Description:

In previous articles I have explained  Implementation of Page Index or Paging in Gridview Control In asp.net (c# & Vb.net) And How to Populate or Fill Dataset with data from Database in asp.net using c# & Vb.net And Use Gridviews Template field to display data in asp.net(c# & vb.net) And Display data in Gridview using boundField in asp.net(c#, VB.net.

Follow these steps:

1.      Bind Gridview with database

2.      Add  events i.e.  RowCancelingEdit, RowDeleting, RowEditing, RowUpdating.

3.      Now write code to update and delete data from gridview as given below.

Implementation:Follow following steps

1.     Create a database i.e. “Blog”.Then create a table “Student_Info”.

Column Name	Datatype
Student_id	Int(Primary Key. So set Is Identity=True)
Student_Name	Varchar(500)
Age	int
Class	Varchar(50)

2.     Now insert some data in this table using “insert” command.

INSERT INTO student_info(Student_Name,Age,Class)VALUES('Akhsay',12, 'first') INSERT INTO student_info(Student_Name,Age,Class)VALUES('Raghav',25, 'M.Sc') INSERT INTO student_info(Student_Name,Age,Class)VALUES('aksht', 23, 'Bsc') INSERT INTO student_info(Student_Name,Age,Class)VALUES('ankit', 23, 'MBA') INSERT INTO student_info(Student_Name,Age,Class)VALUES('anil', 23, 'MCA')

3.     Stored Procedure:

  create proc [dbo].[Fill_Dataset]

     as

     select * from student_info

4.     Create Connection: Now create connection in  webcofig file as given below.

<connectionStrings> <addname="con"connectionString="Data Source=localhost; Initial Catalog=Blog; Integrated Security=true;"providerName="System.Data.SqlClient"/> </connectionStrings>

5.     GridView Design:

<fieldset style="width:280px"><legend><strong>Edit Update & Delete in Gridview</strong></legend>     <div>        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"             BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"             CellPadding="3" DataKeyNames="Student_ID" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" >             <Columns>                 <asp:TemplateField HeaderText="Student name">                     <EditItemTemplate>                         <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Student_Name") %>'></asp:TextBox>                     </EditItemTemplate>                     <ItemTemplate>                         <asp:Label ID="Label1" runat="server" Text='<%# Bind("Student_Name") %>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField HeaderText="Age">                     <EditItemTemplate>                         <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("age") %>'></asp:TextBox>                     </EditItemTemplate>                     <ItemTemplate>                         <asp:Label ID="Label2" runat="server" Text='<%# Bind("age") %>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:TemplateField HeaderText="Class">                     <EditItemTemplate>                         <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("class") %>'></asp:TextBox>                     </EditItemTemplate>                     <ItemTemplate>                         <asp:Label ID="Label3" runat="server" Text='<%# Bind("class") %>'></asp:Label>                     </ItemTemplate>                 </asp:TemplateField>                 <asp:CommandField ShowEditButton="True" />                 <asp:TemplateField ShowHeader="False">                     <ItemTemplate>                         <asp:LinkButton ID="LinkButton1" runat="server"  OnClientClick="javascript:return confirm('Do you really want to delete the Record?');" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton>                     </ItemTemplate>                 </asp:TemplateField>                 </Columns>             <FooterStyle BackColor="White" ForeColor="#000066" />             <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />             <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />             <RowStyle ForeColor="#000066" />             <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />             <SortedAscendingCellStyle BackColor="#F1F1F1" />             <SortedAscendingHeaderStyle BackColor="#007DBB" />             <SortedDescendingCellStyle BackColor="#CAC9C9" />             <SortedDescendingHeaderStyle BackColor="#00547E" />         </asp:GridView> <br /> <br />     </div>     </fieldset>

6.     ASP.NET code behind File using C#:

In code behind, add following code:

usingSystem.Data; usingSystem.Data.SqlClient; usingSystem.Configuration;

Now write code to Bind, update and Delete data from gridview:

public partial class gridview : System.Web.UI.Page {      SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);     protected void Page_Load(object sender, EventArgs e)      {         if (!IsPostBack)         {             Fill_Grid();         }     }     //Fetch data from database and bind to gridview     public void Fill_Grid()     {              if (con.State == ConnectionState.Open)         {             con.Close();         }         con.Open();         DataSet ds = new DataSet();         SqlCommand cmd = new SqlCommand();         cmd.Connection = con;         cmd.CommandText = "Fill_Dataset";         cmd.CommandType = CommandType.StoredProcedure;     SqlDataAdapter dataadapater = new SqlDataAdapter();     dataadapater.SelectCommand = cmd;         dataadapater.Fill(ds);         GridView1.DataSource = ds;         GridView1.DataBind();         cmd.Dispose();         con.Close();     }     //delete data from gridview     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)     {         int id = Convert.ToInt32( GridView1.DataKeys[e.RowIndex].Value.ToString());          if (con.State == ConnectionState.Open)          {              con.Close();          }          con.Open();          SqlCommand cmd = new SqlCommand();          cmd.Connection = con;          cmd.CommandText = "Delete from student_Info where student_id=" + id + "";          cmd.ExecuteNonQuery();          cmd.Dispose();          con.Close();          Fill_Grid();     }     //Edit data in gridview     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)     {         GridView1.EditIndex = e.NewEditIndex;         Fill_Grid();             }     //update data in gridview     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)     {         int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());         TextBox txtName = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox1");         TextBox txtAge = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");         TextBox txtClass = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox3");         if (con.State == ConnectionState.Open)         {             con.Close();                }         con.Open();         SqlCommand cmd = new SqlCommand();         cmd.Connection = con;         cmd.CommandText = "update student_info set Student_name=@name, age=@age, class=@class where student_id=@id";         cmd.Parameters.AddWithValue("@name", txtName.Text);         cmd.Parameters.AddWithValue("@age", txtAge.Text);         cmd.Parameters.AddWithValue("@class", txtClass.Text);         cmd.Parameters.AddWithValue("@id", id);         cmd.ExecuteNonQuery();         cmd.Dispose();                 con.Close();         GridView1.EditIndex = -1;         Fill_Grid();         Response.Write("<script> alert('Record updated successfully')</script>");     }     protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)     {         GridView1.EditIndex = -1;         Fill_Grid();     } }

VB.NET code behind file:

In code behind, add following code:

ImportsSystem.Data ImportsSystem.Data.SqlClient ImportsSystem.Configuration

Bind, Update and delete data from gridview:

Partial Public Class gridview     Inherits System.Web.UI.Page     Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)     Protected Sub Page_Load(sender As Object, e As EventArgs)         If Not IsPostBack Then             Fill_Grid()         End If     End Sub     'Fetch data from database and bind to gridview     Public Sub Fill_Grid()         If con.State = ConnectionState.Open Then             con.Close()         End If         con.Open()         Dim ds As New DataSet()         Dim cmd As New SqlCommand()         cmd.Connection = con         cmd.CommandText = "Fill_Dataset"         cmd.CommandType = CommandType.StoredProcedure         Dim dataadapater As New SqlDataAdapter()         dataadapater.SelectCommand = cmd         dataadapater.Fill(ds)         GridView1.DataSource = ds         GridView1.DataBind()         cmd.Dispose()         con.Close()     End Sub     'delete data from gridview     Protected Sub GridView1_RowDeleting(sender As Object, e As GridViewDeleteEventArgs)         Dim id As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Value.ToString())         If con.State = ConnectionState.Open Then             con.Close()         End If         con.Open()         Dim cmd As New SqlCommand()         cmd.Connection = con         cmd.CommandText = "Delete from student_Info where student_id=" + id + ""         cmd.ExecuteNonQuery()         cmd.Dispose()         con.Close()         Fill_Grid()     End Sub     'Edit data in gridview     Protected Sub GridView1_RowEditing(sender As Object, e As GridViewEditEventArgs)         GridView1.EditIndex = e.NewEditIndex         Fill_Grid()     End Sub     'update data in gridview     Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs)         Dim id As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Value.ToString())         Dim txtName As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("TextBox1"), TextBox)         Dim txtAge As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("TextBox2"), TextBox)         Dim txtClass As TextBox = DirectCast(GridView1.Rows(e.RowIndex).FindControl("TextBox3"), TextBox)         If con.State = ConnectionState.Open Then             con.Close()         End If         con.Open()         Dim cmd As New SqlCommand()         cmd.Connection = con         cmd.CommandText = "update student_info set Student_name=@name, age=@age, class=@class where student_id=@id"         cmd.Parameters.AddWithValue("@name", txtName.Text)         cmd.Parameters.AddWithValue("@age", txtAge.Text)         cmd.Parameters.AddWithValue("@class", txtClass.Text)         cmd.Parameters.AddWithValue("@id", id)         cmd.ExecuteNonQuery()         cmd.Dispose()         con.Close()         GridView1.EditIndex = -1         Fill_Grid()         Response.Write("<script> alert('Record updated successfully')</script>")     End Sub     Protected Sub GridView1_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs)         GridView1.EditIndex = -1         Fill_Grid()     End Sub End Class

Demo:

Use this code, create your own application and check result. You can use this code in any application by customizing it. If you have any doubt, you can ask by commenting in comment section.