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
 |
Thank you for visiting this site. Please send me your feed back
No comments:
Post a Comment