Asp.Net Gridview - Add, Edit and delete
In this article, we aim to provide a comprehensive guide on creating a GridView component within a web application. Our focus will be on using data retrieved from a database to populate the GridView, enabling a dynamic and interactive user experience.
Add, Edit and delete
Building upon the foundational knowledge shared in a previous article, where we discussed the fundamentals of creating a basic GridView, we will now investigate into more advanced functionalities. Specifically, we will explore how to enhance the GridView by incorporating additional operations such as adding, editing, and deleting data directly within the control itself. By implementing these features, users will have the ability to interact with the GridView in a seamless and intuitive manner, facilitating efficient data management.
Throughout the article, we will provide step-by-step instructions and demonstrate the necessary code snippets to assist you in implementing these advanced functionalities. By following along with the tutorial, you will gain a deeper understanding of the GridView control's capabilities and learn how to utilize its full potential within your web application.
Database
To provide practical examples and demonstrations, this article utilizes Microsoft's Pubs database as a sample data source. You can obtain the Pubs database free of charge by downloading it from the following link.
DownloadFor performing the operations discussed in this article, we establish a connection to the "Stores" table within the Pubs database. Below, you can find the table's structure, which outlines the organization and layout of its columns and data.
Table Name: Stores
To ensure a clear understanding of the data and fields involved in the subsequent operations described in the article, let's refer to the table structure. By examining the table structure, you can gain insights into the underlying data and fields that will be utilized in the following operations.
After configuring the database settings, you should proceed by creating a new Asp.Net project. Once the project is set up, navigate to the design view of the desired ASPX file. In this visual editor, you can exercise your coding skills to craft the necessary code segments that will bring the desired functionality to life.
By carefully developing and implementing the appropriate lines of code within the ASPX file, you can shape the behavior and appearance of your web application. This is an opportunity to utilize your coding expertise to create a visually appealing and fully functional interface that aligns with your project requirements.
As you progress through the article, you will receive step-by-step guidance on how to integrate the identified data and fields into the code, ensuring a smooth and coherent implementation of the subsequent operations.
Gridview Add, Edit and delete
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>
Once you have successfully created the design view for your project, the next step entails investigating into the code behind. This essential process unlocks the door to the intricate inner workings of your application, where you will infuse it with the necessary logic and functionality.
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
- Asp.Net 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