how insert, Edit, Update and delete data in gridview with sqldatasource using asp.net

By: Suresh Dasari Feb 13, 2011

Introduction:

In this article I will explain how to insert, edit, update and delete data in gridview with Sqldatasource using asp.net.

Description:

In my previous article I explain clearly how to insert, edit, update and delete data in gridview using asp.net. Now you may think that why he has written the same code with Sqldatasource main reason is if we Sqldatasource we have chance to reduce lot of code in code behind check two posts how much of code in reduced in this post. Now I will explain how to do the insert, edit, update and delete functionality with Sqldatasource here I used some of commands to achieve that functionality those are

1) 1) SelectCommand

2) 2) InsertCommand

3) 3) UpdateCommand

4) 4) DeleteCommand

By Using above Sqldatasource commands we can insert, edit, update and delete the data in gridview Our Question is how we can use these commands in our coding before to see those details first design the table in database and give name Employee_Details

ColumnName	DataType
UserId	Int(set identity property=true)
UserName	varchar(50)
FirstName	varchar(50)
LastName	varchar(50)
City	varchar(50)
Designation	varchar(50)

After completion table creation enter some dummy data because our gridview won’t display gridview headers if it contains null value at that time we won’t see footer textboxes to enter new record if you want to show the gridview header even if griview contains empty data check this post how to show gridview header even if it contains empty data after that design your aspx page like this

<title>Untitled Page</title>

.Gridview

{

font-family:Verdana;

font-size:10pt;

font-weight:normal;

color:black;

}

</style>

</head>

<body>

<div>

<asp:GridView ID="gvDetails" DataKeyNames="UserId" runat="server"

AutoGenerateColumns="false" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"

ShowFooter="true" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White"

DataSourceID="sqlds" onrowcommand="gvDetails_RowCommand">

<asp:TemplateField>

<asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ImageUrl="~/Images/update.jpg" ToolTip="Update" Height="20px" Width="20px" />

<asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" Height="20px" Width="20px" />

</EditItemTemplate>

<asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" ToolTip="Edit" Height="20px" Width="20px" />

<asp:ImageButton ID="imgbtnDelete" CommandName="Delete" Text="Edit" runat="server" ImageUrl="~/Images/delete.jpg" ToolTip="Delete" Height="20px" Width="20px" />

</ItemTemplate>

<asp:ImageButton ID="imgbtnAdd" runat="server" ImageUrl="~/Images/AddNewitem.jpg" CommandName="Insert" Width="30px" Height="30px" ToolTip="Add new User" ValidationGroup="validaiton" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="UserName" HeaderStyle-HorizontalAlign="Left">

<asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>'/>

</EditItemTemplate>

<asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("UserName") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrusrname" runat="server"/>

<asp:RequiredFieldValidator ID="rfvusername" runat="server" ControlToValidate="txtftrusrname" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="FirstName" HeaderStyle-HorizontalAlign="Left">

<asp:TextBox ID="txtfname" runat="server" Text='<%#Eval("FirstName") %>'/>

</EditItemTemplate>

<asp:Label ID="lblfname" runat="server" Text='<%#Eval("FirstName") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrfname" runat="server"/>

<asp:RequiredFieldValidator ID="rfvfname" runat="server" ControlToValidate="txtftrfname" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="LastName" HeaderStyle-HorizontalAlign="Left">

<asp:TextBox ID="txtlname" runat="server" Text='<%#Eval("LastName") %>'/>

</EditItemTemplate>

<asp:Label ID="lbllname" runat="server" Text='<%#Eval("LastName") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrlname" runat="server"/>

<asp:RequiredFieldValidator ID="rfvlname" runat="server" ControlToValidate="txtftrlname" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="City" HeaderStyle-HorizontalAlign="Left">

<asp:TextBox ID="txtcity" runat="server" Text='<%#Eval("City") %>'/>

</EditItemTemplate>

<asp:Label ID="lblcity" runat="server" Text='<%#Eval("City") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrcity" runat="server"/>

<asp:RequiredFieldValidator ID="rfvcity" runat="server" ControlToValidate="txtftrcity" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Designation" HeaderStyle-HorizontalAlign="Left">

<asp:TextBox ID="txtDesg" runat="server" Text='<%#Eval("Designation") %>'/>

</EditItemTemplate>

<asp:Label ID="lblDesg" runat="server" Text='<%#Eval("Designation") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrDesignation" runat="server"/>

<asp:RequiredFieldValidator ID="rfvdesignation" runat="server" ControlToValidate="txtftrDesignation" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="sqlds" runat="server" ConnectionString="<%$ ConnectionStrings:dbconnection %>"

SelectCommand="Select * from Employee_Details"

InsertCommand="insert into Employee_Details(UserName,FirstName,LastName,City,Designation) values(@UserName,@FirstName,@LastName,@City,@Designation)"

DeleteCommand="delete from Employee_Details where UserId=@UserId"

UpdateCommand="update Employee_Details set FirstName=@FirstName,LastName=@LastName, City=@City,Designation=@Designation where UserId=@UserId">

<asp:Parameter Name="UserId" Type= "Int32" />

<asp:Parameter Name="FirstName" Type="String" />

<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="City" Type="String" />

<asp:Parameter Name="Designation" Type="String" />

</UpdateParameters>

<asp:Parameter Name="UserName" Type="String" />

<asp:Parameter Name="FirstName" Type="String" />

<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="City" Type="String" />

<asp:Parameter Name="Designation" Type="String" />

</InsertParameters>

</asp:SqlDataSource>

</div>

<div>

<asp:Label ID="lblresult" runat="server"></asp:Label>

</div>

</form>

</body>

</html>

After that add namcespace using System.Drawing; in your codebehind and write the following code

protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)

{

if(e.CommandName=="Insert")

{

TextBox txtusername = (TextBox)gvDetails.FooterRow.FindControl("txtftrusrname");

TextBox txtfirstname = (TextBox)gvDetails.FooterRow.FindControl("txtftrfname");

TextBox txtlastname = (TextBox)gvDetails.FooterRow.FindControl("txtftrlname");

TextBox txtCity = (TextBox)gvDetails.FooterRow.FindControl("txtftrcity");

TextBox txtDesgnation = (TextBox)gvDetails.FooterRow.FindControl("txtftrDesignation");

sqlds.InsertParameters["UserName"].DefaultValue = txtusername.Text;

sqlds.InsertParameters["FirstName"].DefaultValue = txtfirstname.Text;

sqlds.InsertParameters["LastName"].DefaultValue = txtlastname.Text;

sqlds.InsertParameters["City"].DefaultValue = txtCity.Text;

sqlds.InsertParameters["Designation"].DefaultValue = txtDesgnation.Text;

sqlds.Insert();

lblresult.Text = txtusername.Text + " Details Inserted Successfully";

lblresult.ForeColor = Color.Green;

}

if (e.CommandName == "Update")

{

GridViewRow gvrow = (GridViewRow)((ImageButton)e.CommandSource).NamingContainer;

Label lblusername = (Label)gvrow.FindControl("lbleditusr");

TextBox txtfirstname = (TextBox)gvrow.FindControl("txtfname");

TextBox txtlastname = (TextBox)gvrow.FindControl("txtlname");

TextBox txtCity = (TextBox)gvrow.FindControl("txtcity");

TextBox txtDesgnation = (TextBox)gvrow.FindControl("txtDesg");

sqlds.UpdateParameters ["FirstName"].DefaultValue = txtfirstname.Text;

sqlds.UpdateParameters["LastName"].DefaultValue = txtlastname.Text;

sqlds.UpdateParameters["City"].DefaultValue = txtCity.Text;

sqlds.UpdateParameters["Designation"].DefaultValue = txtDesgnation.Text;

sqlds.Update();

lblresult.Text = lblusername.Text + " Details Updated Successfully";

lblresult.ForeColor = Color.Green;

}

if(e.CommandName=="Delete")

{

GridViewRow gvdeleterow = (GridViewRow) ((ImageButton) e.CommandSource).NamingContainer;

Label lblusername = (Label)gvdeleterow.FindControl("lblitemUsr");

lblresult.Text = lblusername.Text + " Details Updated Successfully";

lblresult.ForeColor = Color.Red;

}

After that set your database connection in web.config like this

</connectionStrings >

Demo

Download sample code attached

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS

Subscribe by Email

40 comments :

Anonymous said...: good..; October 19, 2011 at 4:23 AM
Anonymous said...: great post :); November 18, 2011 at 3:09 AM
Mehtab Ali said...: Question: How to show Header Text if There is no record found in the table..As you said above please go through this link http://www.aspdotnet-suresh.com/2010/12/v-behaviorurldefaultvmlo.html

but in this link you have coded in the.cs page to check there is record in the table or not..please let me know how to do it from the .aspx page code as you have done in this post

http://www.aspdotnet-suresh.com/2011/02/normal-0-false-false-false-en-us-x-none.html; January 4, 2012 at 3:40 AM
Mehtab Ali said...: Suresh I have done it myself..please go through like this
steps
1)create a label as

2) create event of Sqldatasource as Selected

protected void sqlds_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows < 1)
Label1.Text = "No record Found";
}
3) Now it is done; January 4, 2012 at 4:40 AM
Mehtab Ali said...: Suresh I have done it myself..please go through like this
steps
1)create a label as:

2) create event of Sqldatasource as Selected

protected void sqlds_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows < 1)
Label1.Text = "No record Found";
}
3) Now it is done; January 4, 2012 at 4:43 AM
SID said...: suresh can you tell me how to insert update and delete data from an existing excel file using asp.net gridview.
please reply back ASAP.
Thanks in advance.; March 15, 2012 at 11:23 AM
Digital Kiran said...: Hi sir thanks a lot for the code you have provided, it helped me a lot.Is it possible for you to write the same code using a three Layer Architecture?; June 18, 2012 at 2:59 AM
Suresh Dasari said...: @kiran sri...
For 3tier architecture check this post
http://www.aspdotnet-suresh.com/2010/05/introduction-to-3-tier-architecture-in_17.html; June 18, 2012 at 3:42 AM
Help said...: Thank you,
I want to insert data in grid view ,
In gridview Place insert button I want to click that one display insert page.aspx after enterthe data click save button the data display in grid view
plz give me that type of examples; August 22, 2012 at 11:39 PM
Suresh Dasari said...: @Anil Babu...
Check this link http://www.aspdotnet-suresh.com/2011/02/how-to-send-gridview-row-values-to.html; August 22, 2012 at 11:45 PM
Unknown said...: This comment has been removed by the author.; September 6, 2012 at 12:20 PM
Unknown said...: Please code for hotel management system using 3 tier architecture in C#.net !!; September 6, 2012 at 12:25 PM
Anonymous said...: Hi suresh, the code you've placed above is excellent. But i've a small problem in that code. i.e., I want to show gridview header even if it contains empty data as "NO RECORDS FOUND" & i want to display ADD Button. So plz can you tell me how to solve the problem.
Thanks in advance.; September 25, 2012 at 5:42 AM
dinesh harwani said...: suresh ji i want to insert record in database with useing gridview with useing unbound data; October 7, 2012 at 4:32 AM
Anonymous said...: pls publish the vb.net form of this code; October 9, 2012 at 3:08 AM
Anonymous said...: for me update query was executed successfully but values are not updated in the database... please help to me...; October 15, 2012 at 7:14 AM
Unknown said...: Hi Suresh this is Ayub i staded u r articla it is very good it would helpful to beginars keepit up

Thank you
Ayub; December 11, 2012 at 7:41 PM
Vyasaraj Sudharsan said...: Suresh sir can you please tell how to set identity property true in sql table creation.; February 4, 2013 at 4:18 PM
Suresh Dasari said...: @vyasaraj Sundharsan...
i already written post for this please check with searchbox available in top of website...; February 4, 2013 at 6:15 PM
Anonymous said...: How to insert,delete, edit and update data in gridview using single event and multiple commands and without using the sqldatasource control?; March 13, 2013 at 8:57 PM
vishal.patel said...: i want to create your gried view add one id colunm to auto generate code so ur code how it possible..

if(e.CommandName=="Insert")
{

Label txtftrid = (Label)gvdetail.FooterRow.FindControl("txtftrno");

Object reference not set to an instance of an object.above the error..........; March 28, 2013 at 5:28 AM
vishal.patel said...: please reply.....dear.........; March 29, 2013 at 10:01 PM
vishal.patel said...: above your code .i want to add id column to generte auto no inside column how it possible...; March 29, 2013 at 10:49 PM
Anonymous said...: pls send me the code for insert from two different tables in c#; July 22, 2013 at 5:53 AM
Unknown said...: i want to create a grid view by getting the data from textbox....without database connection
and in grid view i want to update,edit,cancel; August 2, 2013 at 4:16 AM
Unknown said...: guys pls help me soon...; August 2, 2013 at 4:17 AM
Iqbal said...: I have downloaded the code and created the same database... but getting the below errors...

Exception Details: System.InvalidOperationException: Instance failure.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.; August 14, 2013 at 1:09 PM
Iqbal said...: @Mehtab Ali

Can you please explain how you have created event of Sqldatasource as Selected and where you have created the event. I need to show the header and insert option even there is no data in the database....

Please help.; August 15, 2013 at 2:20 AM
Peal said...: Suresh Sir,

I tried to to integrate your http://www.aspdotnet-suresh.com/2010/12/v-behaviorurldefaultvmlo.html this code with
http://www.aspdotnet-suresh.com/2011/02/normal-0-false-false-false-en-us-x-none.html this one.
As I need to have the header and insert option even though there is no records....However didn't succeed.

Please help Suresh Sir. I am new in Asp. Net; August 15, 2013 at 4:43 AM
Anonymous said...: hi sir .. i created a grid view . in this grid we can add data ,but in this grid i cannot edit delete data.. i gave option to edit delete .. but these two options are not working..my gmail id sanjaysemail11@gmail.com; August 30, 2013 at 4:58 AM
Unknown said...: Am very new to asp.net can you please tell me how can i start from beggining any article is der wher i can learn step by step.........; September 23, 2013 at 2:09 AM
**Sanat Shukla** said...: Sir ji,
I need your help.
suresh sir, my Question is.....
"JB HUM EDIT BUTTON PR CLICK KAREN TO USH ROW KA SARA DATA GRIDVIEW KI JAGAH HUMARI
SAVE KARNE WALI TABLE ME FILL HO JAYE AUR HUM VAHAAN SE USKO MODIFY KR SAKEN"

SANAT; February 18, 2014 at 10:06 PM
Anonymous said...: hai suresh

your code was ver use full

Anu KK; May 17, 2014 at 4:37 AM
Anonymous said...: hai suresh,

how can I create forum like website in asp.net
Anu KK; May 17, 2014 at 4:39 AM
Anonymous said...: it is not showing the Eidit and Update; July 16, 2014 at 12:09 AM
parthu said...: i did the same but it did not work for me; January 8, 2015 at 3:32 AM
Anonymous said...: how to give a name particular cell in Gridview; January 19, 2015 at 5:39 AM
Unknown said...: This comment has been removed by the author.; January 22, 2017 at 12:28 AM
Unknown said...: great sir....; January 22, 2017 at 12:29 AM
Unknown said...: hlo sir
can u explain what is sqlds i got a red mark under these line; December 3, 2018 at 10:56 PM