how to insert update and delete in asp.net

Posted by Shanky11 under ASP.NET on 12/17/2012 | Points: 10 | Views : 1843 | Status : [Member] | Replies : 10
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
Width="491px" PageSize="3" EnableViewState="True" ShowFooter="true" ShowHeader="true"
HeaderStyle-BackColor="Aqua" Height="279px"
onselectedindexchanged="GridView1_SelectedIndexChanged"
onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating"
onrowcancelingedit="GridView1_RowCancelingEdit">
<Columns>
<asp:TemplateField HeaderText="Name" HeaderStyle-HorizontalAlign="Left">
<ItemTemplate>
<%# Eval("Name")%>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtUserName" runat="Server" Text='<%# Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtnewUserName" runat="Server"></asp:TextBox>
</FooterTemplate>

<HeaderStyle HorizontalAlign="Left"></HeaderStyle>
</asp:TemplateField>

<asp:TemplateField HeaderText="Age">
<ItemTemplate>
<%#Eval("Age")%>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtUserage" runat="Server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Address">
<ItemTemplate>
<%#Eval("adress")%>

</ItemTemplate>
<FooterTemplate>
<asp:Button ID="Button1" runat="server" Text="Insert" CommandName="add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="true"
ShowHeader="true" />
<asp:CommandField HeaderText="Edit" ShowEditButton ="true" />
<asp:CommandField HeaderText="Select" ShowSelectButton="true" />

</Columns>
<EmptyDataTemplate>
<asp:Button ID="btnInsert" runat="Server" Text="Insert" CommandName="EmptyInsert" UseSubmitBehavior="False" />
</EmptyDataTemplate>
</asp:GridView>




Responses

Posted by: Ranjeet_8 on: 12/17/2012 [Member] [MVP] Gold | Points: 25
Posted by: Ranjeet_8 on: 12/17/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
@Shanky11
Please Mark As Answer if post helps you.

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

Posted by: Ankitsrist on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
u can use this for editing....and in next post i will show u how to update
 protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)

{
gvDetails.EditIndex = e.NewEditIndex;
BindEmployeeDetails();
}



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

Posted by: Ankitsrist on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
for updating......try this
 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();
}


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

Posted by: Ankitsrist on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
for deleting.......try this
 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";
}
}


mark this if it helps u

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

Posted by: Ankitsrist on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
and for canceling u can try following code
 protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}


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

Posted by: Ankitsrist on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
and for canceling u can try following code
 protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}


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

Posted by: Ankitsrist on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
mark this if it helps u

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

Posted by: Ankitsrist on: 12/18/2012 [Member] Starter | Points: 25

Up
0
Down
mark this if it helps u

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

Posted by: Satyapriyanayak on: 1/23/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter ad = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dataTable;
SqlDataAdapter sqlda;
DataSet ds;
string str;
protected void Page_Load(object sender, EventArgs e)
{
Session["sortBy"] = null;
if (!IsPostBack)
{
FillVendorGrid();
}
}
private void FillVendorGrid()
{
SqlConnection conn = new SqlConnection(connStr);
dataTable = new DataTable();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM Vendor";
ad = new SqlDataAdapter(cmd);
ad.Fill(dataTable);
ResultGridView.DataSource = dataTable;
ResultGridView.DataBind();

}

protected void ResultGridView_RowEditing(object sender, GridViewEditEventArgs e)
{
ResultGridView.EditIndex = e.NewEditIndex;
FillVendorGrid();
}


protected void ResultGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
ResultGridView.PageIndex = e.NewPageIndex;
FillVendorGrid();
}

protected void ResultGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection conn = new SqlConnection(connStr);
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM Vendor WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
FillVendorGrid();

}

protected void ResultGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtFName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtFName");
TextBox txtLName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtLName");
TextBox txtCity = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCity");
TextBox txtState = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtState");
TextBox txtCountry = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCountry");
TextBox txtDescription = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtDescription");

SqlConnection conn = new SqlConnection(connStr);
cmd.Connection = conn;
cmd.CommandText = "UPDATE Vendor SET VendorFName ='" + txtFName.Text + "',VendorLName ='" + txtLName.Text + "',VendorCity ='" + txtCity.Text + "',VendorState ='" + txtState.Text + "',VendorCountry ='" + txtCountry.Text + "',VendorDescription ='" + txtDescription.Text + "' WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
conn.Open();
cmd.ExecuteNonQuery();
ResultGridView.EditIndex = -1;
FillVendorGrid();
conn.Close();

}

protected void ResultGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
ResultGridView.EditIndex = -1;
FillVendorGrid();

}

protected void ResultGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{

TextBox txtFName = (TextBox)ResultGridView.FooterRow.FindControl("txtFName1");
TextBox txtLName = (TextBox)ResultGridView.FooterRow.FindControl("txtLName1");
TextBox txtCity = (TextBox)ResultGridView.FooterRow.FindControl("txtCity1");
TextBox txtState = (TextBox)ResultGridView.FooterRow.FindControl("txtState1");
TextBox txtCountry = (TextBox)ResultGridView.FooterRow.FindControl("txtCountry1");
TextBox txtDescription = (TextBox)ResultGridView.FooterRow.FindControl("txtDescription1");
SqlConnection conn = new SqlConnection(connStr);
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Vendor(VendorFName, VendorLName,VendorCity,VendorState,VendorCountry,VendorDescription) Values('" + txtFName.Text + "', '" + txtLName.Text + "', '" + txtCity.Text + "', '" + txtState.Text + "', '" + txtCountry.Text + "' , '" + txtDescription.Text + "')";
conn.Open();
cmd.ExecuteNonQuery();
FillVendorGrid();
conn.Close();
}
}
protected void btn_search_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
str = "select * from Vendor where VendorFName like '" + TextBox1.Text + "%'";
cmd = new SqlCommand(str, conn);
sqlda = new SqlDataAdapter(cmd);
ds = new DataSet();
sqlda.Fill(ds, "Vendor");
conn.Close();

ResultGridView.DataSource = ds;
ResultGridView.DataMember = "Vendor";
ResultGridView.DataBind();

}
protected void ResultGridView_Sorting(object sender, GridViewSortEventArgs e)
{
Session["sortBy"] = e.SortExpression;
FillVendorGrid();
}
}

If this post helps you mark it as answer
Thanks

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

Login to post response