Home Asp.net Simple Insert Update Delete Gridview Records In Asp.net C# Vb

Simple Insert Update Delete Gridview Records In Asp.net C# Vb

158
12
Add/Insert Edit/Update Delete Show Gridview Data in Asp.net C# Vb.net
Add/Insert Edit/Update Delete Show Gridview 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, gridview inline insert update delete, difference between executereader executenonquery 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 insert update delete gridview records (perform CRUD operation) in asp.net using c# as well as vb.net with example code.

To explain further about how to bind data in asp.net gridview and perform 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.

Simple Insert Update Delete Gridview Records In Asp.net – [.aspx]

Following is the complete HTML Markup code that I used to perform simple insert update delete gridview records (CRUD operation) for this demonstration in my .aspx page:

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Simple Add/Insert, Edit/Update, Delete Gridview Data Example</title>
    <style type="text/css">
        td a {
            padding: 10px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h4>Simple Add/Insert, Edit/Update, Delete Gridview Data Example</h4>
        <asp:Label ID="lblMsg" runat="server"></asp:Label>
        <table>
            <tr>
                <td>Subject Id:</td>
                <td>
                    <asp:TextBox ID="txtSubjectId" runat="server" Enabled="false" />
                </td>
            </tr>
            <tr>
                <td>Subject Name:</td>
                <td>
                    <asp:TextBox ID="txtSubjectName" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvSubjectName" runat="server" Text="*"
                        ControlToValidate="txtSubjectName" ForeColor="Red" ValidationGroup="vgAdd" />
                </td>
            </tr>
            <tr>
                <td>Marks:</td>
                <td>
                    <asp:TextBox ID="txtMarks" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvMarks" runat="server" Text="*"
                        ControlToValidate="txtMarks" ForeColor="Red" ValidationGroup="vgAdd" />
                    <asp:RegularExpressionValidator ID="revMarks" runat="server" ForeColor="Red"
                        ValidationExpression="^[0-9]*$" Text="*Numbers" ControlToValidate="txtMarks"
                        ValidationGroup="vgAdd" />
                </td>
            </tr>
            <tr>
                <td>Grade:</td>
                <td>
                    <asp:TextBox ID="txtGrade" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvGrade" runat="server" Text="*"
                        ControlToValidate="txtGrade" ForeColor="Red" ValidationGroup="vgAdd" />
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click"
                        Text="Insert" ValidationGroup="vgAdd" />
                    <asp:Button ID="btnUpdate" runat="server" OnClick="btnUpdate_Click"
                        Text="Update" ValidationGroup="vgAdd" />
                    <asp:Button ID="btnDelete" runat="server" OnClick="btnDelete_Click"
                        OnClientClick="return confirm('Are you sure you want to delete this record?')"
                        Text="Delete" ValidationGroup="vgAdd" />
                    <asp:Button ID="btnCancel" runat="server" OnClick="btnCancel_Click"
                        Text="Cancel" CausesValidation="false" />
                </td>
            </tr>
        </table>
        <br />
        <asp:GridView ID="gvSubDetails" DataKeyNames="SubjectId" AutoGenerateColumns="false"
            OnSelectedIndexChanged="gvSubDetails_SelectedIndexChanged" Width="500" runat="server">
            <HeaderStyle BackColor="#9a9a9a" ForeColor="White" Font-Bold="true" Height="30" />
            <AlternatingRowStyle BackColor="#f5f5f5" />
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:LinkButton ID="lbtnSelect" runat="server" CommandName="Select" Text="Select" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="SubjectName">
                    <ItemTemplate>
                        <asp:Label ID="lblSubjectName" Text='<%#Eval("SubjectName") %>' runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Marks">
                    <ItemTemplate>
                        <asp:Label ID="lblMarks" Text='<%#Eval("Marks") %>' runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Grade">
                    <ItemTemplate>
                        <asp:Label ID="lblGrade" Text='<%#Eval("Grade") %>' runat="server" />
                    </ItemTemplate>
                </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>

Insert Update Delete Gridview Records 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 CRUD operations like add edit delete gridview data:

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

protected void Page_Load(object sender, EventArgs e) {
    lblMsg.Text = "";
    if (!IsPostBack) {
        BindSubjectData();
    }
}

//call this method to bind gridview
private 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);
            gvSubDetails.DataSource = dt;
            gvSubDetails.DataBind();
            sqlCon.Close();
        }
    }
}

//Insert click event to insert new record to database
protected void btnInsert_Click(object sender, EventArgs e) {
    bool IsAdded = false;
    string SubjectName = txtSubjectName.Text.Trim();
    int Marks = Convert.ToInt32(txtMarks.Text);
    string Grade = txtGrade.Text;
    using (SqlConnection sqlCon = new SqlConnection(conn)) {
        using (SqlCommand cmd = new SqlCommand()) {
            //here added "@" to write continuous string in new line
            cmd.CommandText = @"INSERT INTO SubjectDetails(SubjectName,Marks,Grade)
                                VALUES(@SubjectName,@Marks,@Grade)";
            cmd.Parameters.AddWithValue("@SubjectName", SubjectName);
            cmd.Parameters.AddWithValue("@Marks", Marks);
            cmd.Parameters.AddWithValue("@Grade", Grade);
            cmd.Connection = sqlCon;
            sqlCon.Open();
            IsAdded = cmd.ExecuteNonQuery() > 0;
            sqlCon.Close();
        }
    }
    if (IsAdded) {
        lblMsg.Text = "'" + SubjectName + "' subject details added successfully!";
        lblMsg.ForeColor = System.Drawing.Color.Green;

        BindSubjectData();
    } else {
        lblMsg.Text = "Error while adding '" + SubjectName + "' subject details";
        lblMsg.ForeColor = System.Drawing.Color.Red;
    }
    ResetAll(); //to reset all form controls
}

//Update click event to update existing record from the gridview
protected void btnUpdate_Click(object sender, EventArgs e) {
    if (string.IsNullOrEmpty(txtSubjectId.Text)) {
        lblMsg.Text = "Please select record to update";
        lblMsg.ForeColor = System.Drawing.Color.Red;
        return;
    }
    bool IsUpdated = false;
    int SubjectID = Convert.ToInt32(txtSubjectId.Text);
    string SubjectName = txtSubjectName.Text.Trim();
    int Marks = Convert.ToInt32(txtMarks.Text);
    string Grade = txtGrade.Text;
    using (SqlConnection sqlCon = new SqlConnection(conn)) {
        using (SqlCommand cmd = new SqlCommand()) {
            //here added "@" to write 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);
            cmd.Parameters.AddWithValue("@Marks", Marks);
            cmd.Parameters.AddWithValue("@Grade", Grade);
            cmd.Connection = sqlCon;
            sqlCon.Open();
            IsUpdated = cmd.ExecuteNonQuery() > 0;
            sqlCon.Close();
        }
    }
    if (IsUpdated) {
        lblMsg.Text = "'" + SubjectName + "' subject details updated successfully!";
        lblMsg.ForeColor = System.Drawing.Color.Green;
    } else {
        lblMsg.Text = "Error while updating '" + SubjectName + "' subject details";
        lblMsg.ForeColor = System.Drawing.Color.Red;
    }
    gvSubDetails.EditIndex = -1;
    BindSubjectData();
    ResetAll(); //to reset all form controls
}

//Delete click event to delete selected record from the database
protected void btnDelete_Click(object sender, EventArgs e) {
    if (string.IsNullOrEmpty(txtSubjectId.Text)) {
        lblMsg.Text = "Please select record to delete";
        lblMsg.ForeColor = System.Drawing.Color.Red;
        return;
    }
    bool IsDeleted = false;
    int SubjectID = Convert.ToInt32(txtSubjectId.Text);
    string SubjectName = txtSubjectName.Text.Trim();
    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 + "' subject details deleted successfully!";
        lblMsg.ForeColor = System.Drawing.Color.Green;
        BindSubjectData();
    } else {
        lblMsg.Text = "Error while deleting '" + SubjectName + "' subject details";
        lblMsg.ForeColor = System.Drawing.Color.Red;
    }
    ResetAll(); //to reset all form controls
}

//Cancel click event to clear and reset all the textboxes
protected void btnCancel_Click(object sender, EventArgs e) {
    ResetAll(); //to reset all form controls
}

protected void gvSubDetails_SelectedIndexChanged(object sender, EventArgs e) {
    txtSubjectId.Text = gvSubDetails.DataKeys[gvSubDetails.SelectedRow.RowIndex].Value.ToString();
    txtSubjectName.Text = (gvSubDetails.SelectedRow.FindControl("lblSubjectName") as Label).Text;
    txtMarks.Text = (gvSubDetails.SelectedRow.FindControl("lblMarks") as Label).Text;
    txtGrade.Text = (gvSubDetails.SelectedRow.FindControl("lblGrade") as Label).Text;
    //make invisible Insert button during update/delete
    btnInsert.Visible = false;
}

//call to reset all form controls
private void ResetAll() {
    btnInsert.Visible = true;
    txtSubjectId.Text = "";
    txtSubjectName.Text = "";
    txtMarks.Text = "";
    txtGrade.Text = "";
}

Insert Update Delete Gridview Records 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 CRUD operations like add edit delete gridview data:

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 this method to bind gridview'
Private 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)
            gvSubDetails.DataSource = dt
            gvSubDetails.DataBind()
            sqlCon.Close()
        End Using
    End Using
End Sub

'Insert click event to insert new record to database'
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim IsAdded As Boolean = False
    Dim SubjectName As String = txtSubjectName.Text.Trim()
    Dim Marks As Integer = Convert.ToInt32(txtMarks.Text)
    Dim Grade As String = txtGrade.Text
    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)
            cmd.Parameters.AddWithValue("@Marks", Marks)
            cmd.Parameters.AddWithValue("@Grade", Grade)
            cmd.Connection = sqlCon
            sqlCon.Open()
            IsAdded = cmd.ExecuteNonQuery() > 0
            sqlCon.Close()
        End Using
    End Using
    If IsAdded Then
        lblMsg.Text = "'" & SubjectName & "' subject details added successfully!"
        lblMsg.ForeColor = System.Drawing.Color.Green

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

'Update click event to update existing record from the gridview'
Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs)
    If String.IsNullOrEmpty(txtSubjectId.Text) Then
        lblMsg.Text = "Please select record to update"
        lblMsg.ForeColor = System.Drawing.Color.Red
        Return
    End If
    Dim IsUpdated As Boolean = False
    Dim SubjectID As Integer = Convert.ToInt32(txtSubjectId.Text)
    Dim SubjectName As String = txtSubjectName.Text.Trim()
    Dim Marks As Integer = Convert.ToInt32(txtMarks.Text)
    Dim Grade As String = txtGrade.Text
    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)
            cmd.Parameters.AddWithValue("@Marks", Marks)
            cmd.Parameters.AddWithValue("@Grade", Grade)
            cmd.Connection = sqlCon
            sqlCon.Open()
            IsUpdated = cmd.ExecuteNonQuery() > 0
            sqlCon.Close()
        End Using
    End Using
    If IsUpdated Then
        lblMsg.Text = "'" & SubjectName & "' subject details updated successfully!"
        lblMsg.ForeColor = System.Drawing.Color.Green
    Else
        lblMsg.Text = "Error while updating '" & SubjectName & "' subject details"
        lblMsg.ForeColor = System.Drawing.Color.Red
    End If
    gvSubDetails.EditIndex = -1
    BindSubjectData()
    ResetAll() 'to reset all form controls'
End Sub

'Delete click event to delete selected record from the database'
Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As EventArgs)
    If String.IsNullOrEmpty(txtSubjectId.Text) Then
        lblMsg.Text = "Please select record to delete"
        lblMsg.ForeColor = System.Drawing.Color.Red
        Return
    End If
    Dim IsDeleted As Boolean = False
    Dim SubjectID As Integer = Convert.ToInt32(txtSubjectId.Text)
    Dim SubjectName As String = txtSubjectName.Text.Trim()
    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 & "' subject details deleted successfully!"
        lblMsg.ForeColor = System.Drawing.Color.Green
        BindSubjectData()
    Else
        lblMsg.Text = "Error while deleting '" & SubjectName & "' subject details"
        lblMsg.ForeColor = System.Drawing.Color.Red
    End If
    ResetAll() 'to reset all form controls'
End Sub

'Cancel click event to clear and reset all the textboxes
Protected Sub btnCancel_Click(ByVal sender As Object, ByVal e As EventArgs)
    ResetAll() 'to reset all form controls'
End Sub

Protected Sub gvSubDetails_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    txtSubjectId.Text = gvSubDetails.DataKeys(gvSubDetails.SelectedRow.RowIndex).Value.ToString()
    txtSubjectName.Text =
    (TryCast(gvSubDetails.SelectedRow.FindControl("lblSubjectName"), Label)).Text
    txtMarks.Text = (TryCast(gvSubDetails.SelectedRow.FindControl("lblMarks"), Label)).Text
    txtGrade.Text = (TryCast(gvSubDetails.SelectedRow.FindControl("lblGrade"), Label)).Text
    'make invisible Insert button during update/delete'
    btnInsert.Visible = False
End Sub

'call to reset all form controls'
Private Sub ResetAll()
    btnInsert.Visible = True
    txtSubjectId.Text = ""
    txtSubjectName.Text = ""
    txtMarks.Text = ""
    txtGrade.Text = ""
End Sub

Example Result

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

Download Example Code

Previous articleSQL Injections: What is SQL Injection and How to Prevent it?
Next articleAsp.net GridView Inline Insert Update Delete Data In C# Vb
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.

12 COMMENTS

  1. Hi sir, i tried this but when I try to run the program this line >> conn As String = ConfigurationManager.ConnectionStrings(“conn”).ConnectionString will be highlighted as yellow and it says Object reference not set to an instance of an object.

    what should i do ?

    • Did you added connection string in your web.config file and update your server and database name?

      If still getting same error, try alternate solution and give direct connection string instead of using ConfiurationManager as below:
      Private conn as String = “your connection string here”

      Hope it helps you!

    • Nevermind, I was on the wrong page. Sorry. Anyway, I am have a problem with the C# code above. I am new to C# so forgive me. I get a sqlexception was unhandled by user code and this is the information I got.

      An exception of type ‘System.Data.SqlClient.SqlException’ occurred in System.Data.dll but was not handled in user code

      Additional information: Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.

      This happened in the insert into query:

      the line that says: IsAdded = icomm.ExecuteNonQuery() > 0;

      I used icomm instead of of cmd so you know why I used icomm. Not sure what I did wrong to make the code not work. Can you help?

        • Thanks for the quick reply. Yep, you are right. It was my error when making the database. I made a mistake when I set the data type. I made it varbinary instead of nvarchar and did not realize it but I finally figured it out. Again, thanks for the quick reply and help! ๐Ÿ˜‰

  2. Hey Love the code but i wanted t o ask if i was to add an Image what would i put in place of insert column instead of Text.Trim() or ToInt32(txtMarks.Text) in the case of text and Integer respectively

  3. When the User clicks the Delete button and the confirm pop appears, he/she clicks NO, how is that interpreted and stop the delete from occurring?

    • We’re preventing using OnClientClick attribute which takes input Ok/Cancel from user. Which is manage by JavaScript’s confirm function. For example, OnClientClick=”return confirm(‘Are you sure you want to delete this record?’)”.

      Check the delete button markup for more details.

  4. This is a great example and very well written. Is the project winforms, web site, mvc, core, or entity framework. It runs on my pc on ieexpress. What steps are required to publish this to a web server.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

four × 4 =