Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 1418
Home > Forums > ADO.NET >

how to do delete and update operations with sqldatasouce

Posted by Kishore22 under ADO.NET on 9/19/2013 | Points: 10 | Views : 2302 | Status : [Member] | Replies : 3
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
how to do delete and update operations with sqldatasouce



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Bandi on: 9/19/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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) 


<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,@FirstNam

Mark This Response as Answer

--

Chandu

http://www.dotnetfunda.com/images/dnfmvp.gif
Kishore22, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 9/19/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Continue from Posted on: 9/19/2013 9:18:45 AM .......
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=ServerName;Integrated Security=true;Initial Catalog=MySampleDB"/>
</connectionStrings >


Reference:
http://www.asp.net/web-forms/tutorials/data-access/accessing-the-database-directly-from-an-aspnet-page/inserting-updating-and-deleting-data-with-the-sqldatasource-cs

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kishore22, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark it as answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kishore22, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts