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

GridView is one of the most popular Asp.Net Control to take user inputs in a tabular format. You can Add records, Delete Records as well as Update Records in a GridView easily. In this article I will explain “How you can Add Edit Delete GridView Records using Asp.Net AJAX“. We can do Edit Update & Cancel operations in a GridView easily. But inserting data from GridView is a hectic job. To do that we need to add GridView Footer template. So let’s start to implement the GridView Add Edit Delete DataBase Data using AJAX in Asp.Net C# VB.Net.

Objective/Outcome:
GridView Add Edit Delete using AJAX in Asp.Net Csharp VB.Net

To do the Example First create a DataBase Table:

CREATE TABLE [dbo].[Student](
    [Roll] [bigint] NOT NULL,
    [Name] [varchar](max) NOT NULL,
    [Email] [varchar](max) NOT NULL,
    [Address] [varchar](max) NOT NULL,
    [AdDate] [datetime] NOT NULL
) ON [PRIMARY]

Insert Some Data into DataBase:

INSERT INTO Student VALUES(1001,'Abul Kalam Azad','akalam@gmail.com','Donia, Dhaka',GETDATE()-12)
INSERT INTO Student VALUES(1002,'Md. Afsarul Alam','afsar@hotmail.com','Mirpur, Dhaka',GETDATE()-12)
INSERT INTO Student VALUES(1003,'Md. Jahangir Alam','jalam@yahoo.com','Shonir Akhra, Dhaka',GETDATE()-25)
INSERT INTO Student VALUES(1004,'Akhtarul Islam','akhtar123@live.com','Savar, Dhaka',GETDATE()-33)
INSERT INTO Student VALUES(1005,'A.K.M. Parvez','parvez@gmail.com','Uttara, Dhaka',GETDATE()-5)

Now Modify the web.config file to connect to your Database like below:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now prepare the GridView HTML Markup like below:

    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="Roll" RowStyle-BackColor="#DAA520"  AlternatingRowStyle-BackColor = "#FFD700" HeaderStyle-BackColor="#6495ED" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5" 
ShowFooter = "true" onrowediting="EditStudent" onrowupdating="UpdateStudent"  onrowcancelingedit="CancelStudent">
<Columns>

<asp:TemplateField HeaderText = "Roll">
    <ItemTemplate>
        <asp:Label ID="lblRoll" runat="server" Text='<%# Eval("Roll")%>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID="txtRoll" runat="server"  Text='<%# Eval("Roll")%>'></asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
        <asp:TextBox ID="txtRoll" runat="server"></asp:TextBox>
    </FooterTemplate>
</asp:TemplateField>

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

<asp:TemplateField HeaderText = "Email">
    <ItemTemplate>
        <asp:Label ID="lblEmail" runat="server" Text='<%# Eval("Email")%>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID="txtEmail" runat="server"  Text='<%# Eval("Email")%>'></asp:TextBox>
    </EditItemTemplate> 
    <FooterTemplate>
        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
    </FooterTemplate>
</asp:TemplateField>

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

<asp:TemplateField ItemStyle-HorizontalAlign="Center">
    <ItemTemplate>
        <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl="Images/delete.png" ToolTip="Delete Record" Width="15px" Height="15px" CommandArgument = '<%# Eval("Roll")%>' OnClientClick = "return confirm('Are you sure to Delete?')" OnClick = "DeleteStudent"/>
    </ItemTemplate>

    <FooterTemplate>
        <asp:ImageButton ID="ImageButton2" runat="server" ImageUrl="Images/Add.jpg" ToolTip="Add Record" OnClick = "AddNewStudent" Width="15px" Height="15px"/>
    </FooterTemplate>
</asp:TemplateField>
<asp:CommandField  ShowEditButton="True" ButtonType="Image" EditText="Edit Record" CancelText="Cancel Update" UpdateText="Update Record" EditImageUrl="Images/edit.jpg" ControlStyle-Height="20px" ControlStyle-Width="20px" CancelImageUrl="Images/cancel.png" UpdateImageUrl="Images/edit.jpg"/>
</Columns>
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>

Note: Here I have added a custom column for delete functionality. Because here I will provide JavaScript confirmation box to the user when clicks on Delete button. Also used CommandArgument property to trace out the exact GridView Row to Delete. Used footer template to take user input’s to Insert data into DataBase through GridView. ShowEditButton=”True” provides us Edit, Update & Cancel button by default.

Now Bind the GridView Data from DataBase:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            RefreshData();
        }
    }    

private void RefreshData()
    {
        string consString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(consString))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM [Student]", conn);
            cmd.CommandType = CommandType.Text;
            conn.Open();
            SqlDataReader drStudents = cmd.ExecuteReader();
            GridView1.DataSource = drStudents;
            GridView1.DataBind();
        }
    }

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient” & “System.Configuration” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            RefreshData()
        End If
    End Sub

    Private Sub RefreshData()
        Dim consString As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Using conn As New SqlConnection(consString)
            Dim cmd As New SqlCommand("SELECT * FROM [Student]", conn)
            cmd.CommandType = CommandType.Text
            conn.Open()
            Dim drStudents As SqlDataReader = cmd.ExecuteReader()
            GridView1.DataSource = drStudents
            GridView1.DataBind()
        End Using
    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient” & “System.Configuration” namespaces.

Now Add or Insert Records into GridView using Ajax:
C# Code:

    protected void AddNewStudent(object sender, EventArgs e)
    {
        System.Threading.Thread.Sleep(1000);// To Test AJAX effect
        string sRoll = ((TextBox)GridView1.FooterRow.FindControl("txtRoll")).Text;
        string sName = ((TextBox)GridView1.FooterRow.FindControl("txtName")).Text;
        string sEmail= ((TextBox)GridView1.FooterRow.FindControl("txtEmail")).Text;
        string sAddress= ((TextBox)GridView1.FooterRow.FindControl("txtAddress")).Text;

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO Student(Roll, Name, Email, Address,AdDate) " +
            "VALUES(@Roll, @Name, @Email,@Address,@AdDate);";

            cmd.Parameters.Add("@Roll", SqlDbType.Int).Value = Convert.ToInt32(sRoll);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = sName;
            cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = sEmail;
            cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = sAddress;
            cmd.Parameters.Add("@AdDate", SqlDbType.DateTime).Value = DateTime.Now;

            con.Open();
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
        }
        RefreshData();
    }

VB.Net Code:

    Protected Sub AddNewStudent(sender As Object, e As EventArgs)
        System.Threading.Thread.Sleep(1000)
        ' To Test AJAX effect
        Dim sRoll As String = DirectCast(GridView1.FooterRow.FindControl("txtRoll"), TextBox).Text
        Dim sName As String = DirectCast(GridView1.FooterRow.FindControl("txtName"), TextBox).Text
        Dim sEmail As String = DirectCast(GridView1.FooterRow.FindControl("txtEmail"), TextBox).Text
        Dim sAddress As String = DirectCast(GridView1.FooterRow.FindControl("txtAddress"), TextBox).Text

        Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "INSERT INTO Student(Roll, Name, Email, Address,AdDate) " & "VALUES(@Roll, @Name, @Email,@Address,@AdDate);"

            cmd.Parameters.Add("@Roll", SqlDbType.Int).Value = Convert.ToInt32(sRoll)
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = sName
            cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = sEmail
            cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = sAddress
            cmd.Parameters.Add("@AdDate", SqlDbType.DateTime).Value = DateTime.Now

            con.Open()
            cmd.Connection = con
            cmd.ExecuteNonQuery()
        End Using
        RefreshData()
    End Sub

Now Set GridView Edit Mode using Ajax:
C# Code:

    protected void EditStudent(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        RefreshData();
    }

VB.Net Code:

    Protected Sub EditStudent(sender As Object, e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        RefreshData()
    End Sub

Now Update GridView Records using Ajax:
C# Code:

    protected void UpdateStudent(object sender, GridViewUpdateEventArgs e)
    {
        string sRoll = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRoll")).Text;
        string sName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtName")).Text;
        string sEmail = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtEmail")).Text;
        string sAddress = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtAddress")).Text;
        int nRoll = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE Student set Roll=@Roll," +
            "Name=@Name,Email=@Email,Address=@Address,AdDate=@AdDate where Roll=@PrevRoll;";

            cmd.Parameters.Add("@Roll", SqlDbType.Int).Value = Convert.ToInt32(sRoll);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = sName;
            cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = sEmail;
            cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = sAddress;
            cmd.Parameters.Add("@AdDate", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@PrevRoll", SqlDbType.Int).Value = nRoll;

            con.Open();
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            GridView1.EditIndex = -1;
        }
        RefreshData();
    }

VB.Net Code:

    Protected Sub UpdateStudent(sender As Object, e As GridViewUpdateEventArgs)
        Dim sRoll As String = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtRoll"), TextBox).Text
        Dim sName As String = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtName"), TextBox).Text
        Dim sEmail As String = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtEmail"), TextBox).Text
        Dim sAddress As String = DirectCast(GridView1.Rows(e.RowIndex).FindControl("txtAddress"), TextBox).Text
        Dim nRoll As Integer = Convert.ToInt32(GridView1.DataKeys(e.RowIndex).Value)

        Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "UPDATE Student set Roll=@Roll," & "Name=@Name,Email=@Email,Address=@Address,AdDate=@AdDate where Roll=@PrevRoll;"

            cmd.Parameters.Add("@Roll", SqlDbType.Int).Value = Convert.ToInt32(sRoll)
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = sName
            cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = sEmail
            cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = sAddress
            cmd.Parameters.Add("@AdDate", SqlDbType.DateTime).Value = DateTime.Now
            cmd.Parameters.Add("@PrevRoll", SqlDbType.Int).Value = nRoll

            con.Open()
            cmd.Connection = con
            cmd.ExecuteNonQuery()
            GridView1.EditIndex = -1
        End Using
        RefreshData()
    End Sub

Now Cancel Update GridView Mode using Ajax:
C# Code:

    protected void CancelStudent(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        RefreshData();
    }

VB.Net Code:

    Protected Sub CancelStudent(sender As Object, e As GridViewCancelEditEventArgs)
        GridView1.EditIndex = -1
        RefreshData()
    End Sub

Now Delete GridView Records using Ajax:
C# Code:

    protected void DeleteStudent(object sender, EventArgs e)
    {
        int nRoll = Convert.ToInt32(((ImageButton)sender).CommandArgument);

        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "DELETE FROM Student WHERE Roll=@Roll;";

            cmd.Parameters.Add("@Roll", SqlDbType.Int).Value = nRoll;

            con.Open();
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
        }
        RefreshData();
    }

VB.Net Code:

    Protected Sub DeleteStudent(sender As Object, e As EventArgs)
        Dim nRoll As Integer = Convert.ToInt32(DirectCast(sender, ImageButton).CommandArgument)

        Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "DELETE FROM Student WHERE Roll=@Roll;"

            cmd.Parameters.Add("@Roll", SqlDbType.Int).Value = nRoll

            con.Open()
            cmd.Connection = con
            cmd.ExecuteNonQuery()
        End Using
        RefreshData()
    End Sub

Hope now you can perform Ajax Add Edit Delete Update Operations in a DataBase by a GridView Control using Asp.Net C# VB.Net applications.

Download Code Example C#        Download Code Example VB.Net

2 thoughts on “GridView Add Edit Delete using AJAX in Asp.Net C# VB.Net

  • September 22, 2016 at 12:26 pm
    Permalink

    very useful code for me

    Reply
  • November 18, 2016 at 1:53 pm
    Permalink

    hello Sir, This is a good title this is very helpful for me. thanks for the give this post on side.

    Reply

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code