Introduction:
In this article I will explain how to insert, edit, update and delete data in gridview with Sqldatasource using asp.net.
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
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 Email
|
|||
|
|
|
24 comments :
good..
excellent
great post :)
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
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
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
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.
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?
@kiran sri...
For 3tier architecture check this post
http://www.aspdotnet-suresh.com/2010/05/introduction-to-3-tier-architecture-in_17.html
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
@Anil Babu...
Check this link http://www.aspdotnet-suresh.com/2011/02/how-to-send-gridview-row-values-to.html
Please code for hotel management system using 3 tier architecture in C#.net !!
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.
suresh ji i want to insert record in database with useing gridview with useing unbound data
pls publish the vb.net form of this code
for me update query was executed successfully but values are not updated in the database... please help to me...
Hi Suresh this is Ayub i staded u r articla it is very good it would helpful to beginars keepit up
Thank you
Ayub
Suresh sir can you please tell how to set identity property true in sql table creation.
@vyasaraj Sundharsan...
i already written post for this please check with searchbox available in top of website...
How to insert,delete, edit and update data in gridview using single event and multiple commands and without using the sqldatasource control?
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..........
please reply.....dear.........
above your code .i want to add id column to generte auto no inside column how it possible...