ASP.net - how to update and delete value in sql database

Question

how to update and delete value in sql database using C#?

Satyapriya Nayak · Answer

Untitled Page DataKeyNames=VendorId AllowPaging=True CellPadding=3 OnPageIndexChanging=ResultGridView_PageIndexChanging OnRowDeleting=ResultGridView_RowDeleting OnRowEditing=ResultGridView_RowEditing OnRowUpdating=ResultGridView_RowUpdating OnRowCancelingEdit=ResultGridView_RowCancelingEdit PageSize=5 BackColor=#DEBA84 BorderColor=#DEBA84 BorderStyle=None BorderWidth=1px CellSpacing=2 OnRowCommand=ResultGridView_RowCommand AllowSorting=true onsorting=ResultGridView_Sorting> ReadOnly=True SortExpression=VendorId /> '> '> '> '> '> '> '> '> '> '> '> '> onclick=btn_search_Click /> 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 VendorLNameVendorCityVendorStateVendorCountryVendorDescription) 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(); } }