by: Saravanan Arumugam
Date: 21/04/2013
16916
In
this article I’m going to explain how to insert delete edit and update records
in ASP.NET GridView using C#.
If we want to insert delete edit and
update records in gridview we should use following gridview events,
1.
onrowcommand
2.
onrowdeleting
3. onrowupdating
4.
onrowcancelingedit
5. onrowediting
Also we should use following template field in
GridView.
1.ItemTemplate :
ItemTemplate is used to
display records in grid view
2.EditItemTemplate
EditItemTemplate is used to
Edit/Update records in grid view
3.FooterTemplate
FooterTemplate is used to
insert new records in grid view
Table Design:
Column Name
|
Data Type
|
empid
|
varchar(50)
|
name
|
varchar(100)
|
designation
|
varchar(50)
|
city
|
varchar(50)
|
country
|
varchar(50)
|
Create table script:
CREATE TABLE
[dbo].[EmployeeDetails](
[empid] [varchar](50)
NULL,
[name]
[varchar](100) NULL,
[designation] [varchar](100) NULL,
[city]
[varchar](50) NULL,
[country] [varchar](50)
NULL
) ON [PRIMARY]
Designer
Source Code:
<html
xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
<table width="800px"
align="center">
<tr>
<td colspan="2" align="center"><b>Employee
Details</b></td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="gvEmployeeDetails"
runat="server"
Width="100%"
AutoGenerateColumns="false"
ShowFooter="true"
onrowcommand="gvEmployeeDetails_RowCommand"
onrowdeleting="gvEmployeeDetails_RowDeleting"
onrowupdating="gvEmployeeDetails_RowUpdating"
onrowcancelingedit="gvEmployeeDetails_RowCancelingEdit"
onrowediting="gvEmployeeDetails_RowEditing">
<Columns>
<asp:TemplateField HeaderText="Employee
ID">
<ItemTemplate>
<asp:Label ID="lblEmpID"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"empid") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblEditEmpID"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"empid") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddEmpID"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblName"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditName"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddName"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<ItemTemplate>
<asp:Label ID="lblDesignation"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"designation") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditDesignation"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"designation") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddDesignation"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="lblCity"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"city") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditCity"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"city") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCity"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblCountry"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"country") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditCountry"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"country") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCountry"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:ImageButton ID="imgbtnEdit"
runat="server"
CommandName="Edit"
ImageUrl="~/Images/icon-edit.png"
Height="32px"
Width="32px"/>
<asp:ImageButton ID="imgbtnDelete"
runat="server"
CommandName="Delete"
ImageUrl="~/Images/Delete.png"/>
</ItemTemplate>
<EditItemTemplate>
<asp:ImageButton ID="imgbtnUpdate"
runat="server"
CommandName="Update"
ImageUrl="~/Images/icon-update.png"/>
<asp:ImageButton ID="imgbtnCancel"
runat="server"
CommandName="Cancel"
ImageUrl="~/Images/icon-Cancel.png"/>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="lbtnAdd"
runat="server"
CommandName="ADD"
Text="Add"
Width="100px"></asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code
Behind:
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Xml;
using
System.Data;
using
System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo;Integrated
Security=True");
protected void
Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}
protected void
BindData()
{
DataSet ds = new
DataSet();
DataTable FromTable = new DataTable();
conn.Open();
string cmdstr = "Select
* from EmployeeDetails";
SqlCommand cmd = new
SqlCommand(cmdstr, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
cmd.ExecuteNonQuery();
FromTable = ds.Tables[0];
if (FromTable.Rows.Count > 0)
{
gvEmployeeDetails.DataSource = FromTable;
gvEmployeeDetails.DataBind();
}
else
{
FromTable.Rows.Add(FromTable.NewRow());
gvEmployeeDetails.DataSource = FromTable;
gvEmployeeDetails.DataBind();
int TotalColumns =
gvEmployeeDetails.Rows[0].Cells.Count;
gvEmployeeDetails.Rows[0].Cells.Clear();
gvEmployeeDetails.Rows[0].Cells.Add(new TableCell());
gvEmployeeDetails.Rows[0].Cells[0].ColumnSpan = TotalColumns;
gvEmployeeDetails.Rows[0].Cells[0].Text = "No
records Found";
}
ds.Dispose();
conn.Close();
}
protected void
gvEmployeeDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Label lblEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEmpID");
conn.Open();
string cmdstr = "delete
from EmployeeDetails where empid=@empid";
SqlCommand cmd = new
SqlCommand(cmdstr, conn);
cmd.Parameters.AddWithValue("@empid",
lblEmpID.Text);
cmd.ExecuteNonQuery();
conn.Close();
BindData();
}
protected void
gvEmployeeDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("ADD"))
{
TextBox txtAddEmpID = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddEmpID");
TextBox txtAddName =
(TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddName");
TextBox txtAddDesignation = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddDesignation");
TextBox txtAddCity = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCity");
TextBox txtAddCountry = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCountry");
conn.Open();
string cmdstr = "insert
into EmployeeDetails(empid,name,designation,city,country)
values(@empid,@name,@designation,@city,@country)";
SqlCommand cmd = new
SqlCommand(cmdstr, conn);
cmd.Parameters.AddWithValue("@empid",
txtAddEmpID.Text);
cmd.Parameters.AddWithValue("@name",
txtAddName.Text);
cmd.Parameters.AddWithValue("@designation",
txtAddDesignation.Text);
cmd.Parameters.AddWithValue("@city",
txtAddCity.Text);
cmd.Parameters.AddWithValue("@country",
txtAddCountry.Text);
cmd.ExecuteNonQuery();
conn.Close();
BindData();
}
}
protected void
gvEmployeeDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label lblEditEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEditEmpID");
TextBox txtEditName = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditName");
TextBox txtEditDesignation = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditDesignation");
TextBox txtEditCity = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCity");
TextBox txtEditCountry = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCountry");
conn.Open();
string cmdstr = "update
EmployeeDetails set
name=@name,designation=@designation,city=@city,country=@country where
empid=@empid";
SqlCommand cmd = new
SqlCommand(cmdstr, conn);
cmd.Parameters.AddWithValue("@empid",
lblEditEmpID.Text);
cmd.Parameters.AddWithValue("@name",
txtEditName.Text);
cmd.Parameters.AddWithValue("@designation",
txtEditDesignation.Text);
cmd.Parameters.AddWithValue("@city",
txtEditCity.Text);
cmd.Parameters.AddWithValue("@country",
txtEditCountry.Text);
cmd.ExecuteNonQuery();
conn.Close();
gvEmployeeDetails.EditIndex = -1;
BindData();
}
protected void
gvEmployeeDetails_RowCancelingEdit(object
sender, GridViewCancelEditEventArgs e)
{
gvEmployeeDetails.EditIndex = -1;
BindData();
}
protected void
gvEmployeeDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvEmployeeDetails.EditIndex = e.NewEditIndex;
BindData();
}
}
- See more at:
http://www.dotnetfox.com/articles/how-to-add-edit-delete-update-records-in-grid-view-Asp-Net-1003.aspx#sthash.ZxuZWlOl.dpuf
by:
Saravanan Arumugam
Date:
21/04/2013
Page views:
16916
In this article I’m going to explain how to insert
delete edit and update records in ASP.NET GridView using C#.
If we want to insert delete edit and update records in gridview we
should use following gridview events,
1.
onrowcommand
2.
onrowdeleting
3.
onrowupdating
4.
onrowcancelingedit
5. onrowediting
Also we should use following
template field in GridView.
1.ItemTemplate :
ItemTemplate is used
to display records in grid view
2.EditItemTemplate
EditItemTemplate is used to
Edit/Update records in grid view
3.FooterTemplate
FooterTemplate is used
to insert new records in grid view
Table Design:
Column Name
|
Data Type
|
empid
|
varchar(50)
|
name
|
varchar(100)
|
designation
|
varchar(50)
|
city
|
varchar(50)
|
country
|
varchar(50)
|
Create table script:
CREATE TABLE [dbo].[EmployeeDetails](
[empid]
[varchar](50) NULL,
[name]
[varchar](100) NULL,
[designation]
[varchar](100) NULL,
[city]
[varchar](50) NULL,
[country]
[varchar](50) NULL
) ON [PRIMARY]
Designer
Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
<table width="800px" align="center">
<tr>
<td colspan="2" align="center"><b>Employee Details</b></td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="gvEmployeeDetails" runat="server"
Width="100%"
AutoGenerateColumns="false" ShowFooter="true"
onrowcommand="gvEmployeeDetails_RowCommand"
onrowdeleting="gvEmployeeDetails_RowDeleting"
onrowupdating="gvEmployeeDetails_RowUpdating"
onrowcancelingedit="gvEmployeeDetails_RowCancelingEdit"
onrowediting="gvEmployeeDetails_RowEditing">
<Columns>
<asp:TemplateField HeaderText="Employee ID">
<ItemTemplate>
<asp:Label ID="lblEmpID"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"empid") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblEditEmpID"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"empid") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddEmpID"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblName"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditName"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddName"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<ItemTemplate>
<asp:Label ID="lblDesignation"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"designation") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditDesignation"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"designation") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddDesignation"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="lblCity"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"city") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditCity"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"city") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCity"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblCountry"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"country") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditCountry"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"country") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCountry"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:ImageButton ID="imgbtnEdit"
runat="server"
CommandName="Edit"
ImageUrl="~/Images/icon-edit.png"
Height="32px"
Width="32px"/>
<asp:ImageButton ID="imgbtnDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png"/>
</ItemTemplate>
<EditItemTemplate>
<asp:ImageButton ID="imgbtnUpdate"
runat="server"
CommandName="Update"
ImageUrl="~/Images/icon-update.png"/>
<asp:ImageButton ID="imgbtnCancel"
runat="server"
CommandName="Cancel"
ImageUrl="~/Images/icon-Cancel.png"/>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="lbtnAdd"
runat="server"
CommandName="ADD"
Text="Add"
Width="100px"></asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code
Behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
public partial class _Default :
System.Web.UI.Page
{
SqlConnection
conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo;Integrated
Security=True");
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
BindData();
}
}
protected void BindData()
{
DataSet
ds = new DataSet();
DataTable
FromTable = new DataTable();
conn.Open();
string
cmdstr = "Select * from EmployeeDetails";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
SqlDataAdapter
adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
cmd.ExecuteNonQuery();
FromTable = ds.Tables[0];
if
(FromTable.Rows.Count > 0)
{
gvEmployeeDetails.DataSource =
FromTable;
gvEmployeeDetails.DataBind();
}
else
{
FromTable.Rows.Add(FromTable.NewRow());
gvEmployeeDetails.DataSource =
FromTable;
gvEmployeeDetails.DataBind();
int
TotalColumns = gvEmployeeDetails.Rows[0].Cells.Count;
gvEmployeeDetails.Rows[0].Cells.Clear();
gvEmployeeDetails.Rows[0].Cells.Add(new TableCell());
gvEmployeeDetails.Rows[0].Cells[0].ColumnSpan = TotalColumns;
gvEmployeeDetails.Rows[0].Cells[0].Text = "No
records Found";
}
ds.Dispose();
conn.Close();
}
protected void gvEmployeeDetails_RowDeleting(object sender, GridViewDeleteEventArgs
e)
{
Label
lblEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEmpID");
conn.Open();
string
cmdstr = "delete from EmployeeDetails where
empid=@empid";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
cmd.Parameters.AddWithValue("@empid", lblEmpID.Text);
cmd.ExecuteNonQuery();
conn.Close();
BindData();
}
protected void gvEmployeeDetails_RowCommand(object sender, GridViewCommandEventArgs
e)
{
if
(e.CommandName.Equals("ADD"))
{
TextBox
txtAddEmpID = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddEmpID");
TextBox
txtAddName = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddName");
TextBox
txtAddDesignation = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddDesignation");
TextBox
txtAddCity = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCity");
TextBox
txtAddCountry = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCountry");
conn.Open();
string
cmdstr = "insert into
EmployeeDetails(empid,name,designation,city,country)
values(@empid,@name,@designation,@city,@country)";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
cmd.Parameters.AddWithValue("@empid", txtAddEmpID.Text);
cmd.Parameters.AddWithValue("@name", txtAddName.Text);
cmd.Parameters.AddWithValue("@designation", txtAddDesignation.Text);
cmd.Parameters.AddWithValue("@city", txtAddCity.Text);
cmd.Parameters.AddWithValue("@country", txtAddCountry.Text);
cmd.ExecuteNonQuery();
conn.Close();
BindData();
}
}
protected void gvEmployeeDetails_RowUpdating(object sender, GridViewUpdateEventArgs
e)
{
Label
lblEditEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEditEmpID");
TextBox
txtEditName = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditName");
TextBox
txtEditDesignation = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditDesignation");
TextBox
txtEditCity = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCity");
TextBox
txtEditCountry = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCountry");
conn.Open();
string
cmdstr = "update EmployeeDetails set
name=@name,designation=@designation,city=@city,country=@country where
empid=@empid";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
cmd.Parameters.AddWithValue("@empid", lblEditEmpID.Text);
cmd.Parameters.AddWithValue("@name", txtEditName.Text);
cmd.Parameters.AddWithValue("@designation",
txtEditDesignation.Text);
cmd.Parameters.AddWithValue("@city", txtEditCity.Text);
cmd.Parameters.AddWithValue("@country", txtEditCountry.Text);
cmd.ExecuteNonQuery();
conn.Close();
gvEmployeeDetails.EditIndex = -1;
BindData();
}
protected void gvEmployeeDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs
e)
{
gvEmployeeDetails.EditIndex = -1;
BindData();
}
protected void gvEmployeeDetails_RowEditing(object sender, GridViewEditEventArgs
e)
{
gvEmployeeDetails.EditIndex =
e.NewEditIndex;
BindData();
}
}
- See more at:
http://www.dotnetfox.com/articles/how-to-add-edit-delete-update-records-in-grid-view-Asp-Net-1003.aspx#sthash.ZxuZWlOl.dpuf
