Introduction :
Here I explain how to insert, edit, update and delete data in grid view using asp.net.
In this project I use some grid view events those are
1) Onrowcancelingedit
2) Onrowediting
3) Onrowupdating
4) Onrowcancelingedit
5) Onrowdeleting
In this grid view I need to write code to insert data into grid view after that I need to edit that grid view data and update it and if I want to delete the data in grid view we need to delete this record simply by click on delete button of particular row.Here I explain full procedure step by step
Step 1: create a connection string in web.config page
<connectionStrings>
<add name="conn" providerName="System.Data.SqlClient"
connectionString="Data Source=Sk-Pc;uid=sa;pwd=12345;database=droplist"></add>
</connectionStrings>
<add name="conn" providerName="System.Data.SqlClient"
connectionString="Data Source=Sk-Pc;uid=sa;pwd=12345;database=droplist"></add>
</connectionStrings>
Step 2: create database
set primary key column ID and auto increment by +1 using sql properties
Step 3: HTML Design code goes here
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Simple Add/Insert, Edit/Update, Delete Gridview Data Example </title>
<style type="text/css">
td a
{
padding: 10px;
}
.buttonnew1, li.pagenav-next, li.pagenav-prev, a.readon, p.readmore a
{
-webkit-transition: background 300ms ease-out;
-moz-transition: background 300ms ease-out;
-o-transition: background 300ms ease-out;
transition: background 300ms ease-out;
}
.buttonnew1, li.pagenav-next, li.pagenav-prev
{
margin: 0px;
margin-top: 0px;
margin-right: 8px;
color: #ffffff;
padding: 6px 19px 5px;
margin-top: 20px;
margin-bottom: 10px;
font-weight: bold;
border: medium none;
color: #FFF !important;
border-radius: 15px;
text-decoration: none;
float: left;
font-size: 0.9em;
height: 30px;
background: #2F71B3 none repeat scroll 0% 0%;
}
.buttonnew1:hover, li.pagenav-next:hover, li.pagenav-prev:hover, a.readon:hover, p.readmore:hover a
{
background: #333333;
text-decoration: underline;
}
#contact-form .buttonnew1
{
margin-top: 18px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="border: solid 3px #2F09B7; padding: 15px; margin-left: 400px; margin-top: 100px;
width: 500px;">
<h4 style="color: #2F09B7; font-size: large;">
Insert,Edit,Update,Delete in Gridview</h4>
<asp:Label ID="lblMsg" runat="server"></asp:Label>
<table style="width: 495px">
<tr>
<td style="color: #2F09B7; font-size: medium; font-weight: bold;">
Subject Id:
</td>
<td>
<asp:TextBox ID="txtSubjectId" runat="server" Enabled="false" />
</td>
</tr>
<tr>
<td style="color: #2F09B7; font-size: medium; font-weight: bold;">
Subject Name:
</td>
<td>
<asp:TextBox ID="txtSubjectName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvSubjectName" runat="server" Text="*" ControlToValidate="txtSubjectName"
ForeColor="Red" ValidationGroup="vgAdd" />
</td>
</tr>
<tr>
<td style="color: #2F09B7; font-size: medium; font-weight: bold;">
Marks:
</td>
<td>
<asp:TextBox ID="txtMarks" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvMarks" runat="server" Text="*" ControlToValidate="txtMarks"
ForeColor="Red" ValidationGroup="vgAdd" />
<asp:RegularExpressionValidator ID="revMarks" runat="server" ForeColor="Red" ValidationExpression="^[0-9]*$"
Text="*Numbers" ControlToValidate="txtMarks" ValidationGroup="vgAdd" />
</td>
</tr>
<tr>
<td style="color: #2F09B7; font-size: medium; font-weight: bold;">
Grade:
</td>
<td>
<asp:TextBox ID="txtGrade" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvGrade" runat="server" Text="*" ControlToValidate="txtGrade"
ForeColor="Red" ValidationGroup="vgAdd" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" Text="Insert"
ValidationGroup="vgAdd" CssClass="buttonnew1" />
<asp:Button ID="btnUpdate" runat="server" OnClick="btnUpdate_Click" Text="Update"
ValidationGroup="vgAdd" CssClass="buttonnew1" />
<asp:Button ID="btnDelete" runat="server" OnClick="btnDelete_Click" OnClientClick="return confirm('Are you sure you want to delete this data?')"
Text="Delete" ValidationGroup="vgAdd" CssClass="buttonnew1" />
<asp:Button ID="btnCancel" runat="server" OnClick="btnCancel_Click" Text="Cancel"
CausesValidation="false" CssClass="buttonnew1" />
</td>
</tr>
</table>
<br />
<asp:GridView ID="gvSubDetails" DataKeyNames="SubjectId" AutoGenerateColumns="False"
OnSelectedIndexChanged="gvSubDetails_SelectedIndexChanged" Width="500px" runat="server"
BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"
CellPadding="3">
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#2F71B3 " ForeColor="White" Font-Bold="true" Height="30" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lbtnSelect" runat="server" CommandName="Select" Text="Select" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="SubjectName">
<ItemTemplate>
<asp:Label ID="lblSubjectName" Text='<%#Eval("SubjectName") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Marks">
<ItemTemplate>
<asp:Label ID="lblMarks" Text='<%#Eval("Marks") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Grade">
<ItemTemplate>
<asp:Label ID="lblGrade" Text='<%#Eval("Grade") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<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>
</div>
</form>
</body>
</html>
Step 4: Add some header files
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System;
using System.Web.UI.WebControls;
Step 5: c# page code goes here
public partial class editupdt : System.Web.UI.Page
{
DataTable dt = new DataTable();
string conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
lblMsg.Text = "";
if (!IsPostBack)
{
BindSubjectData();
}
}
//call this method to bind gridview
private void BindSubjectData()
{
SqlConnection sqlCon = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM SubjectDetails";
cmd.Connection = sqlCon;
sqlCon.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
gvSubDetails.DataSource = dt;
gvSubDetails.DataBind();
sqlCon.Close();
}
//Insert click event to insert new record to database
protected void btnInsert_Click(object sender, EventArgs e)
{
bool IsAdded = false;
string SubjectName = txtSubjectName.Text.Trim();
int Marks = Convert.ToInt32(txtMarks.Text);
string Grade = txtGrade.Text;
using (SqlConnection sqlCon = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand())
{
//here added "@" to write continuous strind in new line
cmd.CommandText = @"INSERT INTO SubjectDetails(SubjectName,Marks,Grade)
VALUES(@SubjectName,@Marks,@Grade)";
cmd.Parameters.AddWithValue("@SubjectName", SubjectName);
cmd.Parameters.AddWithValue("@Marks", Marks);
cmd.Parameters.AddWithValue("@Grade", Grade);
cmd.Connection = sqlCon;
sqlCon.Open();
IsAdded = cmd.ExecuteNonQuery() > 0;
sqlCon.Close();
}
}
if (IsAdded)
{
lblMsg.Text = "'" + SubjectName + "' subject details added successfully!";
lblMsg.ForeColor = System.Drawing.Color.Green;
BindSubjectData();
}
else
{
lblMsg.Text = "Error while adding '" + SubjectName + "' subject details";
lblMsg.ForeColor = System.Drawing.Color.Red;
}
ResetAll();//to reset all form controls
}
//Update click event to update existing record from the gridview
protected void btnUpdate_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtSubjectId.Text))
{
lblMsg.Text = "Please select record to update";
lblMsg.ForeColor = System.Drawing.Color.Red;
return;
}
bool IsUpdated = false;
int SubjectID = Convert.ToInt32(txtSubjectId.Text);
string SubjectName = txtSubjectName.Text.Trim();
int Marks = Convert.ToInt32(txtMarks.Text);
string Grade = txtGrade.Text;
using (SqlConnection sqlCon = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand())
{
//here added "@" to write continuous strind in new line
cmd.CommandText = @"UPDATE SubjectDetails SET SubjectName=@SubjectName,
Marks=@Marks,Grade=@Grade WHERE SubjectID=@SubjectId";
cmd.Parameters.AddWithValue("@SubjectId", SubjectID);
cmd.Parameters.AddWithValue("@SubjectName", SubjectName);
cmd.Parameters.AddWithValue("@Marks", Marks);
cmd.Parameters.AddWithValue("@Grade", Grade);
cmd.Connection = sqlCon;
sqlCon.Open();
IsUpdated = cmd.ExecuteNonQuery() > 0;
sqlCon.Close();
}
}
if (IsUpdated)
{
lblMsg.Text = "'" + SubjectName + "' subject details updated successfully!";
lblMsg.ForeColor = System.Drawing.Color.Green;
}
else
{
lblMsg.Text = "Error while updating '" + SubjectName + "' subject details";
lblMsg.ForeColor = System.Drawing.Color.Red;
}
gvSubDetails.EditIndex = -1;
BindSubjectData();
ResetAll();//to reset all form controls
}
//Delete click event to delete selected record from the database
protected void btnDelete_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtSubjectId.Text))
{
lblMsg.Text = "Please select record to delete";
lblMsg.ForeColor = System.Drawing.Color.Red;
return;
}
bool IsDeleted = false;
int SubjectID = Convert.ToInt32(txtSubjectId.Text);
string SubjectName = txtSubjectName.Text.Trim();
using (SqlConnection sqlCon = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "DELETE FROM SubjectDetails WHERE SubjectId=@SubjectID";
cmd.Parameters.AddWithValue("@SubjectID", SubjectID);
cmd.Connection = sqlCon;
sqlCon.Open();
IsDeleted = cmd.ExecuteNonQuery() > 0;
sqlCon.Close();
}
}
if (IsDeleted)
{
lblMsg.Text = "'" + SubjectName + "' subject details deleted successfully!";
lblMsg.ForeColor = System.Drawing.Color.Green;
BindSubjectData();
}
else
{
lblMsg.Text = "Error while deleting '" + SubjectName + "' subject details";
lblMsg.ForeColor = System.Drawing.Color.Red;
}
ResetAll();//to reset all form controls
}
//Cancel click event to clear and reset all the textboxes
protected void btnCancel_Click(object sender, EventArgs e)
{
ResetAll();//to reset all form controls
}
protected void gvSubDetails_SelectedIndexChanged(object sender, EventArgs e)
{
txtSubjectId.Text = gvSubDetails.DataKeys[gvSubDetails.SelectedRow.RowIndex].Value.ToString();
txtSubjectName.Text = (gvSubDetails.SelectedRow.FindControl("lblSubjectName") as Label).Text;
txtMarks.Text = (gvSubDetails.SelectedRow.FindControl("lblMarks") as Label).Text;
txtGrade.Text = (gvSubDetails.SelectedRow.FindControl("lblGrade") as Label).Text;
//make invisible Insert button during update/delete
btnInsert.Visible = false;
}
//call to reset all form controls
private void ResetAll()
{
btnInsert.Visible = true;
txtSubjectId.Text = "";
txtSubjectName.Text = "";
txtMarks.Text = "";
txtGrade.Text = "";
}
}
Demo :
Thank you so much for this code .., is very useful for me
ReplyDeleteThis comment has been removed by the author.
ReplyDeletevery useful for my project....thanx
ReplyDeleteIts very good
ReplyDeleteThis code is very useful for me
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteCode is very useful its great
ReplyDelete