how to insert ,update,edit,delete the data in grid view

Posted by Kishore22 under ASP.NET on 9/18/2013 | Points: 10 | Views : 14467 | Status : [Member] | Replies : 5
how to insert ,update,edit,delete the data in grid view in aspnet




Responses

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

Up
0
Down
DB structure:
ColumnName  DataType 

UserId int(set identity property=true)
UserName varchar(50)
City varchar(50)
Designation varchar(50)


//design aspx page
<html xmlns="http://www.w3.org/1999/xhtml" >

<head 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="form1" runat="server">
<div>
<asp:GridView ID="gvDetails" DataKeyNames="UserId,UserName" runat="server"
AutoGenerateColumns="false" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"
ShowFooter="true" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White"
onrowcancelingedit="gvDetails_RowCancelingEdit"
onrowdeleting="gvDetails_RowDeleting" onrowediting="gvDetails_RowEditing"
onrowupdating="gvDetails_RowUpdating"
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="AddNew" Width="30px" Height="30px" ToolTip="Add new User" ValidationGroup="validaiton" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="UserName">
<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="City">
<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">
<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>
</div>
<div>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>


Now add the following namespaces in codebehind
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing

For continuation....
Refer reply Posted on: 9/18/2013 6:39:55 AM

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: Allemahesh on: 9/18/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Kishore,

You can see the below links:-
http://www.aspdotnet-suresh.com/2011/02/how-to-inserteditupdate-and-delete-data.html
http://www.codeproject.com/Articles/417693/Insert-Update-Delete-in-ASP-NET-Gridview-DataSourc
http://www.dotnetgallery.com/kb/resource10-How-to-perform-insert-update-delete-and-select-rows-in-ASPNET-gridview-c.aspx
http://www.c-sharpcorner.com/uploadfile/raj1979/select-add-update-and-delete-data-in-a-Asp-Net-gridview-control/
http://www.webcodeexpert.com/2013/07/how-to-bindinserteditupdatedelete-in.html#.Ujl5_sZkNy0

Happy Coding

If it helps you or directs U towards the solution, MARK IT AS ANSWER

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

Posted by: Allemahesh on: 9/18/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Kishore,

GridView HTML

<asp:GridView ID="gvEG" runat="server" AutoGenerateColumns="False" CssClass="grid"

AlternatingRowStyle-CssClass="gridAltRow" RowStyle-CssClass="gridRow" ShowFooter="True"
EditRowStyle-CssClass="gridEditRow" FooterStyle-CssClass="gridFooterRow"
OnRowCancelingEdit="gvEG_RowCancelingEdit"
OnRowCommand="gvEG_RowCommand" OnRowDataBound="gvEG_RowDataBound" OnRowDeleting="gvEG_RowDeleting"
OnRowEditing="gvEG_RowEditing" OnRowUpdating="gvEG_RowUpdating" DataKeyNames="ID,DepartmentId">
<Columns>
<asp:TemplateField HeaderText="Employee Code" HeaderStyle-HorizontalAlign="Left"
ControlStyle-Width="50px">
<EditItemTemplate>
<asp:TextBox ID="txtEmployeeCode" runat="server" Text='<%# Bind("EmployeeCode") %>'
MaxLength="6" Width="50px"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEmployeeCode" ValidationGroup="Update" runat="server"
ControlToValidate="txtEmployeeCode" ErrorMessage="Please Enter Employee Code"
ToolTip="Please Enter Employee Code" SetFocusOnError="true"
ForeColor="Red">*</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="reEmployeeCode" runat="server"
ControlToValidate="txtEmployeeCode"
ErrorMessage="Please Enter Only Numbers in Employee Code"
ToolTip="Please Enter Only Numbers"
SetFocusOnError="true" ForeColor="Red" ValidationExpression="^\d+$"
ValidationGroup="Update">*</asp:RegularExpressionValidator>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtEmployeeCode" runat="server" MaxLength="6" Width="50px"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEmployeeCode" ValidationGroup="Insert" runat="server"
ControlToValidate="txtEmployeeCode" ErrorMessage="Please Enter Employee Code"
ToolTip="Please Enter Employee Code" SetFocusOnError="true"
ForeColor="Red">*</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="reEmployeeCode" runat="server"
ControlToValidate="txtEmployeeCode"
ErrorMessage="Please Enter Only Numbers in Employee Code"
ToolTip="Please Enter Only Numbers"
SetFocusOnError="true" ForeColor="Red" ValidationExpression="^\d+$"
ValidationGroup="Insert">*</asp:RegularExpressionValidator>
</FooterTemplate>
<ItemTemplate>
<%# Eval("EmployeeCode")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name" HeaderStyle-HorizontalAlign="Left" ControlStyle-Width="90px">
<EditItemTemplate>
<asp:TextBox ID="txtEmployeeName" runat="server" Text='<%# Bind("EmployeeName") %>'
Width="90px"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEmployeeName" ValidationGroup="Update" runat="server"
ControlToValidate="txtEmployeeName" ErrorMessage="Please Enter Name"
ToolTip="Please Enter Name"
SetFocusOnError="true" ForeColor="Red">*</asp:RequiredFieldValidator>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtEmployeeName" runat="server" Width="90px"></asp:TextBox>
<asp:RequiredFieldValidator ID="rfvEmployeeName" ValidationGroup="Insert" runat="server"
ControlToValidate="txtEmployeeName" ErrorMessage="Please Enter Name"
ToolTip="Please Enter Name"
SetFocusOnError="true" ForeColor="Red">*</asp:RequiredFieldValidator>
</FooterTemplate>
<ItemTemplate>
<%# Eval("EmployeeName") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Department" HeaderStyle-HorizontalAlign="Left">
<EditItemTemplate>
<asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id">
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<%# Eval("DepartmentName")%>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlDepartment" runat="server" DataTextField="Name" DataValueField="Id">
</asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Group" HeaderStyle-HorizontalAlign="Left">
<EditItemTemplate>
<asp:DropDownList ID="ddlEmployeeGroup" runat="server"
SelectedValue='<%# Eval("EmployeeGroup") %>'>
<asp:ListItem Text="User" Value="User"></asp:ListItem>
<asp:ListItem Text="Admin" Value="Admin"></asp:ListItem>
<asp:ListItem Text="Super User" Value="Super User"></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<%# Eval("EmployeeGroup")%>
</ItemTemplate>

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

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

Up
0
Down
In my previous post some of the code is erased (by default..)
Check the continuous code for the above post Posted on: 9/18/2013 5:59:24 AM
SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB");

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployeeDetails();
}
}
protected void BindEmployeeDetails()
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from Employee_Details", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No Records Found";
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindEmployeeDetails();
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
TextBox txtcity = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtcity");
TextBox txtDesignation = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtDesg");
con.Open();
SqlCommand cmd = new SqlCommand("update Employee_Details set City='" + txtcity.Text + "',Designation='" + txtDesignation.Text + "' where UserId=" + userid, con);
cmd.ExecuteNonQuery();
con.Close();
lblresult.ForeColor = Color.Green;
lblresult.Text = username + " Details Updated successfully";
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["UserId"].ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();
con.Open();
SqlCommand cmd = new SqlCommand("delete from Employee_Details where UserId=" + userid, con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Red;
lblresult.Text = username + " details deleted successfully";
}
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if(e.CommandName.Equals("AddNew"))
{
TextBox txtUsrname = (TextBox)gvDetails.FooterRow.FindControl("txtftrusrname");
TextBox txtCity = (TextBox)gvDetails.FooterRow.FindControl("txtftrcity");
TextBox txtDesgnation = (TextBox) gvDetails.FooterRow.FindControl("txtftrDesignation");
con.Open();
SqlCommand cmd =
new SqlCommand(
"insert into Employee_Details(UserName,City,Designation) values('" + txtUsrname.Text + "','" +
txtCity.Text + "','" + txtDesgnation.Text + "')", con);
int result= cmd.ExecuteNonQuery();
con.Close();
if(result==1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtUsrname.Text + " Details inserted successfully";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = txtUsrname.Text + " Details not inserted";
}
}
}


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/18/2013 [Member] [MVP] Platinum | Points: 25

Login to post response