Gridview - Add, Edit and delete
In this article we create a Gridview from database and add some additional operations such a add, edit and delete data in the GridView control. In the previous article , we learned how to create a simple GridView at runtime .
Database
In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.
DownloadHere we connect the Stores table of Pubs database for these operations. You can see the structure of the table here.
After setting the database, create a new Asp.Net project and open the design view and write the following code in the aspx file.
Default.aspx
<!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 runat="server">
<title>Gridview Add, Edit and Delete</title>
<script type="text/javascript">
function deleteConfirm(pubid) {
var result = confirm('Do you want to delete ' + pubid + ' ?');
if (result) {
return true;
}
else {
return false;
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gridView" DataKeyNames="stor_id" runat="server"
AutoGenerateColumns="false" ShowFooter="true" HeaderStyle-Font-Bold="true"
onrowcancelingedit="gridView_RowCancelingEdit"
onrowdeleting="gridView_RowDeleting"
onrowediting="gridView_RowEditing"
onrowupdating="gridView_RowUpdating"
onrowcommand="gridView_RowCommand"
OnRowDataBound="gridView_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="stor_id">
<ItemTemplate>
<asp:Label ID="txtstorid" runat="server" Text='<%#Eval("stor_id") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblstorid" runat="server" width="40px" Text='<%#Eval("stor_id") %>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="instorid" width="40px" runat="server"/>
<asp:RequiredFieldValidator ID="vstorid" runat="server" ControlToValidate="instorid" Text="?" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="stor_name">
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%#Eval("stor_name") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtname" width="70px" runat="server" Text='<%#Eval("stor_name") %>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="inname" width="120px" runat="server"/>
<asp:RequiredFieldValidator ID="vname" runat="server" ControlToValidate="inname" Text="?" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="stor_address">
<ItemTemplate>
<asp:Label ID="lbladdress" runat="server" Text='<%#Eval("stor_address") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtaddress" width="70px" runat="server" Text='<%#Eval("stor_address") %>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="inaddress" width="110px" runat="server"/>
<asp:RequiredFieldValidator ID="vaddress" runat="server" ControlToValidate="inaddress" Text="?" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="city">
<ItemTemplate>
<asp:Label ID="lblcity" runat="server" Text='<%#Eval("city") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtcity" width="50px" runat="server" Text='<%#Eval("city") %>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="incity" width="60px" runat="server"/>
<asp:RequiredFieldValidator ID="vcity" runat="server" ControlToValidate="incity" Text="?" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="state">
<ItemTemplate>
<asp:Label ID="lblstate" runat="server" Text='<%#Eval("state") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtstate" width="30px" runat="server" Text='<%#Eval("state") %>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="instate" width="40px" runat="server"/>
<asp:RequiredFieldValidator ID="vstate" runat="server" ControlToValidate="instate" Text="?" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="zip">
<ItemTemplate>
<asp:Label ID="lblzip" runat="server" Text='<%#Eval("zip") %>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtzip" width="30px" runat="server" Text='<%#Eval("zip") %>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="inzip" width="40px" runat="server"/>
<asp:RequiredFieldValidator ID="vzip" runat="server" ControlToValidate="inzip" Text="?" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<EditItemTemplate>
<asp:Button ID="ButtonUpdate" runat="server" CommandName="Update" Text="Update" />
<asp:Button ID="ButtonCancel" runat="server" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<ItemTemplate>
<asp:Button ID="ButtonEdit" runat="server" CommandName="Edit" Text="Edit" />
<asp:Button ID="ButtonDelete" runat="server" CommandName="Delete" Text="Delete" />
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="ButtonAdd" runat="server" CommandName="AddNew" Text="Add New Row" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
<div >
<br />
<asp:Label ID="lblmsg" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
After created the design view and open the code behind and write the following code in the source file
C# Source Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
private SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
loadStores();
}
}
protected void loadStores()
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from stores", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
int count = ds.Tables[0].Rows.Count;
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gridView.DataSource = ds;
gridView.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gridView.DataSource = ds;
gridView.DataBind();
int columncount = gridView.Rows[0].Cells.Count;
lblmsg.Text = " No data found !!!";
}
}
protected void gridView_RowEditing(object sender, GridViewEditEventArgs e)
{
gridView.EditIndex = e.NewEditIndex;
loadStores();
}
protected void gridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string stor_id = gridView.DataKeys[e.RowIndex].Values["stor_id"].ToString();
TextBox stor_name = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtname");
TextBox stor_address = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtaddress");
TextBox city = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtcity");
TextBox state = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtstate");
TextBox zip = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtzip");
con.Open();
SqlCommand cmd = new SqlCommand("update stores set stor_name='" + stor_name.Text + "', stor_address='" + stor_address.Text + "', city='" + city.Text + "', state='" + state.Text + "', zip='" + zip.Text + "' where stor_id=" + stor_id, con);
cmd.ExecuteNonQuery();
con.Close();
lblmsg.BackColor = Color.Blue ;
lblmsg.ForeColor = Color.White ;
lblmsg.Text = stor_id + " Updated successfully........ ";
gridView.EditIndex = -1;
loadStores();
}
protected void gridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gridView.EditIndex = -1;
loadStores();
}
protected void gridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string stor_id = gridView.DataKeys[e.RowIndex].Values["stor_id"].ToString();
con.Open();
SqlCommand cmd = new SqlCommand("delete from stores where stor_id=" + stor_id, con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
loadStores();
lblmsg.BackColor = Color.Red;
lblmsg.ForeColor = Color.White ;
lblmsg.Text = stor_id + " Deleted successfully....... ";
}
}
protected void gridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string stor_id = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "stor_id"));
Button lnkbtnresult = (Button)e.Row.FindControl("ButtonDelete");
if (lnkbtnresult != null)
{
lnkbtnresult.Attributes.Add("onclick", "javascript:return deleteConfirm('" + stor_id + "')");
}
}
}
protected void gridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox instorid = (TextBox)gridView.FooterRow.FindControl("instorid");
TextBox inname = (TextBox)gridView.FooterRow.FindControl("inname");
TextBox inaddress = (TextBox)gridView.FooterRow.FindControl("inaddress");
TextBox incity = (TextBox)gridView.FooterRow.FindControl("incity");
TextBox instate = (TextBox)gridView.FooterRow.FindControl("instate");
TextBox inzip = (TextBox)gridView.FooterRow.FindControl("inzip");
con.Open();
SqlCommand cmd =
new SqlCommand(
"insert into stores(stor_id,stor_name,stor_address,city,state,zip) values('" + instorid.Text + "','" +
inname.Text + "','" + inaddress.Text + "','" + incity.Text + "','" + instate.Text + "','" + inzip.Text + "')", con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
loadStores();
lblmsg.BackColor = Color.Green;
lblmsg.ForeColor = Color.White ;
lblmsg.Text = instorid.Text + " Added successfully...... ";
}
else
{
lblmsg.BackColor = Color.Red;
lblmsg.ForeColor = Color.White;
lblmsg.Text = instorid.Text + " Error while adding row.....";
}
}
}
}
After created the design view and open the code behind and write the following code in the source file
VB.Net Source Code
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Private con As New SqlConnection("Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****")
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
loadStores()
End If
End Sub
Protected Sub loadStores()
con.Open()
Dim cmd As New SqlCommand("Select * from stores", con)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
Dim count As Integer = ds.Tables(0).Rows.Count
con.Close()
If ds.Tables(0).Rows.Count > 0 Then
gridView.DataSource = ds
gridView.DataBind()
Else
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow())
gridView.DataSource = ds
gridView.DataBind()
Dim columncount As Integer = gridView.Rows(0).Cells.Count
lblmsg.Text = " No data found !!!"
End If
End Sub
Protected Sub gridView_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
gridView.EditIndex = e.NewEditIndex
loadStores()
End Sub
Protected Sub gridView_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
Dim stor_id As String = gridView.DataKeys(e.RowIndex).Values("stor_id").ToString()
Dim stor_name As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtname"), TextBox)
Dim stor_address As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtaddress"), TextBox)
Dim city As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtcity"), TextBox)
Dim state As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtstate"), TextBox)
Dim zip As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtzip"), TextBox)
con.Open()
Dim cmd As New SqlCommand(Convert.ToString("update stores set stor_name='" + stor_name.Text + "', stor_address='" + stor_address.Text + "', city='" + city.Text + "', state='" + state.Text + "', zip='" + zip.Text + "' where stor_id=") & stor_id, con)
cmd.ExecuteNonQuery()
con.Close()
lblmsg.BackColor = Color.Blue
lblmsg.ForeColor = Color.White
lblmsg.Text = stor_id & Convert.ToString(" Updated successfully........ ")
gridView.EditIndex = -1
loadStores()
End Sub
Protected Sub gridView_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
gridView.EditIndex = -1
loadStores()
End Sub
Protected Sub gridView_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
Dim stor_id As String = gridView.DataKeys(e.RowIndex).Values("stor_id").ToString()
con.Open()
Dim cmd As New SqlCommand("delete from stores where stor_id=" + stor_id, con)
Dim result As Integer = cmd.ExecuteNonQuery()
con.Close()
If result = 1 Then
loadStores()
lblmsg.BackColor = Color.Red
lblmsg.ForeColor = Color.White
lblmsg.Text = stor_id + " Deleted successfully....... "
End If
End Sub
Protected Sub gridView_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim stor_id As String = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "stor_id"))
Dim lnkbtnresult As Button = DirectCast(e.Row.FindControl("ButtonDelete"), Button)
If lnkbtnresult IsNot Nothing Then
lnkbtnresult.Attributes.Add("onclick", (Convert.ToString("javascript:return deleteConfirm('") & stor_id) + "')")
End If
End If
End Sub
Protected Sub gridView_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
If e.CommandName.Equals("AddNew") Then
Dim instorid As TextBox = DirectCast(gridView.FooterRow.FindControl("instorid"), TextBox)
Dim inname As TextBox = DirectCast(gridView.FooterRow.FindControl("inname"), TextBox)
Dim inaddress As TextBox = DirectCast(gridView.FooterRow.FindControl("inaddress"), TextBox)
Dim incity As TextBox = DirectCast(gridView.FooterRow.FindControl("incity"), TextBox)
Dim instate As TextBox = DirectCast(gridView.FooterRow.FindControl("instate"), TextBox)
Dim inzip As TextBox = DirectCast(gridView.FooterRow.FindControl("inzip"), TextBox)
con.Open()
Dim cmd As New SqlCommand("insert into stores(stor_id,stor_name,stor_address,city,state,zip) values('" + instorid.Text + "','" + inname.Text + "','" + inaddress.Text + "','" + incity.Text + "','" + instate.Text + "','" + inzip.Text + "')", con)
Dim result As Integer = cmd.ExecuteNonQuery()
con.Close()
If result = 1 Then
loadStores()
lblmsg.BackColor = Color.Green
lblmsg.ForeColor = Color.White
lblmsg.Text = instorid.Text + " Added successfully...... "
Else
lblmsg.BackColor = Color.Red
lblmsg.ForeColor = Color.White
lblmsg.Text = instorid.Text + " Error while adding row....."
End If
End If
End Sub
End Class
Hope you have run the program successfully!!
Related Topics
- ASP.NET Simple GridView
- Sorting , Paging and AutoGenerateColumns
- ASP.NET GridView Editing
- ASP.NET GridView Delete
- DropDownList in GridView
- Create Gridview at runtime
- Gridview export to Excel
- Gridview export to CSV
- GridView summary on Footer
- Subtotal row in Gridview
- SubTotal and GrandTotal in GridView
- Create Gridview without database
- GridView from Stored Procedure
- How to create Gridview Popup Form
- How to create a Blank row in gridview
- Nested GridView in ASP.NET
- Freeze Gridview Header row, Scroll in GridView
- Auto Generate Row Number in GridView
- Image between GridView rows
- How to select a row in gridview
- Checkbox in ASP.NET GridView Control