Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

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


<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
.Gridview
{
font-family:Verdana;
font-size:10pt;
font-weight:normal;
color:black;
}
</style>
</head>
<body>
<form id="form2" runat="server">
<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">
<Columns>
<asp:TemplateField>
<EditItemTemplate>
<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>
<ItemTemplate>
<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>
<FooterTemplate>
<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">
<EditItemTemplate>
<asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("UserName") %>'/>
</ItemTemplate>
<FooterTemplate>
<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">
<EditItemTemplate>
<asp:TextBox ID="txtfname" runat="server" Text='<%#Eval("FirstName") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblfname" runat="server" Text='<%#Eval("FirstName") %>'/>
</ItemTemplate>
<FooterTemplate>
<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">
<EditItemTemplate>
<asp:TextBox ID="txtlname" runat="server" Text='<%#Eval("LastName") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lbllname" runat="server" Text='<%#Eval("LastName") %>'/>
</ItemTemplate>
<FooterTemplate>
<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">
<EditItemTemplate>
<asp:TextBox ID="txtcity" runat="server" Text='<%#Eval("City") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblcity" runat="server" Text='<%#Eval("City") %>'/>
</ItemTemplate>
<FooterTemplate>
<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">
<EditItemTemplate>
<asp:TextBox ID="txtDesg" runat="server" Text='<%#Eval("Designation") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblDesg" runat="server" Text='<%#Eval("Designation") %>'/>
</ItemTemplate>
<FooterTemplate>
<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">
<UpdateParameters>
<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>
<InsertParameters>
<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>
<add name="dbconnection" connectionString="Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"/>
</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 RSS subscribe by email Subscribe by Email

40 comments :

Anonymous said...

good..

Anonymous said...

great post :)

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

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

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

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.

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?

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

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

Suresh Dasari said...

@Anil Babu...
Check this link http://www.aspdotnet-suresh.com/2011/02/how-to-send-gridview-row-values-to.html

Unknown said...
This comment has been removed by the author.
Unknown said...

Please code for hotel management system using 3 tier architecture in C#.net !!

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.

dinesh harwani said...

suresh ji i want to insert record in database with useing gridview with useing unbound data

Anonymous said...

pls publish the vb.net form of this code

Anonymous said...

for me update query was executed successfully but values are not updated in the database... please help to me...

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

Vyasaraj Sudharsan said...

Suresh sir can you please tell how to set identity property true in sql table creation.

Suresh Dasari said...

@vyasaraj Sundharsan...
i already written post for this please check with searchbox available in top of website...

Anonymous said...

How to insert,delete, edit and update data in gridview using single event and multiple commands and without using the sqldatasource control?

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..........

vishal.patel said...

please reply.....dear.........

vishal.patel said...

above your code .i want to add id column to generte auto no inside column how it possible...

Anonymous said...

pls send me the code for insert from two different tables in c#

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

Unknown said...

guys pls help me soon...

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.

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.

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

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

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.........

**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

Anonymous said...

hai suresh

your code was ver use full

Anu KK

Anonymous said...

hai suresh,

how can I create forum like website in asp.net
Anu KK

Anonymous said...

it is not showing the Eidit and Update

parthu said...

i did the same but it did not work for me

Anonymous said...

how to give a name particular cell in Gridview

Unknown said...
This comment has been removed by the author.
Unknown said...

great sir....

Unknown said...

hlo sir
can u explain what is sqlds i got a red mark under these line

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.