using gridview to update/delete .

Question

hey  How do I update or delete entries into a database by using datagrid view wizard? I have already established a database connection using the wizard and set the autogeneratedeletebutton and autogenerateedit button properties to true. How do you please assist me on what exactly the code must be inside the row update event ?

Satyapriya Nayak · Answer

Hi Nisha 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(); } } Thanks If this post helps you mark it as answer

Dorababu Meka · Answer

Try this article as per your requirement http://www.aspdotnet-suresh.com/2011/02/normal-0-false-false-false-en-us-x-none.html

anisha bathija · Answer

hey thank you so much for the constant help and patience  but I think there is something that I am missing out on. I tried to run the new code with my database and i got the following error. I didnot try the row update method yet as I thought I must check delete first.

anisha bathija · Answer

hey thank you so much for the constant help and patience  but I think there is something that I am missing out on. I tried to run the new code with my database and i got the following error. I didnot try the row update method yet as I thought I must check delete first.

Dorababu Meka · Answer

Can you post your design of gridview

anisha bathija · Answer

protected void GridView1_RowUpdating(object sender GridViewUpdateEventArgs e) { string sqlcon = ConfigurationManager.ConnectionStrings[BPSTConnectionString].ToString(); SqlConnection con = new SqlConnection(sqlcon); con.Open(); string UserID = GridView1.Rows[e.RowIndex].Cells[0].Text; //ID string Username = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text; string password = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text; string Firstname = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text; string Lastname = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text; SqlDataAdapter da = new SqlDataAdapter(update User_Table set User_Name=' + Username + '  Password= ' + password + ' First_Name = ' + Firstname + ' Last_Name = ' + Lastname + 'where User_ID=' + UserID + ' con); //cmd.ExecuteNonQuery(); GridView1.EditIndex = -1; BindData(); DataSet ds = new DataSet(); da.Fill(ds); //return (ds); con.Close(); } I am getting the following error:

Dorababu Meka · Answer

Try this sample gridview OnRowUpdating=grd_RowUpdating> protected void Page_Load(object sender EventArgs e) { if (!IsPostBack) { bind(); } } public void bind() { string strQuey = select IdName from tbldemo; // here your table name MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings[sqlcon].ConnectionString); // your connection stirng MySqlCommand cmd = new MySqlCommand(strQuey con); con.Open(); MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); grd.DataSource = ds; grd.DataBind(); } Edit and Update protected void grd_RowEditing(object sender GridViewEditEventArgs e) { grd.EditIndex = e.NewEditIndex; // turn to edit mode bind(); } protected void grd_RowUpdating(object sender GridViewUpdateEventArgs e) { string id = grd.Rows[e.RowIndex].Cells[1].Text; //ID string company = ((TextBox)grd.Rows[e.RowIndex].Cells[2].Controls[0]).Text; //Company MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings[sqlcon].ConnectionString); MySqlCommand cmd = new MySqlCommand(update tbldemo set Name=' + company + ' where Id=' + id + ' con); con.Open(); cmd.ExecuteNonQuery(); grd.EditIndex = -1; bind(); } protected void grd_RowCancelingEdit(object sender GridViewCancelEditEventArgs e) { grd.EditIndex = -1; bind(); } Similarly for delete