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
Download Example Code
You may also like, 100+ Frequently Asked Interview Questions on Asp.net, Top 10 OOPS Concepts in .NET C# with Examples here.
hi..how to visible gridview on load if no record?
Hi Saidi,
Thanks for the great point. I have updated my post that will resolve your problem. You only need to change BindSubjectData() method to resolve your issue.
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
Good Job man..nice article