Insert Update And Delete Rows In ASP.NET GridView Control

In this article, you can learn how to edit, update, delete, and cancel in GridView.

Introduction

In this blog, you will learn how to edit, update, delete, and cancel in GridView. First, drag and drop the GridView. In GridView properties, set AutoGenarateColumns to False.

Next, open Default.aspx source code. To make a column in GridView, use  <asp:TemplateField>. Here, first, I created a table named 'Product' in my database. It contains 3 columns: ProductId, ProductName, and ProdcutPrice.

1. Download Sample
2. Inside that folder, a person.sql file will be there. Execute it in your database.  
   

   /****** Object:  Table [dbo].[Product]    Script Date: 07/07/2012 02:18:07 ******/
   SET ANSI_NULLS ON
   GO
   
   SET QUOTED_IDENTIFIER ON
   GO
   
   SET ANSI_PADDING ON
   GO
   
   CREATE TABLE [dbo].[Product](
       [pk_id] [int] IDENTITY(1,1) NOT NULL,
       [ProductId] [varchar](5) NULL,
       [ProductName] [varchar](50) NULL,
       [ProductPrice] [numeric](8, 2) NULL,
   PRIMARY KEY CLUSTERED
   (
       [pk_id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   
   GO
   
   SET ANSI_PADDING OFF
   GO

3. Change the connection string in web.config file.
   

   <configuration>
     <connectionStrings>
       <add name="MyConnection"
            connectionString="Data Source=MONISH-PC\MONISH;Initial Catalog=master;Persist Security Info=True;User ID=saty;Password=123"
            providerName="System.Data.SqlClient" />
     </connectionStrings>

Building the Sample

This sample is written by three tier architecture so you have to add references.

• UI->BusinessLogic
• BusinessLogic->DataAccess back to BL
• BusinessLogic->Commonfunctioon back to BL
• BusinessLogic -> UI finally BL return data to UI

 

 

Description

 

In Default.aspx, we have to add the following source code. This is used to Edit the Row in Gridview. Here, I am going to edit only two columns - name and marks.

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" AutoGenerateColumns="False" onrowcancelingedit="GridView1_RowCancelingEdit" onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating">
    <AlternatingRowStyle BackColor="White" />
    <Columns>
        <asp:TemplateField Visible="False">
            <ItemTemplate>
                <asp:Label ID="lblPk_id" runat="server" Text='<%#Eval("pk_id")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField Headertext="ProductId">
            <EditItemTemplate>
                <asp:TextBox ID="txtProductId" runat="server" Text='<%#Eval("ProductId")%>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtProductId" runat="server" Text='<%#Eval("ProductId")%>'></asp:TextBox>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="lblProductId" runat="server" Text='<%#Eval("ProductId")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ProductName">
            <EditItemTemplate>
                <asp:TextBox ID="txtProductName" runat="server" Text='<%#Eval("ProductName")%>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtProductName" runat="server" Text='<%#Eval("ProductName")%>'></asp:TextBox>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="lblProductName" runat="server" Text='<%#Eval("ProductName")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ProductPrice">
            <EditItemTemplate>
                <asp:TextBox ID="txtProductPrice" runat="server" Text='<%#Eval("ProductPrice")%>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtProductPrice" runat="server" Text='<%#Eval("ProductPrice")%>'></asp:TextBox>
            </FooterTemplate>
            <ItemTemplate>
                <asp:Label ID="lblProductPrice" runat="server" Text='<%#Eval("ProductPrice")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:TemplateField>
            <FooterTemplate>
                <asp:Button ID="btnInsert" runat="Server" Text="Insert" CommandName="Insert" UseSubmitBehavior="False" /> </FooterTemplate>
        </asp:TemplateField>
    </Columns>
    <EditRowStyle BackColor="#2461BF" />
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#EFF3FB" />
    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#F5F7FB" />
    <SortedAscendingHeaderStyle BackColor="#6D95E1" />
    <SortedDescendingCellStyle BackColor="#E9EBEF" />
    <SortedDescendingHeaderStyle BackColor="#4870BE" /> </asp:GridView>

Note - I have used SQL Server 2008. 

Summary

For this article, we filled GridView with data from the database.

We can perform the following operations on data using GridView:

1. Add New record into a database; here, data will directly be added to a database table.
2. Update Records into the database, using edit link.
3. Delete Records using delete link