Home Asp.net Asp.net GridView Inline Insert Update Delete Data In C# Vb

Asp.net GridView Inline Insert Update Delete Data In C# Vb

124
5
Gridview Inline Add/Insert Edit/Update Delete Data in Asp.net C# Vb.net
Gridview Inline Add/Insert Edit/Update Delete Data in Asp.net C# Vb.net

In my previous tutorials, I’d explained about how to bind data to gridview, how to get connection string, insert update delete gridview data using parameterized query, difference between executereader executenonquery and executescalar and other more cracking tutorials on GridView, Asp.net, SQL Server here.

Now here in this tutorial, I’ll explain how to show data in gridview and then perform CRUD operations on gridview data. I’ll cover asp.net gridview inline insert update delete data in c# as well as vb with example code.

To explain further about how to bind data in asp.net gridview and perform gridview inline insert update and delete operations, we need to create database table to read data and bind retrieved resultset to gridview, so simply execute following script to sql query editor to create database table and then add few records manually or download complete example code with script at the end of the page.

Here is the script to create “SubjectDetails” table:

CREATE TABLE [dbo].[SubjectDetails]
(
    [SubjectId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [SubjectName] [nvarchar](100) NULL,
    [Marks] [int] NULL,
    [Grade] [nvarchar](50) NULL
)
Note: To avoid SQL Injections and security purpose, I used parameterized query in my following example code. Check what is sql injection and how to prevent it? with example.Best practice is to use parameterized query to perform select, insert, update or delete operations instead simple sql statements.

Asp.net GridView Inline Add/Insert Edit/Update Remove/Delete

Following is the complete HTML Markup code that I used to perform gridview inline editing in my .aspx page:

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Gridview Inline Add/Insert, Edit/Update, Delete Data Example</title>
    <style type="text/css">
        td a {
            padding: 10px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
        <h4>Gridview Inline Add/Insert, Edit/Update, Delete Data Example</h4>
        <asp:Label ID="lblMsg" runat="server"></asp:Label><br />
        <asp:GridView ID="gvSubDetails" runat="server" DataKeyNames="SubjectId"
            OnRowEditing="gvSubDetails_RowEditing" OnRowCommand="gvSubDetails_RowCommand"
            OnRowDeleting="gvSubDetails_RowDeleting" OnRowUpdating="gvSubDetails_RowUpdating"
            OnRowCancelingEdit="gvSubDetails_RowCancelingEdit" AutoGenerateColumns="false" ShowFooter="true">
            <HeaderStyle BackColor="#9a9a9a" ForeColor="White" Font-Bold="true" Height="30" />
            <AlternatingRowStyle BackColor="#f5f5f5" />
            <Columns>
                <asp:TemplateField>
                    <EditItemTemplate>
                        <asp:LinkButton ID="lbtnUpdate" runat="server" CommandName="Update" Text="Update" />
                        <asp:LinkButton ID="lbtnCancel" runat="server" CommandName="Cancel" Text="Cancel" />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:LinkButton ID="lbtnEdit" runat="server" CommandName="Edit" Text="Edit" />
                        <asp:LinkButton ID="lbtnDelete" runat="server" CommandName="Delete"
                            OnClientClick="return confirm('Are you sure you want to delete this record?')"
                            Text="Delete" CausesValidation="false" />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:LinkButton ID="lbtnAdd" runat="server" CommandName="Add"
                            Text="Add New" ValidationGroup="vgAdd" />
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="SubjectName">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtSubjectName" Text='<%#Eval("SubjectName") %>' runat="server" />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblSubjectName" Text='<%#Eval("SubjectName") %>' runat="server" />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="newSubjectName" runat="server" />
                        <asp:RequiredFieldValidator ID="rfvSubjectName" runat="server" Text="*"
                            ControlToValidate="newSubjectName" ValidationGroup="vgAdd" ForeColor="Red" />
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Marks">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtMarks" runat="server" Text='<%#Eval("Marks") %>' />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblMarks" runat="server" Text='<%#Eval("Marks") %>' />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="newMarks" runat="server" />
                        <asp:RequiredFieldValidator ID="rfvMarks" runat="server" Text="*"
                            ControlToValidate="newMarks" ForeColor="Red" ValidationGroup="vgAdd" />
                        <asp:RegularExpressionValidator ID="revMarks" runat="server" ForeColor="Red"
                            ControlToValidate="newMarks" ValidationExpression="^[0-9]*$" Text="*Numbers"
                            ValidationGroup="vgAdd" />
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Grade">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtGrade" runat="server" Text='<%#Eval("Grade") %>' />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblGrade" runat="server" Text='<%#Eval("Grade") %>' />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="newGrade" runat="server" />
                        <asp:RequiredFieldValidator ID="rfvGrade" runat="server" ForeColor="Red"
                            ControlToValidate="newGrade" Text="*" ValidationGroup="vgAdd" />
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Add the connection string to web.config file, here is the sample code(Note: Change your datasource and database to connect with sql server):

<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>
  <connectionStrings>
    <add name="conn" providerName="System.Data.SqlClient" 
         connectionString="Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB" />
  </connectionStrings>
</configuration>

Asp.net GridView Inline Insert Update Delete Data In C# – [.cs]

First add the following namespace that is required to connect with sql server:

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

Now check the below sample code snippet to perform gridview inline insert update delete:

string conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

protected void Page_Load(object sender, EventArgs e) {
    lblMsg.Text = "";
    if (!IsPostBack) {
        BindSubjectData();
    }
}
    
//call to bind gridview
protected void BindSubjectData() {
    using (SqlConnection sqlCon = new SqlConnection(conn)) {
        using (SqlCommand cmd = new SqlCommand()) {
            cmd.CommandText = "SELECT * FROM SubjectDetails";
            cmd.Connection = sqlCon;
            sqlCon.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0) {
                gvSubDetails.DataSource = dt;
                gvSubDetails.DataBind();
            } else {
                DataRow dr = dt.NewRow();
                dt.Rows.Add(dr);
                gvSubDetails.DataSource = dt;
                gvSubDetails.DataBind();
                gvSubDetails.Rows[0].Visible = false;
            }
            sqlCon.Close();
        }
    }
}

//called on row edit command
protected void gvSubDetails_RowEditing(object sender, GridViewEditEventArgs e) {
    gvSubDetails.EditIndex = e.NewEditIndex;
    BindSubjectData();
}

//called when cancel edit mode
protected void gvSubDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {
    gvSubDetails.EditIndex = -1;
    BindSubjectData();
}

//called on row add new command
protected void gvSubDetails_RowCommand(object sender, GridViewCommandEventArgs e) {
    if (e.CommandName == "Add") {
        bool IsAdded = false;
        TextBox SubjectName =
        (TextBox)gvSubDetails.FooterRow.FindControl("newSubjectName");
        TextBox Marks = (TextBox)gvSubDetails.FooterRow.FindControl("newMarks");
        TextBox Grade = (TextBox)gvSubDetails.FooterRow.FindControl("newGrade");
        using (SqlConnection sqlCon = new SqlConnection(conn)) {
            using (SqlCommand cmd = new SqlCommand()) {
                //here i'd added "@" for continuous string in new line
                cmd.CommandText = @"INSERT INTO SubjectDetails(SubjectName,Marks,Grade)
                                    VALUES(@SubjectName,@Marks,@Grade)";
                cmd.Parameters.AddWithValue("@SubjectName", SubjectName.Text);
                cmd.Parameters.AddWithValue("@Marks", Marks.Text);
                cmd.Parameters.AddWithValue("@Grade", Grade.Text);
                cmd.Connection = sqlCon;
                sqlCon.Open();
                IsAdded = cmd.ExecuteNonQuery() > 0;
                sqlCon.Close();
            }
        }
        if (IsAdded) {
            lblMsg.Text = "'" + SubjectName.Text + "' subject details added successfully!";
            lblMsg.ForeColor = System.Drawing.Color.Green;

            BindSubjectData();
        } else {
            lblMsg.Text = "Error while adding '" + SubjectName.Text + "' subject details";
            lblMsg.ForeColor = System.Drawing.Color.Red;
        }
    }
}

//called on row update command
protected void gvSubDetails_RowUpdating(object sender, GridViewUpdateEventArgs e) {
    bool IsUpdated = false;
    //getting key value, row id
    int SubjectID =
    Convert.ToInt32(gvSubDetails.DataKeys[e.RowIndex].Value.ToString());
    //getting row field details
    TextBox SubjectName =
    (TextBox)gvSubDetails.Rows[e.RowIndex].FindControl("txtSubjectName");
    TextBox Marks = (TextBox)gvSubDetails.Rows[e.RowIndex].FindControl("txtMarks");
    TextBox Grade = (TextBox)gvSubDetails.Rows[e.RowIndex].FindControl("txtGrade");
    using (SqlConnection sqlCon = new SqlConnection(conn)) {
        using (SqlCommand cmd = new SqlCommand()) {
            //here i'd added "@" for continuous string in new line
            cmd.CommandText = @"UPDATE SubjectDetails SET SubjectName=@SubjectName,
                                Marks=@Marks,Grade=@Grade WHERE SubjectID=@SubjectId";
            cmd.Parameters.AddWithValue("@SubjectId", SubjectID);
            cmd.Parameters.AddWithValue("@SubjectName", SubjectName.Text);
            cmd.Parameters.AddWithValue("@Marks", Marks.Text);
            cmd.Parameters.AddWithValue("@Grade", Grade.Text);
            cmd.Connection = sqlCon;
            sqlCon.Open();
            IsUpdated = cmd.ExecuteNonQuery() > 0;
            sqlCon.Close();
        }
    }
    if (IsUpdated) {
        lblMsg.Text = "'" + SubjectName.Text + "' subject details updated successfully!";
        lblMsg.ForeColor = System.Drawing.Color.Green;
    } else {
        lblMsg.Text = "Error while updating '" + SubjectName.Text + "' subject details";
        lblMsg.ForeColor = System.Drawing.Color.Red;
    }
    gvSubDetails.EditIndex = -1;
    BindSubjectData();
}

//called on row delete command
protected void gvSubDetails_RowDeleting(object sender, GridViewDeleteEventArgs e) {
    bool IsDeleted = false;
    //getting key value, row id
    int SubjectID = Convert.ToInt32(gvSubDetails.DataKeys[e.RowIndex].Value.ToString());
    //getting row field subjectname
    Label SubjectName = (Label)gvSubDetails.Rows[e.RowIndex].FindControl("lblSubjectName");
    using (SqlConnection sqlCon = new SqlConnection(conn)) {
        using (SqlCommand cmd = new SqlCommand()) {
            cmd.CommandText = "DELETE FROM SubjectDetails WHERE SubjectId=@SubjectID";
            cmd.Parameters.AddWithValue("@SubjectID", SubjectID);
            cmd.Connection = sqlCon;
            sqlCon.Open();
            IsDeleted = cmd.ExecuteNonQuery() > 0;
            sqlCon.Close();
        }
    }
    if (IsDeleted) {
        lblMsg.Text = "'" + SubjectName.Text + "' subject details has been deleted successfully!";
        lblMsg.ForeColor = System.Drawing.Color.Green;
        BindSubjectData();
    } else {
        lblMsg.Text = "Error while deleting '" + SubjectName.Text + "' subject details";
        lblMsg.ForeColor = System.Drawing.Color.Red;
    }
}

Asp.net GridView Inline Insert Update Delete Data In Vb.net – [.vb]

First add the following namespace that is required to connect with sql server:

Imports System.Data.SqlClient

Now check the below sample code snippet to perform gridview inline insert update delete:

Private conn As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    lblMsg.Text = ""
    If Not IsPostBack Then
        BindSubjectData()
    End If
End Sub

'call to bind gridview'
Protected Sub BindSubjectData()
    Using sqlCon As New SqlConnection(conn)
        Using cmd As New SqlCommand()
            cmd.CommandText = "SELECT * FROM SubjectDetails"
            cmd.Connection = sqlCon
            sqlCon.Open()
            Dim da As New SqlDataAdapter(cmd)
            Dim dt As New DataTable()
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                gvSubDetails.DataSource = dt
                gvSubDetails.DataBind()
            Else
                Dim dr As DataRow = dt.NewRow()
                dt.Rows.Add(dr)
                gvSubDetails.DataSource = dt
                gvSubDetails.DataBind()
                gvSubDetails.Rows(0).Visible = False
            End If
            sqlCon.Close()
        End Using
    End Using
End Sub

'called on row edit command'
Protected Sub gvSubDetails_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    gvSubDetails.EditIndex = e.NewEditIndex
    BindSubjectData()
End Sub

'called when cancel edit mode'
Protected Sub gvSubDetails_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    gvSubDetails.EditIndex = -1
    BindSubjectData()
End Sub

'called on row add new command'
Protected Sub gvSubDetails_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
    If e.CommandName = "Add" Then
        Dim IsAdded As Boolean = False
        Dim SubjectName As TextBox = CType(gvSubDetails.FooterRow.FindControl("newSubjectName"), TextBox)
        Dim Marks As TextBox = CType(gvSubDetails.FooterRow.FindControl("newMarks"), TextBox)
        Dim Grade As TextBox = CType(gvSubDetails.FooterRow.FindControl("newGrade"), TextBox)
        Using sqlCon As New SqlConnection(conn)
            Using cmd As New SqlCommand()
                cmd.CommandText = "INSERT INTO SubjectDetails(SubjectName,Marks,Grade)" &
                                    "VALUES(@SubjectName,@Marks,@Grade)"
                cmd.Parameters.AddWithValue("@SubjectName", SubjectName.Text)
                cmd.Parameters.AddWithValue("@Marks", Marks.Text)
                cmd.Parameters.AddWithValue("@Grade", Grade.Text)
                cmd.Connection = sqlCon
                sqlCon.Open()
                IsAdded = cmd.ExecuteNonQuery() > 0
                sqlCon.Close()
            End Using
        End Using
        If IsAdded Then
            lblMsg.Text = "'" & SubjectName.Text & "' subject details has been added successfully!"
            lblMsg.ForeColor = System.Drawing.Color.Green

            BindSubjectData()
        Else
            lblMsg.Text = "Error while adding '" & SubjectName.Text & "' subject details"
            lblMsg.ForeColor = System.Drawing.Color.Red
        End If
    End If
End Sub

'called on row update command'
Protected Sub gvSubDetails_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    Dim IsUpdated As Boolean = False
    'getting key value, row id'
    Dim SubjectID As Integer = Convert.ToInt32(gvSubDetails.DataKeys(e.RowIndex).Value.ToString())
    'getting row field details'
    Dim SubjectName As TextBox = CType(gvSubDetails.Rows(e.RowIndex).FindControl("txtSubjectName"), TextBox)
    Dim Marks As TextBox = CType(gvSubDetails.Rows(e.RowIndex).FindControl("txtMarks"), TextBox)
    Dim Grade As TextBox = CType(gvSubDetails.Rows(e.RowIndex).FindControl("txtGrade"), TextBox)
    Using sqlCon As New SqlConnection(conn)
        Using cmd As New SqlCommand()
            cmd.CommandText = "UPDATE SubjectDetails SET SubjectName=@SubjectName," &
                                "Marks=@Marks,Grade=@Grade WHERE SubjectID=@SubjectId"
            cmd.Parameters.AddWithValue("@SubjectId", SubjectID)
            cmd.Parameters.AddWithValue("@SubjectName", SubjectName.Text)
            cmd.Parameters.AddWithValue("@Marks", Marks.Text)
            cmd.Parameters.AddWithValue("@Grade", Grade.Text)
            cmd.Connection = sqlCon
            sqlCon.Open()
            IsUpdated = cmd.ExecuteNonQuery() > 0
            sqlCon.Close()
        End Using
    End Using
    If IsUpdated Then
        lblMsg.Text = "'" & SubjectName.Text & "' subject details has been updated successfully!"
        lblMsg.ForeColor = System.Drawing.Color.Green
    Else
        lblMsg.Text = "Error while updating '" & SubjectName.Text & "' subject details"
        lblMsg.ForeColor = System.Drawing.Color.Red
    End If
    gvSubDetails.EditIndex = -1
    BindSubjectData()
End Sub

'called on row delete command'
Protected Sub gvSubDetails_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
    Dim IsDeleted As Boolean = False
    'getting key value, row id'
    Dim SubjectID As Integer = Convert.ToInt32(gvSubDetails.DataKeys(e.RowIndex).Value.ToString())
    'getting row field subjectname'
    Dim SubjectName As Label = CType(gvSubDetails.Rows(e.RowIndex).FindControl("lblSubjectName"), Label)
    Using sqlCon As New SqlConnection(conn)
        Using cmd As New SqlCommand()
            cmd.CommandText = "DELETE FROM SubjectDetails WHERE SubjectId=@SubjectID"
            cmd.Parameters.AddWithValue("@SubjectID", SubjectID)
            cmd.Connection = sqlCon
            sqlCon.Open()
            IsDeleted = cmd.ExecuteNonQuery() > 0
            sqlCon.Close()
        End Using
    End Using
    If IsDeleted Then
        lblMsg.Text = "'" & SubjectName.Text & "' subject details has been deleted successfully!"
        lblMsg.ForeColor = System.Drawing.Color.Green
        BindSubjectData()
    Else
        lblMsg.Text = "Error while deleting '" & SubjectName.Text & "' subject details"
        lblMsg.ForeColor = System.Drawing.Color.Red
    End If
End Sub

Example Result

Gridview Inline Add/Insert Edit/Update Delete Data in Asp.net C# Vb.net
Gridview Inline Add/Insert Edit/Update Delete Data in Asp.net C# Vb.net

Download Example Code

Previous articleSimple Insert Update Delete Gridview Records In Asp.net C# Vb
Next articleShow Alert Message in JavaScript or jQuery
Hi there, I am Mayank, the man behind Technical Mack. I started AspnetO with a motive to educate people on various programming languages ranging from beginners to expert level. Through this blog, I aim to provide more insightful content.

5 COMMENTS

  1. Just a suggestion when writing useful posts like this post a solution that is generalized like reading from a excel file and saving to the same excel so the db connection is not intended

LEAVE A REPLY

Please enter your comment!
Please enter your name here

two × one =