How I do Update in SQL with c#

This error message shows that you have opened a transaction and it is still open at the point of executing ExecuteNonQuery,

You are executing ExecuteNonQuery before commitment of transaction.

Define

comando.Transaction = trx; 

So that ExecuteNonQuery will be executed on the same transaction.

you forgot to set transaction

 comando.Transaction = trx; 

You need to assign the transaction to the command, like so:

SqlCommand comando = new SqlCommand(); comando.Transaction = trx; 

I would also recommend setting the Connection property outside of the for loop too, so you code would look like this:

SqlCommand comando = new SqlCommand(); comando.Transaction = trx; comando.Connection = conexao; for (int j = 0; j < arr.Length; j++) { comando.CommandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = " + arr[j].Col3 + " WHERE CD_RECURSO = " + arr[j].Col1; comando.ExecuteNonQuery(); } trx.Commit(); 

You need to set SqlCommand's transaction property.

 SqlCommand comando = new SqlCommand(); comando.Transaction = trx; 

Your sqlCommand doesn't know about your transaction.

Here's a copy-paste fix:

SqlConnection conexao = new SqlConnection(WebConfigurationManager.ConnectionStrings["strConexao"].ToString()); conexao.Open(); SqlTransaction trx = conexao.BeginTransaction(); try { for (int j = 0; j < arr.Length; j++) { var commandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = " + arr[j].Col3 + " WHERE CD_RECURSO = " + arr[j].Col1; SqlCommand comando = new SqlCommand(commandText, conexao, trx); comando.ExecuteNonQuery(); } trx.Commit(); return 1; } catch (SqlException ex) { try { trx.Rollback(); return 0; } catch (Exception exRollback) { return 0; } } 

As has been pointed out, you never assign your transaction to the command. However there are a few other points I have picked up on.

First and foremost USE PARAMETRISED QUERIES, they will improve performace, type safety and most importantly save you from SQL Injection Attacks.

So instead of:

comando.CommandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = " + arr[j].Col3 + " WHERE CD_RECURSO = " + arr[j].Col1; 

You would use:

comando.CommandText = "UPDATE RECURSO_CLIENTE SET NM_CLIENTE = @Col3 WHERE CD_RECURSO = @Col1"; comando.Parameters.AddWithValue("@Col3", arr[j].Col3); comando.Parameters.AddWithValue("@Col1", arr[j].Col1); 

Secondly, wrap your sql command object with a using wrapper to ensure it is properly dispose of, there is no benefit from reusing the same object over and over again (and this can cause problems):

for (int j = 0; j < arr.Length; j++) { using (var comando = new SqlCommand("UPDATE RECURSO_CLIENTE SET NM_CLIENTE = @Col3 WHERE CD_RECURSO = @Col1", conexao)) { comando.Transaction = trx; comando.Parameters.AddWithValue("@Col3", arr[j].Col3); comando.Parameters.AddWithValue("@Col1", arr[j].Col1); comando.ExecuteNonQuery(); } } 

Finally, if you are using SQL-Server 2008+ you can use Table valued Parameters to do this update in a single query:

You first need a type

CREATE TABLE YourTypeName AS TABLE (Col1 INT, Col3 INT); 

Then your update statement would be something like:

DECLARE @UpdateValues AS YourTypeName; MERGE RECURSO_CLIENTE rc USING @UpdateValues u ON rc.CD_RECURSO = u.Col1 WHEN MATCHED UPDATE SET NM_CLIENTE = u.Col3; 

This means a single statement and you don't need to use explicit transactions. (You might wonder why I have used merge instead of UPDATE, here is why). So putting it all together you would get:

var dataTable = new DataTable(); dataTable.Columns.Add("Col1", typeof(int)); dataTable.Columns.Add("Col3", typeof(int)); for (int j = 0; j < arr.Length; j++) { var newRow = dataTable.NewRow(); newRow[0] = arr[j].Col1; newRow[1] = arr[j].Col3; dataTable.Rows.Add(newRow); } string sql = @" MERGE RECURSO_CLIENTE rc USING @UpdateValues u ON rc.CD_RECURSO = u.Col1 WHEN MATCHED UPDATE SET NM_CLIENTE = u.Col3;"; using (var conexao = new SqlConnection(WebConfigurationManager.ConnectionStrings["strConexao"].ToString())) using (var comando = new SqlCommand(sql, conexao)) { conexao.Open(); var tableParam = new SqlParameter("@UpdateValues", SqlDbType.Structured); tableParam.TypeName = "@YourTypeName"; tableParam.Value = dataTable; comando.Parameters.Add(tableParam); comando.ExecuteNonQuery(); }