articles

Home / DeveloperSection / Articles / Insert, Delete, Update in GridView in ASP.Net using C#

Insert, Delete, Update in GridView in ASP.Net using C#

Insert, Delete, Update in GridView in ASP.Net using C#

AVADHESH PATEL 260553 06-Aug-2012

Insert, Delete, Update in GridView in ASP.Net using C#

The ASP.NET GridView is a very common and useful control. Here, I’m explaining how to work with GridView control in ASP.NET, like how to insert, delete and update records in GridView control.

Follow the below steps to know how to work with GridView in ASP.NET.

Step 1:- Create a Database in SqlServer and create a table within this database. For demonstration here I have created a database named “Demo” and a table named “GridView

CREATE DATABASE DEMO
GO
 
USE DEMO
GO
 
CREATE TABLE GridView
(
id INT PRIMARY KEY IDENTITY,
name VARCHAR (50) NOT NULL,
age INT NOT NULL,
salary FLOAT NOT NULL,
country VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
photopath VARCHAR(500) NULL
)
GO

  Insert, Delete, Update in GridView in ASP.Net using C#

Note: Here “id” column is auto increment and primary key. 

Step 2:- Add connection string in your application’s web.config file and change name and connectionString according to your SQL Server configuration as following.

<configuration>
     <connectionStrings>
                <add name="dbconnection" providerName="System.Data.SqlClient"
                    connectionString="Data Source=.;Initial Catalog=demo; User Id=xyz; password=123456" />
                </connectionStrings>
</configuration>

 Step 3:- Drag & drop GridView control from Toolboxon “.aspx” page and write down the following line of code within <asp : GridView> section or simply copy the following line of code and put where you want to display GridView

<div>
        <asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="True"
            AutoGenerateColumns="False" onrowdeleting="RowDeleting"
            OnRowCancelingEdit="cancelRecord" OnRowEditing="editRecord"
            OnRowUpdating="updateRecord" CellPadding="4"
            EnableModelValidation="True" GridLines="None" Width="1297px"
            ForeColor="#333333" >
            <RowStyle HorizontalAlign="Center" />
            <AlternatingRowStyle BackColor="White" />
            <EditRowStyle BackColor="#7C6F57" />
            <FooterStyle BackColor="#1C5E55" ForeColor="White" Font-Bold="True" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#E3EAEB" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
 
            <Columns>
 
            <asp:TemplateField>
            <HeaderTemplate>Id</HeaderTemplate>
            <ItemTemplate>
            <asp:Label ID ="lblId" runat="server" Text='<%#Bind("id")%>'></asp:Label>
            </ItemTemplate>
            </asp:TemplateField>
 
            <asp:TemplateField>
            <HeaderTemplate>Name</HeaderTemplate>
            <ItemTemplate>
            <asp:Label ID ="lblName" runat="server" Text='<%#Bind("name") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID="txtName" runat="server" Text='<%#Bind("name") %>' MaxLength="50"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtName" runat="server" Text="*" ToolTip="Enter name" ControlToValidate="txtName"></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator ID="revtxtName" runat="server" Text="*" ToolTip="Enter alphabate " ControlToValidate="txtName" ValidationExpression="^[a-zA-Z'.\s]{1,40}$"></asp:RegularExpressionValidator>
           
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtNewName" runat="server" MaxLength="50"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtNewName" runat="server" Text="*" ToolTip="Enter name" ControlToValidate="txtNewName"></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator ID="revtxtNewName" runat="server" Text="*" ToolTip="Enter alphabate " ControlToValidate="txtNewName" ValidationExpression="^[a-zA-Z'.\s]{1,40}$"></asp:RegularExpressionValidator>
          
            </FooterTemplate>
            </asp:TemplateField>
           
            <asp:TemplateField>
            <HeaderTemplate>Age</HeaderTemplate>
            <ItemTemplate>
            <asp:Label ID="lblAge" runat ="server" Text='<%#Bind("age") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID ="txtAge" runat="server" Text='<%#Bind("age") %>' MaxLength="2"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtAge" runat="server" Text="*" ToolTip="Enter age" ControlToValidate="txtAge"></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator ID="revtxtAge" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtAge" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
          
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtNewAge" runat="server" MaxLength="2"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtNewAge" runat="server" Text="*" ToolTip="Enter age" ControlToValidate="txtNewAge"></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator ID="revNewtxtAge" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtNewAge" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
            </FooterTemplate>
            </asp:TemplateField>
 
            <asp:TemplateField>
            <HeaderTemplate>Salary</HeaderTemplate>
            <ItemTemplate>
            <asp:Label ID = "lblSalary" runat="server" Text='<%#Bind("salary") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID="txtSalary" runat="server" Text='<%#Bind("salary") %>'  MaxLength="10"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtSalary" runat="server" Text="*"  ToolTip="Enter salary" ControlToValidate="txtSalary"></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator ID="revtxtSalary" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtSalary" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
          
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtNewSalary" runat="server" MaxLength="10"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtNewSalary" runat="server" Text="*"  ToolTip="Enter salary" ControlToValidate="txtNewSalary"></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator ID="revtxtNewSalary" runat="server" Text="*" ToolTip="Enter numeric value" ControlToValidate="txtNewSalary" ValidationExpression="^[0-9]+$"></asp:RegularExpressionValidator>
            </FooterTemplate>
            </asp:TemplateField>
 
            <asp:TemplateField>
            <HeaderTemplate>Country</HeaderTemplate>
            <ItemTemplate>
            <asp:Label ID = "lblCountry" runat="server" Text='<%#Bind("country") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID="txtCountry" runat="server" Text='<%#Bind("country") %>' MaxLength="20"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtCountry" runat="server" Text="*" ToolTip="Enter country" ControlToValidate="txtCountry"></asp:RequiredFieldValidator>
        
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtNewCountry" runat="server" MaxLength="20"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtNewCountry" runat="server" Text="*" ToolTip="Enter country" ControlToValidate="txtNewCountry"></asp:RequiredFieldValidator>
            </FooterTemplate>
            </asp:TemplateField>
 
            <asp:TemplateField>
            <HeaderTemplate>City</HeaderTemplate>
            <ItemTemplate>
            <asp:Label ID = "lblCity" runat="server" Text='<%#Bind("city") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
            <asp:TextBox ID="txtCity" runat="server" Text='<%#Bind("city") %>' MaxLength="20"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtCity" runat="server" Text="*" ToolTip="Enter city" ControlToValidate="txtCity"></asp:RequiredFieldValidator>
            </EditItemTemplate>
            <FooterTemplate>
            <asp:TextBox ID="txtNewCity" runat="server" MaxLength="20"></asp:TextBox>
            <asp:RequiredFieldValidator ID="rfvtxtNewCity" runat="server" Text="*" ToolTip="Enter city" ControlToValidate="txtNewCity"></asp:RequiredFieldValidator>
            </FooterTemplate>
            </asp:TemplateField>
 
            <asp:TemplateField>
            <HeaderTemplate>Photo</HeaderTemplate>
            <ItemTemplate>
            <asp:Image ID="imgPhoto" Width="100px" Height="100px" runat="server" text="Photo" ImageUrl='<%#Bind("photopath") %>' />
            </ItemTemplate>
            <EditItemTemplate>
            <asp:FileUpload ID="fuPhoto" runat="server" ToolTip="select Employee Photo"/>
            <asp:RegularExpressionValidator ID="revfuPhoto" runat="server" Text="*" ToolTip="Image formate only" ControlToValidate="fuPhoto" ValidationExpression="[a-zA-Z0_9].*\b(.jpeg|.JPEG|.jpg|.JPG|.jpe|.JPE|.png|.PNG|.mpp|.MPP|.gif|.GIF)\b"></asp:RegularExpressionValidator>
            </EditItemTemplate>
            <FooterTemplate>
            <asp:FileUpload ID="fuNewPhoto" runat="server" ToolTip="select Employee Photo"/>
            <asp:RequiredFieldValidator ID="rfvfuNewPhoto" runat="server" ErrorMessage="*" ToolTip="Select Photo" ControlToValidate="fuNewPhoto"></asp:RequiredFieldValidator>
            <asp:RegularExpressionValidator ID="revfuNewPhoto" runat="server" Text="*" ToolTip="Image formate only" ControlToValidate="fuNewPhoto" ValidationExpression="[a-zA-Z0_9].*\b(.jpeg|.JPEG|.jpg|.JPG|.jpe|.JPE|.png|.PNG|.mpp|.MPP|.gif|.GIF)\b"></asp:RegularExpressionValidator>
            </FooterTemplate>
            </asp:TemplateField>
 
            <asp:TemplateField>
            <HeaderTemplate>Operation</HeaderTemplate>
            <ItemTemplate>
            <asp:Button ID="btnEdit" runat="server" CommandName="Edit" Text="Edit" />
            <asp:Button ID="btnDelete" runat="server" CommandName="Delete" Text="Delete" CausesValidation="true" OnClientClick="return confirm('Are you sure?')" />
            </ItemTemplate>
            <EditItemTemplate>
            <asp:Button ID="btnUpdate" runat="server" CommandName="Update" Text="Update" />
            <asp:Button ID="btnCancel" runat="server" CommandName="Cancel" Text="Cancel" CausesValidation="false" />
            </EditItemTemplate>
 
            <FooterTemplate>
            <asp:Button ID="btnNewInsert" runat="server" Text="Insert" OnClick="InsertNewRecord"/>
            <asp:Button ID="btnNewCancel" runat="server" Text="Cancel" OnClick="AddNewCancel" CausesValidation="false" />
            </FooterTemplate>        
            </asp:TemplateField>           
            </Columns>
            <EmptyDataTemplate>
                      No record available                    
            </EmptyDataTemplate>       
        </asp:GridView>
        <br />
        <asp:Button ID="btnAdd" runat="server" Text="Add New Record" OnClick="AddNewRecord" />  
    </div>

 Step 4:- Create one class within the App_Code folder. 

Here I have given this class named is GlobalClass and write the following line of code.

using System.Data;
using System.Data.SqlClient;
 
public class GlobalClass
{
    public static SqlDataAdapter adap;
    public static DataTable dt;
    // Stored image path before updating the record
    public static string imgEditPath;
}

Step 5:- Create one folder, named Images, where we have stored employee's photos see in the following image.


Insert, Delete, Update in GridView in ASP.Net using C#

Step 6:- Write down following line of code in “.cs” page which related to your .aspx page (e.g. if your .aspx page name is default.aspx then your .cs file is default.aspx.cs).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;
using System.IO;
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            // Call FillGridView Method
            FillGridView();
        }
    }
    /// <summary>
    /// Fill record into GridView
    /// </summary>
    public void FillGridView()
    {
        try
        {
            string cnString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
            SqlConnection con = new SqlConnection(cnString);
            GlobalClass.adap = new SqlDataAdapter("select * from gridview", con);
            SqlCommandBuilder bui = new SqlCommandBuilder(GlobalClass.adap);
            GlobalClass.dt = new DataTable();
            GlobalClass.adap.Fill(GlobalClass.dt);
            GridView1.DataSource = GlobalClass.dt;
            GridView1.DataBind();
        }
        catch
        {
            Response.Write("<script> alert('Connection String Error...') </script>");
        }
    }
    /// <summary>
    /// Edit record
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void editRecord(object sender, GridViewEditEventArgs e)
    {
        // Get the image path for remove old image after update record
        Image imgEditPhoto = GridView1.Rows[e.NewEditIndex].FindControl("imgPhoto"as Image;
        GlobalClass.imgEditPath = imgEditPhoto.ImageUrl;
        // Get the current row index for edit record
        GridView1.EditIndex = e.NewEditIndex;
        FillGridView();
    }
 
    /// <summary>
    /// Cancel the operation (e.g. edit)
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void cancelRecord(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        FillGridView();
    }
 
    /// <summary>
    /// Add new row into DataTable if no record found in Table
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void AddNewRecord(object sender, EventArgs e)
    {
        try
        {
            if (GlobalClass.dt.Rows.Count > 0)
            {
                GridView1.EditIndex = -1;
                GridView1.ShowFooter = true;
                FillGridView();
            }
            else
            {
                GridView1.ShowFooter = true;
                DataRow dr = GlobalClass.dt.NewRow();
                dr["name"] = "0";
                dr["age"] = 0;
                dr["salary"] = 0;
                dr["country"] = "0";
                dr["city"] = "0";
                dr["photopath"] = "0";
                GlobalClass.dt.Rows.Add(dr);
                GridView1.DataSource = GlobalClass.dt;
                GridView1.DataBind();
                GridView1.Rows[0].Visible = false;
            }
        }
        catch
        {
            Response.Write("<script> alert('Row not added in DataTable...') </script>");
        }
    }
 
    /// <summary>
    /// Cancel new added record
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void AddNewCancel(object sender, EventArgs e)
    {
        GridView1.ShowFooter = false;
        FillGridView();
    }
 
    /// <summary>
    /// Insert New Record
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void InsertNewRecord(object sender, EventArgs e)
    {
        try
        {
            string strName = GlobalClass.dt.Rows[0]["name"].ToString();
            if (strName == "0")
            {
                GlobalClass.dt.Rows[0].Delete();
                GlobalClass.adap.Update(GlobalClass.dt);
            }
            TextBox txtName = GridView1.FooterRow.FindControl("txtNewName"as TextBox;
            TextBox txtAge = GridView1.FooterRow.FindControl("txtNewAge"as TextBox;
            TextBox txtSalary = GridView1.FooterRow.FindControl("txtNewSalary"as TextBox;
            TextBox txtCountry = GridView1.FooterRow.FindControl("txtNewCountry"as TextBox;
            TextBox txtCity = GridView1.FooterRow.FindControl("txtNewCity"as TextBox;
            FileUpload fuPhoto = GridView1.FooterRow.FindControl("fuNewPhoto"as FileUpload;
            Guid FileName = Guid.NewGuid();
            fuPhoto.SaveAs(Server.MapPath("~/Images/" + FileName + ".png"));
            DataRow dr = GlobalClass.dt.NewRow();
            dr["name"] = txtName.Text.Trim();
            dr["age"] = txtAge.Text.Trim();
            dr["salary"] = txtSalary.Text.Trim();
            dr["country"] = txtCountry.Text.Trim();
            dr["city"] = txtCity.Text.Trim();
            dr["photopath"] = "~/Images/" + FileName + ".png";
            GlobalClass.dt.Rows.Add(dr);
            GlobalClass.adap.Update(GlobalClass.dt);
            GridView1.ShowFooter = false;
            FillGridView();
        }
        catch
        {
            Response.Write("<script> alert('Record not added...') </script>");
        }
 
    }
    /// <summary>
    /// Update the record
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void updateRecord(object sender, GridViewUpdateEventArgs e)
    {
        try
        {
            TextBox txtName = GridView1.Rows[e.RowIndex].FindControl("txtName"as TextBox;
            TextBox txtAge = GridView1.Rows[e.RowIndex].FindControl("txtAge"as TextBox;
            TextBox txtSalary = GridView1.Rows[e.RowIndex].FindControl("txtSalary"as TextBox;
            TextBox txtCountry = GridView1.Rows[e.RowIndex].FindControl("txtCountry"as TextBox;
            TextBox txtCity = GridView1.Rows[e.RowIndex].FindControl("txtCity"as TextBox;
            FileUpload fuPhoto = GridView1.Rows[e.RowIndex].FindControl("fuPhoto"as FileUpload;
            Guid FileName = Guid.NewGuid();
            if (fuPhoto.FileName != "")
            {
                fuPhoto.SaveAs(Server.MapPath("~/Images/" + FileName + ".png"));
                GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["photopath"] = "~/Images/" + FileName + ".png";
                File.Delete(Server.MapPath(GlobalClass.imgEditPath));
            }
            GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["name"] = txtName.Text.Trim();
            GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["age"] = Convert.ToInt32(txtAge.Text.Trim());
            GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["salary"] = Convert.ToInt32(txtSalary.Text.Trim());
            GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["country"] = txtCountry.Text.Trim();
            GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex]["city"] = txtCity.Text.Trim();
            GlobalClass.adap.Update(GlobalClass.dt);   
            GridView1.EditIndex = -1;
            FillGridView();
        }
        catch
        {
            Response.Write("<script> alert('Record updation fail...') </script>");
        }
    }
 
    /// <summary>
    /// Delete Record
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        try
        {
            GlobalClass.dt.Rows[GridView1.Rows[e.RowIndex].RowIndex].Delete();
            GlobalClass.adap.Update(GlobalClass.dt);
            // Get the image path for removing deleted's record image from server folder
            Image imgPhoto = GridView1.Rows[e.RowIndex].FindControl("imgPhoto"as Image;
            File.Delete(Server.MapPath(imgPhoto.ImageUrl));
            FillGridView();
        }
        catch
        {
            Response.Write("<script> alert('Record not deleted...') </script>");
        }
    }
}

Step 7:- Now, save and build your application and execute. Your application output display as following.

Insert, Delete, Update in GridView in ASP.Net using C#

It’s your application first screen. Because there is no data in a table so it displaces the message “No record available”.

Step 8:- For inserting data, click on the button “Add New Record”. Now fill the data. Every control has validation for prohibited wrong input.  

Insert, Delete, Update in GridView in ASP.Net using C#

After filling data, your GridView looking as following.  Here I have inserted three records.

Insert, Delete, Update in GridView in ASP.Net using C#

Note: - Id is generated automatically for every new record.

Step 9:- For updating records click button Edit and change TextBox Data and you can select a new photo of the employee. For example in place of age “28”, updated with “30” and in place of salary “50000”, updated with “55000”, then click button Update”. Through the button Cancel you return previous stage.

Insert, Delete, Update in GridView in ASP.Net using C#

Step 10:- For record deletion, click on the button Delete”. Before deleting records, a confirmation alert message is a popup.

Here I have deleted 3rd record (e.g. Andrew Daniel). After deleting record your GridView looks as following.

Insert, Delete, Update in GridView in ASP.Net using C#



Updated 04-Aug-2020
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By