Pages

3 Aug 2013

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

Design View

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



Code View

 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 >

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)