If you are using a Gridview control in your application then you might want to add, update, and delete data rows through the GridView control in ASP.NET.
This article discusses the steps require to load data in a GridView, edit data and then delete data through the GridView control.
This is the aspx code:
First of all, let's create a UI with a GridView control in ASP.NET.
The ASPX code on the page looks like this. As you can see from there, I have a GridView control and it has formatting HTML. I also have TemplateField columns for EMPID, FNAME, and LNAME database columns. These are the column names in my database table.
This you will change for you depending on what database table and columns you are using.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="gridviewexample.aspx.cs"
Inherits="gridviewexample" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="grid1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" AllowPaging="true"
AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" OnPageIndexChanging="grid1_PageIndexChanging"
OnRowCancelingEdit="grid1_RowCancelingEdit" OnRowDeleting="grid1_RowDeleting"
OnRowEditing="grid1_RowEditing" OnRowUpdating="grid1_RowUpdating">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<RowStyle BackColor="White" ForeColor="#330099" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
<Columns>
<asp:TemplateField HeaderText="EMPID">
<ItemTemplate>
<asp:Label ID="lblempid" runat="server" Text='<%#Eval("empid") %>'></asp:Label></ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="FNAME">
<ItemTemplate>
<asp:TextBox ID="txtfname" runat="server" Text='<%#Eval("fname")%>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="LNAME">
<ItemTemplate>
<asp:TextBox ID="txtlname" runat="server" Text='<%#Eval("lname")%>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
This is the source code:
Here is the complete code in the codebehind. In this code, the binddata method is responsible for loading data from the database. As you can see from this method, I create SqlCommand object and selects data from Record2 database table. You will need to change this SELECT query for your database table.
I store my database connection string in Web.config file, which I am fetching using ConfigurationManager as you can see from this code.
After that I fill data in a DataSet and bind to the GridView control using DataSource property of GridView.
Now code for editing and deleting is on RowEditing and RowDeleting events where I take data (EmpID, Fname, and Lname) from the selected row and then create a SQL UPDATE or DELETE query and execute the query using SqlCommand object. After that I bind data again to refill the updated data.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class gridviewexample : System.Web.UI.Page
{
SqlDataAdapter da;
DataSet ds = new DataSet();
SqlConnection con;
SqlCommand cmd = new SqlCommand();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
binddata();
}
}
public void binddata()
{
con = new SqlConnection(ConfigurationManager.AppSettings["connect"]);
cmd.CommandText = "select* from Record2";
cmd.Connection = con;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Open();
cmd.ExecuteNonQuery();
grid1.DataSource = ds;
grid1.DataBind();
con.Close();
}
protected void grid1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grid1.PageIndex = e.NewPageIndex;
binddata();
}
protected void grid1_RowEditing(object sender, GridViewEditEventArgs e)
{
grid1.EditIndex = e.NewEditIndex;
binddata();
}
protected void grid1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grid1.EditIndex = -1;
binddata();
}
protected void grid1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
con = new SqlConnection(ConfigurationManager.AppSettings["connect"]);
cmd.Connection = con;
Label lblempid = (Label)grid1.Rows[e.RowIndex].FindControl("lblempid");
cmd.CommandText = "Delete from Record2 where empid='" + lblempid.Text + "'";
con.Open();
cmd.ExecuteNonQuery();
con.Close();
binddata();
}
protected void grid1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
con = new SqlConnection(ConfigurationManager.AppSettings["connect"]);
GridViewRow row = (GridViewRow)grid1.Rows[e.RowIndex];
Label lblempid = (Label)row.FindControl("lblempid");
TextBox txtfname = (TextBox)row.FindControl("txtfname");
TextBox txtlname = (TextBox)row.FindControl("txtlname");
cmd.Connection = con;
cmd.CommandText = "Update Record2 set fname='" + txtfname.Text + "',lname='" +txtlname.Text + "' where empid='" + lblempid.Text + "'";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
con.Close();
binddata();
}
}
Summary
In this simple article, we saw how to load, edit, and delete data from a database using a GridView control in ASP.NET.