A simple & quick way to Insert Delete Update data in Asp.Net GridView using SqlDataSource C# VB.Net

For each & every database driven web application development based on Asp.Net, no one can avoid the Asp.Net powerful control GridView because of its set of builtin capabilities & a long list of extensions & improvements. The GridView control provides us the infrastructure for in-place Editing & Deleting. But for Insertion its left for developers(!!). Here i am showing how you can Insert Update Delete data using GridView with minimum coding. You can implement Update Delete without writing a single line of code, that’s why at first i am showing Update & delete operations & in the later section i will describe how to Insert using GridView Footer.

Related Article:
GridView Add Edit Delete using AJAX in Asp.Net C# VB.Net

Atfirst we need to create a table in SQL Server database. For this example i use the below table named tblSupplier:
GridView Insert Update Delete 1
Note: To test the example insert some test data into the above table.

Open the web.config file and add your connectionstring:

<add name="TestConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>

Now add a SqlDataSource control. Click on the smart menu. Goto Configure Data Source. Now from the dropdown menu select the connectionstring “TestConnection”:
GridView Insert Update Delete 2

Now click on next. From next select the tablename plus columns. Then click on advance and check Generate Insert, Update, and Delete statements checkbox. For this example this should be:
GridView Insert Delete Update 3

Now add a GridView in your page. Set the Data Source plus check Enable Paging, Enable Sorting, Enable Editing & Enable Deleting like below:
GridView Insert Update Delete 4
GridView Insert Update Delete 5

Now the code of the GridView looks like below:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID" 
        DataSourceID="SqlDataSource1" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
        <Columns>
            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                ReadOnly="True" SortExpression="ID" />
            <asp:BoundField DataField="Code" HeaderText="Code" SortExpression="Code" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Address" HeaderText="Address" 
                SortExpression="Address" />
            <asp:BoundField DataField="ContactNo" HeaderText="ContactNo" 
                SortExpression="ContactNo" />
            <asp:BoundField DataField="LastDelivery" HeaderText="LastDelivery" 
                SortExpression="LastDelivery" />
        </Columns>
    </asp:GridView>

Note: I have added some style to the GridView.

Now run the project. Our GridView is now ready for Updating/Editing & Deleting. Have a look below:
GridView Insert Update Delete Asp.Net Csharp VB_Net 1
Note: Now you can Edit & Delete data from your page which rquires just few clicks.

Now Inserting Data Using GridView Footer:
It requires few tricks to implement. First we need to modify our GridView code (make all columns as template column & add a new row into the GridView Footer for insert) like:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="SqlDataSource1" OnRowCommand="GridView1_RowCommand" ShowFooter="True" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
    <Columns>

    <asp:TemplateField HeaderText="Code">
    <ItemTemplate><%# Eval("Code") %></ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox runat="server" ID="Code" Text='<%# Bind("Code")%>' />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox runat="server" ID="txtCode" Text='' />
    </FooterTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Name">
    <ItemTemplate><%# Eval("Name") %></ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox runat="server" ID="EditName" Text='<%# Bind("Name")%>' />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox runat="server" ID="txtName" Text='' />
    </FooterTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Address">
    <ItemTemplate><%# Eval("Address") %></ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox runat="server" ID="EditAddress" Text='<%# Bind("Address")%>' />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox runat="server" ID="txtAddress" Text='' />
    </FooterTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Contact No">
    <ItemTemplate><%# Eval("ContactNo") %></ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox runat="server" ID="EditContactNo" Text='<%# Bind("ContactNo")%>' />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox runat="server" ID="txtContactNo" Text='' />
    </FooterTemplate>
    </asp:TemplateField>

    <asp:TemplateField HeaderText="Action">
    <ItemTemplate>
    <asp:Button runat="server" ID="cmdEdit" Text="Edit" CommandName="Edit" />
    <asp:Button runat="server" ID="cmdDelete" OnClientClick="return confirm('Are you sure to delete')" Text="Delete" CommandName="Delete" />
    </ItemTemplate>
    <EditItemTemplate>
    <asp:Button runat="server" ID="cmdUpdate" Text="Update" CommandName="Update" />
    <asp:Button runat="server" ID="cmdCancel" Text="Cancel" CommandName="Cancel" />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:Button runat="server" ID="cmdAdd" Text="Add Supplier" CommandName="New" />
    </FooterTemplate>
    </asp:TemplateField>
    </Columns>

    <EmptyDataTemplate>
        <table width="500px">
        <tr style="background:#FF6600;color:White"><th>Code</th><th>Name
        </th><th>Address</th><th>Contact No</th><th>Action</th></tr>
        <tr>
        <td><asp:TextBox runat="server" ID="txtCode" /></td>
        <td><asp:TextBox runat="server" ID="txtName" /></td>
        <td><asp:TextBox runat="server" ID="txtAddress" /></td>
        <td><asp:TextBox runat="server" ID="txtContactNo" /></td>
        <td><asp:Button runat="server" ID="cmdAdd" Text="Add Supplier" CommandName="EmptyNew" /></td>
        </tr>
        </table>
    </EmptyDataTemplate>

</asp:GridView>

Where each column implemented under Template Column. It gives us another opportunity to rearrange the GridView like Header Column, Column Ordering, Prompt user before Deleting a row of the GridView by confirm() method of javascript etc. Since we supplied Edit, Update, Cancel, & Delete command CommandName property to Edit,Update, Cancel, & Delete respectively into the GridView declaration to cooperate with SqlDataSource to enable users to Update and Delete data without any further implementation. Now look at the FooterTemplate of each column. This is because we are using FooterTemplate for our Insert operation. Add FotterTemplate for each column by server side control based on datatype. Since tblSupplier contains each column of datatype string so i need four TextBox here. In the last Template column headed by Action I added a Button with CommandName property=New. Now when you run this application what you see?

GridView Insert Update Delete 6

Look at the FooterTemplate of the GridView. Now we need to bind four TextBox data with SqlDataSource after clicking on the Add Supplier button. So first we need catch the click event of Add Supplier button. Gridview gives us an event to resolve such type of problem. The event name is RowCommand. Now we need to implement the RowCommand. But questions are how we can identify which button user pressed to fire this event? Answer is CommandName property. So the GridView1_RowCommand() method code is:

C# Code:

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "EmptyNew")
        {
            Code.Direction = ParameterDirection.Input;
            Code.Value = ((TextBox)GridView1.Controls[0].Controls[0].FindControl("txtCode")).Text;
            Name.Direction = ParameterDirection.Input;
            Name.Value = ((TextBox)GridView1.Controls[0].Controls[0].FindControl("txtName")).Text;
            Address.Direction = ParameterDirection.Input;
            Address.Value = ((TextBox)GridView1.Controls[0].Controls[0].FindControl("txtAddress")).Text;
            ContactNo.Direction = ParameterDirection.Input;
            ContactNo.Value = ((TextBox)GridView1.Controls[0].Controls[0].FindControl("txtContactNo")).Text;
            SqlDataSource1.Insert();
        }

        if (e.CommandName == "New")
        {
            Code.Direction = ParameterDirection.Input;
            Code.Value = ((TextBox)GridView1.FooterRow.FindControl("txtCode")).Text;
            Name.Direction = ParameterDirection.Input;
            Name.Value = ((TextBox)GridView1.FooterRow.FindControl("txtName")).Text;
            Address.Direction = ParameterDirection.Input;
            Address.Value = ((TextBox)GridView1.FooterRow.FindControl("txtAddress")).Text;
            ContactNo.Direction = ParameterDirection.Input;
            ContactNo.Value = ((TextBox)GridView1.FooterRow.FindControl("txtContactNo")).Text;
            LastDelivery.Direction = ParameterDirection.Input;
            LastDelivery.Value = DateTime.Now; 
            SqlDataSource1.Insert();
        }
    }

VB.Net Code:

    Protected Sub GridView1_RowCommand(sender As Object, e As System.Web.UI.WebControls.GridViewCommandEventArgs)
        If e.CommandName = "EmptyNew" Then
            Code.Direction = ParameterDirection.Input
            Code.Value = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtCode"), TextBox).Text
            Name.Direction = ParameterDirection.Input
            Name.Value = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtName"), TextBox).Text
            Address.Direction = ParameterDirection.Input
            Address.Value = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtAddress"), TextBox).Text
            ContactNo.Direction = ParameterDirection.Input
            ContactNo.Value = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtContactNo"), TextBox).Text
            SqlDataSource1.Insert()
        End If


        If e.CommandName = "New" Then
            Code.Direction = ParameterDirection.Input
            Code.Value = DirectCast(GridView1.FooterRow.FindControl("txtCode"), TextBox).Text
            Name.Direction = ParameterDirection.Input
            Name.Value = DirectCast(GridView1.FooterRow.FindControl("txtName"), TextBox).Text
            Address.Direction = ParameterDirection.Input
            Address.Value = DirectCast(GridView1.FooterRow.FindControl("txtAddress"), TextBox).Text
            ContactNo.Direction = ParameterDirection.Input
            ContactNo.Value = DirectCast(GridView1.FooterRow.FindControl("txtContactNo"), TextBox).Text
            LastDelivery.Direction = ParameterDirection.Input
            LastDelivery.Value = DateTime.Now
            SqlDataSource1.Insert()
        End If

    End Sub

The CommandName “EmptyNew” i will discuss later in this post. To add SqlParameter into the SqlDataSource i need to use four SqlParameter(or you can use List collection) variable. Declare the below five variables within page scope.
C# Code:

    private System.Data.SqlClient.SqlParameter Code = new System.Data.SqlClient.SqlParameter("Code", SqlDbType.Char, 6);
    private System.Data.SqlClient.SqlParameter Name = new System.Data.SqlClient.SqlParameter("Name", SqlDbType.VarChar, 100);
    private System.Data.SqlClient.SqlParameter Address = new System.Data.SqlClient.SqlParameter("Address", SqlDbType.VarChar, 200);
    private System.Data.SqlClient.SqlParameter ContactNo = new System.Data.SqlClient.SqlParameter("ContactNo", SqlDbType.Char, 6);
    private System.Data.SqlClient.SqlParameter LastDelivery = new System.Data.SqlClient.SqlParameter("LastDelivery", SqlDbType.DateTime);

VB.Net Code:

    Private Code As New System.Data.SqlClient.SqlParameter("Code", SqlDbType.[Char], 6)
    Private Name As New System.Data.SqlClient.SqlParameter("Name", SqlDbType.VarChar, 100)
    Private Address As New System.Data.SqlClient.SqlParameter("Address", SqlDbType.VarChar, 200)
    Private ContactNo As New System.Data.SqlClient.SqlParameter("ContactNo", SqlDbType.[Char], 6)
    Private LastDelivery As New System.Data.SqlClient.SqlParameter("LastDelivery", SqlDbType.DateTime)

When I catch the event for Add Supplier button then populate the five parameters & also Inserting event auto fired for the SqlDataSource object. Keep in mind that we need to override the Inserting handler to assign our data into the SqlDataSource object. So add below code for the SqlDataSource Inserting Event:
C# Code:

    protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
        e.Command.Parameters.Clear();
        e.Command.Parameters.Add(Code);
        e.Command.Parameters.Add(Name);
        e.Command.Parameters.Add(Address);
        e.Command.Parameters.Add(ContactNo);
        e.Command.Parameters.Add(LastDelivery);
    }

VB.Net Code:

    Protected Sub SqlDataSource1_Inserting(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting
        e.Command.Parameters.Clear()
        e.Command.Parameters.Add(Code)
        e.Command.Parameters.Add(Name)
        e.Command.Parameters.Add(Address)
        e.Command.Parameters.Add(ContactNo)
        e.Command.Parameters.Add(LastDelivery)
    End Sub

Outcome/Objective:
G6

Now everything is completed but the problem is if the table has no row in database then the Gridview show nothing. The solution is EmptyDataTemplate. See the above GridView HTML code. Here i also added another Button which CommandName is EmptyNew. Hope now you can understand why i implement the GridView1_RowCommand in this way.

Download Code Example C#        Download Code Example VB.Net

2 thoughts on “A simple & quick way to Insert Delete Update data in Asp.Net GridView using SqlDataSource C# VB.Net

  • March 17, 2015 at 2:11 am
    Permalink

    This is exactly what I want except I want the edit/delete insert buttons to remain on the left. Please tell me what I need to do different to accomplish this. All the examples I see are on the right, not the traditional left side.

    Reply
  • June 11, 2015 at 5:53 pm
    Permalink

    Denise, Just move the template column with the buttons from the last column to the first. It’s that simple!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code