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:
(
[SubjectId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SubjectName] [nvarchar](100) NULL,
[Marks] [int] NULL,
[Grade] [nvarchar](50) NULL
)
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:
<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):
<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">
</add>
</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.Configuration;
using System.Data.SqlClient;
Now check the below sample code snippet to perform CRUD operations like add edit delete gridview data:
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 strind 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 strind in new line
cmd.CommandText = @"UPDATE SubjectDetails SET [email protected],
[email protected],[email protected] WHERE [email protected]";
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 [email protected]";
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:
Now check the below sample code snippet to perform CRUD operations like add edit delete gridview data:
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 [email protected]," &
"[email protected],[email protected] WHERE [email protected]"
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 [email protected]"
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
Download Example Code
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
Code works well but i cannot insert and update from textboxes. the textboxes are freezed
It might be because the Insert method is calling two times. Have you checked with debugging your code?
If you can’t able to figure it out, show me your code.
The code in the download is different than what you have listed about . It just confuses me some.
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?
This error states that you are doing wrong conversion in your insert query. Show me your database table schema and final query that you are trying to execute.
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! 😉
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!