Asp.net: add, edit, delete and update record in Gridview control

In this article I am going to explain how to add, edit, delete and update record in Gridview using Asp.net

Description: 
In this article I am going to insert the record into data using Gridview. Edit, delete and update the record on RowCommand event of gridview.

Implementation:

Create a table Student_detail :

Gridview: add, edit, delete and update record in using Asp.net


HTML Markup of Gridview:
<asp:GridView ID="GridView1" runat="server" Width="550px" AutoGenerateColumns="False" ShowFooter="True" AllowPaging="True" DataKeyNames="Id"
     CellPadding="4" ForeColor="#333333" GridLines="None">
  <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:Label ID="lblname" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtname" runat="server" />
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Enter Name" ControlToValidate="txtname"></asp:RequiredFieldValidator>
            </FooterTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Fee">
            <ItemTemplate>
               <asp:Label ID="lblfee" runat="server" Text='<%# Eval("Fee") %>'></asp:Label>
            </ItemTemplate>
             <EditItemTemplate>
                <asp:TextBox ID="txtfee" runat="server" Text='<%# Eval("Fee") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtfee" runat="server" />
                 <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Enter Fee" ControlToValidate="txtfee"></asp:RequiredFieldValidator>
            </FooterTemplate>
             <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Class">
            <ItemTemplate>
               <asp:Label ID="lblclass" runat="server" Text='<%# Eval("Class") %>'></asp:Label>
            </ItemTemplate>
             <EditItemTemplate>
                <asp:TextBox ID="txtclass" runat="server" Text='<%# Eval("Class") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtclass" runat="server"></asp:TextBox>
                 <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Enter Class" ControlToValidate="txtclass"></asp:RequiredFieldValidator>
            </FooterTemplate>
             <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
           <asp:TemplateField HeaderText="Roll Number">
            <ItemTemplate>
                <asp:Label ID="lblrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:Label>
            </ItemTemplate>
                <EditItemTemplate>
                <asp:TextBox ID="txtrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtrollno" runat="server"></asp:TextBox>
                 <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Enter Roll number" ControlToValidate="txtrollno"></asp:RequiredFieldValidator>
            </FooterTemplate>
                <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
            <asp:TemplateField>
            <ItemTemplate>
             <asp:Button ID="btnedit" runat="server" Text="Edit" CommandName="Edit" CausesValidation="false"/><asp:Button ID="btndelete" runat="server" Text="Delete"  CommandName="Delete" CausesValidation="false" CssClass="btn"/>
            </ItemTemplate>
                <EditItemTemplate>
                    <asp:Button ID="btnupdate" runat="server" Text="Update"  CommandName="Update" CausesValidation="false"/><asp:Button ID="btncancel" runat="server" Text="Cancel"  CommandName="Cancel" CausesValidation="false" CssClass="btn" />
                </EditItemTemplate>
            <FooterTemplate>
                <asp:Button ID="btninsert" runat="server" Text="Insert Record" CommandName="Insert" />
            </FooterTemplate>
                <ItemStyle VerticalAlign="Top" Width="400px" />
        </asp:TemplateField>
    </Columns>
                       <EditRowStyle BackColor="#999999" />
                       <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                       <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                       <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                       <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                       <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                       <SortedAscendingCellStyle BackColor="#E9E7E2" />
                       <SortedAscendingHeaderStyle BackColor="#506C8C" />
                       <SortedDescendingCellStyle BackColor="#FFFDF8" />
                       <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>

Add the namespace to code file:
C#:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Bind the Gridview
Write the function  to bind the Gridview and call it in page load event of the page .
C#:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {     
        if (!IsPostBack)
        {
            BindGridview();
        }
    }
    public void BindGridview()
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter("Select * from Student_Detail", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
         }
        catch(Exception ex)
        {

        }
    }

VB:
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindGridview()
        End If
    End Sub
    Public Sub BindGridview()
        Try
            Dim adp As New SqlDataAdapter("Select * from Student_Detail", con)
            Dim dt As New DataTable()
            adp.Fill(dt)
            GridView1.DataSource = dt
            GridView1.DataBind()
        Catch ex As Exception
        End Try
    End Sub

Add, Delete and Update the record
Write the code on RowCommand event of Gridview to insert a new record and to Delete and update the existing record.
C#:
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Insert")
        {
            SqlCommand cmd = new SqlCommand("Insert into Student_Detail(Name,Fee,Class,RollNo) values(@name,@fee,@class,@rollno)", con);
            TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txtname");
            TextBox txtfee = (TextBox)GridView1.FooterRow.FindControl("txtfee");
            TextBox txtclass = (TextBox)GridView1.FooterRow.FindControl("txtclass");
            TextBox txtrollno = (TextBox)GridView1.FooterRow.FindControl("txtrollno");
            con.Open();
            cmd.Parameters.AddWithValue("@name", txtname.Text);
            cmd.Parameters.AddWithValue("@fee", txtfee.Text);
            cmd.Parameters.AddWithValue("@class", txtclass.Text);
            cmd.Parameters.AddWithValue("@rollno", txtrollno.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            Response.Write("<script type=\"text/javascript\">alert('Record Insert Successfully!!!');</script>");
            BindGridview();
            txtname.Text = string.Empty;
            txtfee.Text = string.Empty;
            txtclass.Text = string.Empty;
            txtrollno.Text = string.Empty;
        }
        if (e.CommandName == "Delete")
        {
            Button btn = (Button)e.CommandSource;
            GridViewRow gvrow = ((GridViewRow)btn.NamingContainer);
            int id = Convert.ToInt32(GridView1.DataKeys[gvrow.RowIndex].Value.ToString());
            SqlCommand cmd = new SqlCommand("Delete From Student_Detail where Id = @id", con);
            con.Open();
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();
            con.Close();
            Response.Write("<script type=\"text/javascript\">alert('Record Deleted Successfully!!!');</script>");
            BindGridview();
        }
        if (e.CommandName == "Update")
        {
            Button btn = (Button)e.CommandSource;
            GridViewRow gvrow = ((GridViewRow)btn.NamingContainer);
            int id = Convert.ToInt32(GridView1.DataKeys[gvrow.RowIndex].Value.ToString());
            TextBox txtname = (TextBox)GridView1.Rows[gvrow.RowIndex].FindControl("txtname");
            TextBox txtfee = (TextBox)GridView1.Rows[gvrow.RowIndex].FindControl("txtfee");
            TextBox txtclass = (TextBox)GridView1.Rows[gvrow.RowIndex].FindControl("txtclass");
            TextBox txtrollno = (TextBox)GridView1.Rows[gvrow.RowIndex].FindControl("txtrollno");
            SqlCommand cmd = new SqlCommand("Update Student_Detail set Name=@name, Fee=@fee,Class=@class, RollNo=@rollNo where Id = @id", con);
            con.Open();
            cmd.Parameters.AddWithValue("@name", txtname.Text);
            cmd.Parameters.AddWithValue("@fee", txtfee.Text);
            cmd.Parameters.AddWithValue("@class", txtclass.Text);
            cmd.Parameters.AddWithValue("@rollNo", txtrollno.Text);
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();
            GridView1.EditIndex = -1;
            con.Close();
            Response.Write("<script type=\"text/javascript\">alert('Record Updated Successfully!!!');</script>");
            BindGridview();
        }
    }
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {

    }

VB:
Protected Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles GridView1.RowCommand
        If e.CommandName = "Insert" Then
            Dim cmd As New SqlCommand("Insert into Student_Detail(Name,Fee,Class,RollNo) values(@name,@fee,@class,@rollno)", con)
            Dim txtname As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtname"), TextBox)
            Dim txtfee As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtfee"), TextBox)
            Dim txtclass As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtclass"), TextBox)
            Dim txtrollno As TextBox = DirectCast(GridView1.FooterRow.FindControl("txtrollno"), TextBox)
            con.Open()
            cmd.Parameters.AddWithValue("@name", txtname.Text)
            cmd.Parameters.AddWithValue("@fee", txtfee.Text)
            cmd.Parameters.AddWithValue("@class", txtclass.Text)
            cmd.Parameters.AddWithValue("@rollno", txtrollno.Text)
            cmd.ExecuteNonQuery()
            con.Close()
            Response.Write("<script type=""text/javascript"">alert('Insert Record Successfully!!!');</script>")
            txtname.Text = String.Empty
            txtfee.Text = String.Empty
            txtclass.Text = String.Empty
            txtrollno.Text = String.Empty
        End If
        If e.CommandName = "Delete" Then
            Dim btn As Button = DirectCast(e.CommandSource, Button)
            Dim gvrow As GridViewRow = DirectCast(btn.NamingContainer, GridViewRow)
            Dim id As Integer = Convert.ToInt32(GridView1.DataKeys(gvrow.RowIndex).Value.ToString())
            Dim cmd As New SqlCommand("Delete From Student_Detail where Id = @id", con)
            con.Open()
            cmd.Parameters.AddWithValue("@id", id)
            cmd.ExecuteNonQuery()
            con.Close()
            Response.Write("<script type=""text/javascript"">alert('Record Deleted Successfully!!!');</script>")
            BindGridview()
        End If
        If e.CommandName = "Update" Then
            Dim btn As Button = DirectCast(e.CommandSource, Button)
            Dim gvrow As GridViewRow = DirectCast(btn.NamingContainer, GridViewRow)
            Dim id As Integer = Convert.ToInt32(GridView1.DataKeys(gvrow.RowIndex).Value.ToString())
            Dim txtname As TextBox = DirectCast(GridView1.Rows(gvrow.RowIndex).FindControl("txtname"), TextBox)
            Dim txtfee As TextBox = DirectCast(GridView1.Rows(gvrow.RowIndex).FindControl("txtfee"), TextBox)
            Dim txtclass As TextBox = DirectCast(GridView1.Rows(gvrow.RowIndex).FindControl("txtclass"), TextBox)
            Dim txtrollno As TextBox = DirectCast(GridView1.Rows(gvrow.RowIndex).FindControl("txtrollno"), TextBox)
            Dim cmd As New SqlCommand("Update Student_Detail set Name=@name, Fee=@fee,Class=@class, RollNo=@rollNo where Id = @id", con)
            con.Open()
            cmd.Parameters.AddWithValue("@name", txtname.Text)
            cmd.Parameters.AddWithValue("@fee", txtfee.Text)
            cmd.Parameters.AddWithValue("@class", txtclass.Text)
            cmd.Parameters.AddWithValue("@rollNo", txtrollno.Text)
            cmd.Parameters.AddWithValue("@id", id)
            cmd.ExecuteNonQuery()
            GridView1.EditIndex = -1
            con.Close()
            Response.Write("<script type=""text/javascript"">alert('Record Updated Successfully!!!');</script>")
            BindGridview()
        End If
    End Sub
Protected Sub GridView1_RowDeleting(sender As Object, e As GridViewDeleteEventArgs) Handles GridView1.RowDeleting 
    End Sub

    Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs) Handles GridView1.RowUpdating 
    End Sub

PageIndexChange event of gridview
C#:
  protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        BindGridview();
    }

VB:
Protected Sub GridView1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles GridView1.PageIndexChanging
        GridView1.PageIndex = e.NewPageIndex
        BindGridview()
    End Sub

Cancel edit and enable editing mode
Write the below code to enable the edit mode (RowEditing event of gridview) and cancel the editing mode (RowCancelingEdit event of gridviw).
C#:
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        BindGridview();
    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        BindGridview();
    }

VB:
Protected Sub GridView1_RowEditing(sender As Object, e As GridViewEditEventArgs) Handles GridView1.RowEditing
        GridView1.EditIndex = e.NewEditIndex
        BindGridview()
    End Sub
Protected Sub GridView1_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
        GridView1.EditIndex = -1
        BindGridview()
    End Sub

Build, run the project.
 Result:
Gridview: add, edit, delete and update record in using Asp.net

   In this article we have learn how to Insert,edit, delete and update record in Gridview in asp.net (C#, VB). I hope you enjoyed this article. 

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget