• How to Add Insert Update Delete rows ASP.NET GridView Control

    Posted on March 29, 2012 by in ASP.NET, C#, Dotnet

    GridView displays the values of a data source in a table where each column represents a field and each row represents a record.  It enables you to select, sort, and edit these items.  In this article, I am going to explain how to insert, update or delete a row from a grid view control. If you are new to using a grid control, I would recommend you to read my blogs “Using GridView in ASP.NET & C# —PART 1 & PART 2”.

    Database Connection

    Create a new ASP.NET Web Application project. Open web.config and add following entry to connection strings element

    <add name="Sql" connectionString="Data Source=<SERVERNAME>;
         Initial Catalog= TEST;User=testuser;Password=testuser;" 
         providerName="System.Data.SqlClient"/> 

    Page Design
    Create a new aspx page and name it as ProductsEditView.aspx. Open the aspx file in design mode and add the following code in div tag. I will explain each setting in detail later.

    <asp:gridview id="gvProducts"          
              autogeneratecolumns="False" emptydatatext="No data available."               
              runat="server" DataKeyNames="ProductID"
              OnRowEditing="gvProducts_RowEditing" OnRowCancelingEdit="gvProducts_RowCancelingEdit"
              OnRowCommand="gvProducts_RowCommand" onrowupdating="gvProducts_RowUpdating"
              OnRowDeleting="gvProducts_RowDeleting">
      <Columns>
      <asp:BoundField DataField="ProductID"  HeaderText="Product ID" ReadOnly="true"/>                           
          <asp:TemplateField headertext="Product Number">
              <ItemTemplate> <%#Eval("ProductNumber")%></ItemTemplate>    
              <EditItemTemplate>
                   <asp:TextBox id="txtProductNumber" runat="server" text='<%#Eval("ProductNumber")%>'/>
              </EditItemTemplate>                 
              <FooterTemplate>
             <asp:TextBox ID="txtNewProductNumber" runat="Server"/>                     
        </FooterTemplate>
          </asp:TemplateField>                   
          <asp:TemplateField headertext="Product Name">
             <ItemTemplate> <%#Eval("Name")%></ItemTemplate>
             <EditItemTemplate>
                    <asp:TextBox id="txtProductName" text='<%#Eval("Name")%>' runat="server"/>                                             
             </EditItemTemplate>
             <FooterTemplate>
                        <asp:TextBox ID="txtNewProductName" runat="Server"/> 
             </FooterTemplate>
         </asp:TemplateField>     
         <asp:TemplateField headertext="Price">
             <ItemTemplate> <%#Eval("ListPrice")%></ItemTemplate>
             <EditItemTemplate>
                   <asp:TextBox id="txtListPrice" text='<%#Eval("ListPrice")%>' runat="server"/>                                                                         
             </EditItemTemplate>
             <FooterTemplate>
                    <asp:TextBox ID="txtNewListPrice" runat="Server"> 
             </FooterTemplate>
         </asp:TemplateField>                                 
         <asp:TemplateField>
             <ItemTemplate>
                  <asp:LinkButton ID="btnedit" runat="server" CommandName="Edit" Text="Edit"/>        
             </ItemTemplate>
              <EditItemTemplate>
                  <asp:LinkButton ID="btnupdate" runat="server" CommandName="Update" Text="Update" />
                  <asp:LinkButton ID="btncancel" runat="server" CommandName="Cancel" Text="Cancel"/>
                  <asp:LinkButton ID="btnDelete" runat="server" CommandName="Delete" Text="Delete"/> 
              </EditItemTemplate>
              <FooterTemplate>
                 <asp:Button ID="btnInsert" runat="Server" Text="Insert" CommandName="Insert" UseSubmitBehavior="False" />
              </FooterTemplate>
          </asp:TemplateField>                                          
      </Columns>
    </asp:gridview>         
    <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="AddNewRecord" />
    Name Description
    Autogeneratecolumns=”False” Set’s a value indicating bound fields should not created for each field in the data source.
    emptydatatext=”No data available.” It displays “No data available” in the grid, if resultset is emtpy
    OnRowCommand=”ProductsView_RowCommand” when a button is clicked in the GridView control, ProductsView_RowCommand is called. This enables you to provide an event-handling method that performs a custom routine whenever this event occurs.
    OnRowEditing=”gvProducts_RowEditing” when a row’s Edit button is clicked, gvProducts_RowEditing is called before the control enters edit mode
    asp:BoundField Displays the value of a field in a data source.
    BoundField -> DataField Gets or sets the name of the data field to bind to the BoundField object.
    BoundField -> HeaderText Gets or sets the text that is displayed in the header of a data control
    TemplateField Used TemplateField to display custom content for each record displayed.
    TemplateField -> ItemTemplate Use the ItemTemplate property to specify the custom content displayed for the items in a TemplateField object. Define the content by creating a template that specifies how the items are rendered. 
    <%#Eval(“ProductNumber”)%> Data-binding expressions are contained within <%# and %> delimiters. At run time, the Eval method calls the Eval method of the DataBinder object. The Eval method takes the name of a data field and returns a string containing the value of that field from the current record in the data source.
    <ItemTemplate> <%#Eval(“ProductNumber”)%></ItemTemplate>    Specify the custom content displayed for the templatefield items.  In our example, we are directly writing the product number text. We can also define a new control in the itemtemplate
    <EditItemTemplate><asp:TextBox id=”txtProductNumber” runat=”server” text=’<%#Eval(“ProductNumber”)%>’/> </EditItemTemplate> When the control enters edit mode, content in edititemtemplate is displayed. When user clicks on “Edit” link button, a textbox with  existing product number is displayed
    <FooterTemplate><asp:TextBox ID=”txtNewProductNumber” runat=”Server”/> </FooterTemplate> We can define content to be displayed in the footer section of the control.  In our example, we define content that needs to be displayed when user requests to enter data for new product
    <asp:LinkButton ID=”btnedit” runat=”server”CommandName=”Edit” Text=”Edit”/> Command name is set to “Edit”. When this button is clicked, control goes into edit mode
    <asp:LinkButton ID=”btnupdate” runat=”server”           CommandName=”Update” Text=”Update” /> Command name is set to “Update”. When this button is clicked, control would understand it is an update.

    View Products
    I am using ADO.NET API such as SQL Connection, SQL Command, SQL Adapters etc. to get the products from the database and to bind to gridview.

    private void BindData()
    {
        //Bind the grid view
        gvProducts.DataSource = RetrieveProducts();
        gvProducts.DataBind();
    }
    private DataSet RetrieveProducts()
    {
        if (ViewState["Products"] != null)
            return (DataSet)ViewState["Products"];
    
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //SQL statement to fetch entries from products
        string sql = @"Select top 10  P.ProductID, P.Name, P.ProductNumber, ListPrice  from tblProduct P";
    
        DataSet dsProducts = new DataSet();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dsProducts);
    
            }
        }
        return dsProducts;
    }

    If you run the application now, you would see the following page listing all the products.
    Add Insert Update Delete rows ASP.NET GRIDVIEW 1

    Create Product
    Click on “Add” button and it would create a blank row.  All we did in the button click event is to make the footer row visible. In the footer design itself, we designed it in such a way that, it looks like a blank product  record.

    protected void AddNewRecord(object sender, EventArgs e) 
    { 
         gvProducts.ShowFooter = true;
         BindData();             
    }

    Add Insert Update Delete rows ASP.NET GRIDVIEW 2

    After you fill in the data, click on the insert button to add the product to the back-end database. When user clicks on the “insert” button, gvProducts_RowCommand event handler is called. In the event handler, we get the data from the controls and insert them in the database.

    protected void gvProducts_RowCommand(object sender, 
    GridViewCommandEventArgs e)
    {
        // If multiple ButtonField column fields are used, use the
        // CommandName property to determine if "Insert" button was clicked. 
        if (e.CommandName.Equals("Insert"))
        {   
            //fetch the values of the new product
            TextBox txtNewProductNumber = gvProducts.FooterRow.FindControl("txtNewProductNumber") as TextBox;
            TextBox txtNewProductName = gvProducts.FooterRow.FindControl("txtNewProductName") as TextBox;
            TextBox txtNewListPrice = gvProducts.FooterRow.FindControl("txtNewListPrice") as TextBox;
            //insert the new product into database
            InsertProduct(txtNewProductNumber.Text, txtNewProductName.Text, txtNewListPrice.Text);
            gvProducts.ShowFooter = false;
            //clear the view state so that latest list will be retrieved from db
            ViewState["Products"] = null;
            BindData(); // rebind the data
        }
    }
    
    private void InsertProduct(string productNumber, string productName, string listPrice)
    {
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to insert a product
        string sql = String.Format("Insert into tblProduct values('{0}','{1}',{2})",
                                        productName,
                                        productNumber,
                                        listPrice);
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }

    Following are the screen shots of products page before and after clicking on “Insert” button
    Add Insert Update Delete rows ASP.NET GRIDVIEW 3

    Add Insert Update Delete rows ASP.NET GRIDVIEW 3a

    Update Product
    Click on “Edit” and the grid would go into edit mode.  If you look at event handling function,  you would notice  that we are setting edit index of the grid to the row you trying to edit. Since it is a post back, we have to re-bind the grid control.

    protected void gvProducts_RowEditing(object sender, GridViewEditEventArgs e)
    {
    gvProducts.EditIndex = e.NewEditIndex;
    BindData();
    }

    Add Insert Update Delete rows ASP.NET GRIDVIEW 4

    Change product number to “product number updated” and product name to “product name updated” and price to 50. Clicking on “Update” would save your changes to DB.

    When you click on “Update” button, gvProducts_RowUpdating event is fired. Based on the selected row, get the gridviewrow. Once you get the gridviewrow, you could search for the content controls and get the updated values. Once we have the latest values, we could simply persist them to the database using ADO.NET API.

    protected void gvProducts_RowUpdating(Object sender, GridViewUpdateEventArgs e)
    {
        
        // Get the product id of the selected product    
        string ID = gvProducts.DataKeys[e.RowIndex].Value.ToString();
    
        // Get the GridViewRow object that represents the row being edited
        // from the Rows collection of the GridView control.            
        GridViewRow row = gvProducts.Rows[e.RowIndex];
    
        // Get the controls that contain the updated values. In this
        // example, the updated values are contained in the TextBox 
        // controls declared in the edit item templates of each TemplateField 
        // column fields in the GridView control.
        TextBox txtProductNumber = (TextBox)row.FindControl("txtProductNumber");
        TextBox txtProductName = (TextBox)row.FindControl("txtProductName");
        TextBox txtListPrice = (TextBox)row.FindControl("txtListPrice");
        //update the product 
        UpdateProduct(ID, txtProductNumber.Text, txtProductName.Text, txtListPrice.Text);
        gvProducts.EditIndex = -1;
        BindData();
    }
    
    private void UpdateProduct(string productID, string productNumber, 
                        string productName, string listPrice)
    {
        //fetch the connection string from web.config
        string connString = 
               ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to update a product
        string sql = String.Format(@"Update tblProduct set Name='{0}'
                                        ,ProductNumber='{1}'
                                        ,ListPrice ={2} 
                                        where ProductID = {3}",
                                        productName,
                                        productNumber,
                                        listPrice, productID);
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }

    Add Insert Update Delete rows ASP.NET GRIDVIEW 5

    Add Insert Update Delete rows ASP.NET GRIDVIEW 6

    Delete Product
     Clicking on “Delete” button would delete that record from the table. When you click on “Delete” button, gvProducts_RowDeleting event would be fired. In the event handler, we fetch the product ID from the row you were trying to delete. Based on the product ID, which is primary key for tblProduct table, that record is deleted using ADO.NET API.

    protected void gvProducts_RowDeleting(Object sender, 
    GridViewDeleteEventArgs e)
    {
        // Get the product id of the selected product    
        string ProductID = gvProducts.DataKeys[e.RowIndex].Value.ToString();            
        //delete the product 
        DeleteProduct(ProductID);
        gvProducts.EditIndex = -1;
        BindData();
    }
    private void DeleteProduct(string productID)
    {
        //fetch the connection string from web.config
        string connString = 
             ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to delete from products
        string sql = String.Format(@"Delete from tblProduct 
                                        where ProductID = {0}",
                                        productID);
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }

    Add Insert Update Delete rows ASP.NET GRIDVIEW 8

    Add Insert Update Delete rows ASP.NET GRIDVIEW 9

    Cancel Edit:
    If you ever want to get out the edit mode, click on “Cancel” button and you would be back in view mode.

    protected void gvProducts_RowCancelingEdit
            (object sender, GridViewCancelEditEventArgs e)
    {
        gvProducts.EditIndex = -1;
        BindData();
    }

    SQL Statements:
    I have created a tblProduct table and loaded it with some test data. If you would like to use the same table, you could use below scripts.

    CREATE TABLE [dbo].[tblProduct](
     [ProductID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [varchar](255) NOT NULL,
     [ProductNumber] [varchar](255) NOT NULL,
     [ListPrice] [numeric](9, 2) NOT NULL,
     CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED 
    (
     [ProductID] 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
    
    DECLARE @COUNTER INT, @productname VARCHAR(255), 
     @productnumber VARCHAR(255), @listprice DECIMAL
    SET @COUNTER = 0
    SET @productname = 'product name '
    SET @productnumber = 'product number '
    SET @listprice = 500
    WHILE(@COUNTER <= 5)
    BEGIN
     SET @listprice= @listprice + 100
     INSERT INTO [dbo].[tblProduct]
         VALUES
               (@productname + convert(VARCHAR, @COUNTER)
               ,@productnumber + convert(VARCHAR, @COUNTER)
               ,@listprice)
     SET @COUNTER = @COUNTER + 1           
    END
    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    39 Responsesso far.

    1. tuba al says:

      thanks, very useful.

    2. sunil says:

      if database table have no record, I mean grid not showing any record but I want to insert a new record, but insert is not enable if record count is less than 1.

      • Vanamali says:

        Hi Sunil

        The reason you are not seeing any record is because the table layout is not drawn. When there are no records in the gridview, nothing is getting displayed. So we need to fix the gridview. Once you get atleast the header drawn, clicking on Add would show the footer row.

        Modify BindData function in the above example as shown below and I have also created a new function FixGridHeader() that would show the header when record count is zero.

        private void BindData()
        {
            //execute the select statement 
            DataSet dsProducts = RetrieveProducts();
            DataTable dtProducts = dsProducts.Tables[0];
        
            //if it is an empty dataset, add a dummy row to the header
            if (dtProducts.Rows.Count == 0)
            {
                FixGridHeader(dtProducts);
            }
            else
            {
                //if the query returned results, just add them to the grid
                gvProducts.DataSource = dtProducts;
                gvProducts.DataBind();
            }
        }
        
        private void FixGridHeader(DataTable dataSource)
        {
            //add blank row to the the resultset
            dataSource.Rows.Add(dataSource.NewRow());
        
            gvProducts.DataSource = dataSource;
            gvProducts.DataBind();
        
            //hide empty row
            gvProducts.Rows[0].Visible = false;
            gvProducts.Rows[0].Controls.Clear();
        }

        Also for more information, take a look at the below article that would explain to fix the header when there are no records in the gridview.
        http://technico.qnownow.com/2012/06/08/show-gridview-header-when-there-is-no-data/

    3. rabbil says:

      excellent example, with all the concept….thanks a ton…!!

    4. pviningston says:

      UpdateProduct(ID, txtProductNumber.Text, txtProductName.Text, txtListPrice.Text);

      this line takes the default values from the textboxes and not the updated values…
      any reason why?

    5. HelpfulGuy says:

      Good article, but like everybody who posts, neglects the required using statements:

      using System.Data;
      using System.Data.SqlClient;
      using System.Configuration;

    6. NotACoder says:

      The function for FixGridHeader does not work when recordcount is 0. Get ‘System.Data.DataRowView’ does not contain a property with the name ‘ID.’ I am also using an identity field as my ID field.

      • Vanamali says:

        I do not think it has to do anything with record count. Is your code working fine if there is existing data? Please Check your SELECT statement and see if you are have included “ID” in it.

        • NotACoder says:

          I have included the ID field in my SELECT statement. I havent tried adding any rows to the table yet. It’s blank. Brand new application.

    7. Jack says:

      First of all Thanks for tutorial.
      I am trying to insert sth I have re-write this code step by step for my data. But texbox’ text is always null after writing some values to them.I am talking about textboxes like this

      • Vanamali says:

        code is missing after textboxes like this.. please try to put the between

         [Code][/Code] 

        tags , with a small c instead of capital

        • Jack says:

          <asp:Label ID="projectNameLbl" runat="server" Text='’ />

          I couldn’t implement your advise can you help me on code?

          • Jack says:

            Sorry I wrote wrong code, right one is below.

            <asp:TextBox ID="txtUserName" runat="server" Text='’ />

          • Vanamali says:

            Yes. Please email me your code.

            • Jack says:
              <asp:TemplateField HeaderText="User Name">
                                  <ItemTemplate>
                                      <%#Eval("userName")%></ItemTemplate>
                                  <EditItemTemplate>
                                      <asp:TextBox ID="txtUserName" runat="server" Text='<%#Eval("userName")%>' />
                                  </EditItemTemplate>
                                  <FooterTemplate>
                                      <asp:TextBox ID="txtNewUserName" runat="Server" />
                                  </FooterTemplate>
                              </asp:TemplateField> 
      • Vanamali says:

        The Edit Template looks correct, are you seeings values in the grid?

        When are you getting text box value as null, is it while inserting or while updating?

        • Jack says:

          I have working on inserting and get nulls. in footer textboxes texts are null. but I can display my data.

          • Vanamali says:

            Please check if you are rebinding the grid in the page_load all the time. You could should only bind it during page_load and not on subsequent postbacks.
            protected void Page_Load(object sender, EventArgs e)
            {
            if(!IsPostBack)
            BindData();
            }

    8. Anuj Kumar Rai says:

      dear sir when i m using to edit,update and cancelEdit template in gridview source code…and after that i write all .cs code then it is giving error

      Error 1 ‘ASP.default_aspx’ does not contain a definition for ‘GridView1_Editing’ and no extension method ‘GridView1_Editing’ accepting a first argument of type ‘ASP.default_aspx’ could be found (are you missing a using directive or an assembly reference?)

      Error 2 ‘ASP.default_aspx’ does not contain a definition for ‘GridView1_update’ and no extension method ‘GridView1_update’ accepting a first argument of type ‘ASP.default_aspx’ could be found (are you missing a using directive or an assembly reference?)

      • Vanamali says:

        You might have written OnRowEditing=”GridView1_Editing” in ASPX page but in the code behind you might have still kept the gvProducts_RowEditing as name for the event. Please make sure both of them are the same.

    9. sobhan says:

      How do I refer txtProductName from the code behind? It is not identified since it is inside the gridview control. Any help on this would be appreciated.

      • Vanamali says:

        Hi Sobhan,

        That is why we use findcontrol method of the gridview row to get reference to the control. something like this …

        TextBox txtProductName = (TextBox)row.FindControl(“txtProductName”);

    10. Manish Joshi says:

      nice article…

    11. Chetan says:

      Amazing solution

      Thanks…

    12. jlwill3 says:

      Thank you for a very useful article.

      It’s a shame the gridview does not have insert functionality built in, and that we have to result to a hack. One thing that gets lost using this method is the ability to use built-in field validation capability of asp.net. Validation doesn’t work on controls that are placed in the footer. Probably for the same reason as why the footer controls are not available in the code-behind. I’m sure that’s by design as the footer was never intended to contain these types of controls anyway.

      I just ended up adding some validation code to the method for creating a new record, and sending a javascript alert to the user if validation fails, and the reason why it failed.

    13. kate says:

      how to make a database where in you can add,view,update, and delete records??

    14. rayuduvkr says:

      Hi Sir,
      while im clicking Add click event ,gridview is disabling and it showing only one textbox ,still i can see add button in browser

    15. Ramdas Mutkule says:

      In my application all are working (Select, Delete , Cancel, edit) But problem is
      when i click edit button it is not edit this gridview there double click .
      then change the value of the row of grid, changed value not come in these textbox.
      textbox value is null,
      all code done but what happen is dont know
      –please help me

    16. Thanks this is great work on editable gridview. I however have a need to add a cancel button when I try to insert a new row. This cancel button is to be beside the Insert button. How can I handle this new cancel button’s event such that I can remove the added empty row.

    17. chaky90 says:

      how to insrert asp.net???
      for example wich standar please

    18. javaid says:

      Hi to every body
      Realy very usefull

    19. Hi its very nysc,…..
      Good work buddy

      can i get this code,if possible mail me.
      Mail id :[email protected]

    20. kooteswara says:

      god blesses the helping hands …like u u u

    21. nancy says:

      if i have no sql connection..simply wanna update my record in a grid…plz explain yaar hw s it possible…

    Leave a Reply