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

<asp:TemplateField HeaderText="Name">

<asp:Label ID="lblname" runat="server" Text='<%# Eval("Name") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtname" runat="server" />

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Enter Name" ControlToValidate="txtname"></asp:RequiredFieldValidator>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Fee">

<asp:Label ID="lblfee" runat="server" Text='<%# Eval("Fee") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtfee" runat="server" Text='<%# Eval("Fee") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtfee" runat="server" />

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Enter Fee" ControlToValidate="txtfee"></asp:RequiredFieldValidator>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Class">

<asp:Label ID="lblclass" runat="server" Text='<%# Eval("Class") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtclass" runat="server" Text='<%# Eval("Class") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtclass" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Enter Class" ControlToValidate="txtclass"></asp:RequiredFieldValidator>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Roll Number">

<asp:Label ID="lblrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtrollno" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Enter Roll number" ControlToValidate="txtrollno"></asp:RequiredFieldValidator>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField>

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

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

<asp:Button ID="btninsert" runat="server" Text="Insert Record" CommandName="Insert" />

</FooterTemplate>

</asp:TemplateField>

</Columns>

</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:

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.

Monday, August 3, 2015

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

No comments:

Post a Comment

Popular Posts

Labels