Bulk Update all in Gridview

Question

I have a gridview with a simple Insert, Update, Delete processes which works great. I want to now have a 'save all' feature that will go a step further than the simple update. Instead of updating one row at a time in my gridview, I want a button to update all rows and their changes if any. What's happening is, I am updating the values across the grid, I click update all, it looks like it's updated, but when I check the database table values, there are no changes. Can anyone help? Code behind: protected void gridview1_RowCommand( object sender, GridViewCommandEventArgs e) { gridview1 row = (GridViewRow)(((Control)e.CommandSource).NamingContainer); string Id = e.CommandArgument.ToString(); DropDownList Ddllsit1 = gridview1.FooterRow.FindControl( "Ddllsit1" ) as DropDownList; TextBox box1 = (TextBox)row.FindControl( "box1" ); if (e.CommandName == "Insert" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "INSERT" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.Parameters.AddWithValue( "@type" , Ddllsit1.SelectedItem.ToString()); sqlCmd.Parameters.AddWithValue( "@value" , box1.Trim()); sqlCmd.ExecuteNonQuery(); sqlCon.Close(); } else if (e.CommandName == "Update" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "UPDATE" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.Parameters.AddWithValue( "@type" , Ddllsit1.SelectedItem.ToString()); sqlCmd.Parameters.AddWithValue( "@value" , box1.Trim()); sqlCmd.ExecuteNonQuery(); sqlCon.Close(); lblDeleteMessage.Text = "" ; } else if (e.CommandName == "Delete" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "DELETE" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.ExecuteNonQuery(); lblDeleteMessage.Text = "Deleted Successfully" ; } else if (e.CommandName == "UpdateAll" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); foreach (GridViewRow row1 in this .gridview1.Rows) { TextBox value = row1.FindControl( "value" ) as TextBox; DropDownList Ddllsit1 = row1.FindControl( "Ddllsit1" ) as DropDownList; SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "UPDATEALL" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.Parameters.AddWithValue( "@type" , Ddllsit1.SelectedItem.ToString()); sqlCmd.Parameters.AddWithValue( "@value" , box1.Trim()); sqlCmd.ExecuteNonQuery(); sqlCon.Close(); } } } Let me know what other information I may provide.

Sachin Singh · Answer

you are closing the connection after the first iteration which never opens again. however, if you would have changed the first row of grid, then it would have worked at least once. Modify as else if (e.CommandName == "UpdateAll" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); foreach (GridViewRow row1 in this .gridview1.Rows) { TextBox value = row1.FindControl( "value" ) as TextBox; DropDownList Ddllsit1 = row1.FindControl( "Ddllsit1" ) as DropDownList; SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "UPDATEALL" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.Parameters.AddWithValue( "@type" , Ddllsit1.SelectedItem.ToString()); sqlCmd.Parameters.AddWithValue( "@value" , box1.Trim()); sqlCmd.ExecuteNonQuery(); } sqlCon.Close(); }

Emi Who · Answer

Thanks. I tried binging my gridview and wrapping update method in a PostBack{}. But still pulling my hair out. Changed code as follows: Code behind: protected void gridview1_RowCommand( object sender, GridViewCommandEventArgs e) { gridview1 row = (GridViewRow)(((Control)e.CommandSource).NamingContainer); string Id = e.CommandArgument.ToString(); DropDownList Ddllsit1 = gridview1.FooterRow.FindControl( "Ddllsit1" ) as DropDownList; TextBox box1 = (TextBox)row.FindControl( "box1" ); if (e.CommandName == "Insert" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "INSERT" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.Parameters.AddWithValue( "@type" , Ddllsit1.SelectedItem.ToString()); sqlCmd.Parameters.AddWithValue( "@value" , box1.Trim()); sqlCmd.ExecuteNonQuery(); sqlCon.Close(); } else if (e.CommandName == "Update" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "UPDATE" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.Parameters.AddWithValue( "@type" , Ddllsit1.SelectedItem.ToString()); sqlCmd.Parameters.AddWithValue( "@value" , box1.Trim()); sqlCmd.ExecuteNonQuery(); sqlCon.Close(); lblDeleteMessage.Text = "" ; } else if (e.CommandName == "Delete" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "DELETE" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.ExecuteNonQuery(); lblDeleteMessage.Text = "Deleted Successfully" ; } } //To update all values on button click. protected void UpdateValues() { if (e.CommandName == "UpdateAll" ) { if (sqlCon.State == ConnectionState.Closed) sqlCon.Open(); foreach (GridViewRow row1 in this .gridview1.Rows) { TextBox value = row1.FindControl( "value" ) as TextBox; DropDownList Ddllsit1 = row1.FindControl( "Ddllsit1" ) as DropDownList; SqlCommand sqlCmd = new SqlCommand( "Storeproc" , sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue( "@Action" , "UPDATEALL" ); sqlCmd.Parameters.AddWithValue( "@Id" , Id); sqlCmd.Parameters.AddWithValue( "@type" , Ddllsit1.SelectedItem.ToString()); sqlCmd.Parameters.AddWithValue( "@value" , box1.Trim()); sqlCmd.ExecuteNonQuery(); } sqlCon.Close(); } } protected void LnkUpdateAll_Click( object sender, EventArgs e) { UpdateValues(); }

Sachin Singh · Answer

i guess, Id is not string in your procedure so change it to proper type, if id is int then sqlCmd.Parameters.AddWithValue( "@Id" ,Convert.ToInt32(Id));

Emi Who · Answer

Oh yes, thank you. Am I missing to bind the gridview data somewhere? I still have the same outcome. I update the values in the grid, I click update all, the new values appear in the view state of the page, but are not commited to the database table. (table name, columns, parameters were all triple checked to be accurate).