Using parameter in query results in BEGIN / COMMIT mismatch SQLException

Question

The following code executing a bunch of SQL statements works well...

// SQL Server 2008 R2
SqlConnection connection = null;
var runBatch = false;
try
{
    connection = new SqlConnection(connectionString);
    connection.Open();
    var command = connection.CreateCommand();

    // 1st batch
    command.CommandText = "BEGIN TRANSACTION";
    command.ExecuteNonQuery;

    // 2nd batch
    command.CommandText = "UPDATE MyTable SET MyColumn = 'foo' WHERE Name = 'bar'";
    command.ExecuteNonQuery;

    // 3rd batch
    command.CommandText = "COMMIT TRANSACTION";
    command.ExecuteNonQuery;
}
finally
{
    if (connection != null)
    {
        connection.Close();
    }
}

...unless I use a parameter:

// [...]
command.Parameters.AddWithValue("name", "bar");

// 1st batch
command.CommandText = "BEGIN TRANSACTION";
command.ExecuteNonQuery; // <= throws Exception

Exception message:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0

Note: This would work, if I combined all the statements in a single command. But in my app, the original SQL script is a bunch of batches separated by GO which is automatically split into multiple commands (so I have no control over how or what transactions are used):

/* Original SQL */

BEGIN TRANSACTION
GO
UPDATE MyTable
SET MyColumn = 'foo'
WHERE NAME = @name
GO
COMMIT TRANSACTION
GO

I have read about this exception, but nothing really seems to apply to this special scenario.

I have not figured out yet what exact difference introducing a parameter makes here and why it breaks the code. Anyone got a solution for this?

Where is the Name of the tran? [MSDN](https://msdn.microsoft.com/fr-fr/library/ms188929.aspx#Syntaxe) — Ilyes, Oct 25 '16 at 14:19
@Tanner I don't really have control over what transactions are used in the SQL, see my edit to the question... — marsze, Oct 25 '16 at 14:27

score 0 · Answer 1 · answered Oct 26 '16 at 16:22

Try this:

// SQL Server 2008 R2

SqlConnection connection = null;

var runBatch = false;

try
{
    connection = new SqlConnection(connectionString);
    connection.Open();
    var command = connection.CreateCommand();

    // 1st batch
    command.CommandText = "BEGIN TRANSACTION";
    command.ExecuteNonQuery();

    // 2nd batch
    command.CommandText = "UPDATE MyTable SET MyColumn = 'foo' WHERE Name = @name";
    command.Parameters.AddWithValue("name", "bar");
    command.ExecuteNonQuery();

    // 3rd batch
    command.CommandText = "COMMIT TRANSACTION";
    command.Parameters.Clear();
    command.ExecuteNonQuery();
}
finally
{
    if (connection != null)
    {
        connection.Close();
    }
}

This could work, but it's really just a workaround and not an explanation for this behavior. Also, in reality I will not know which batch will need parameters and which will break if I add any. — marsze, Oct 27 '16 at 09:17
I guess it's cause command.ExecuteNonQuery() . If you declare a Parameters where dont have parameters you will get that error. — CesarKM, Oct 27 '16 at 19:14

Using parameter in query results in BEGIN / COMMIT mismatch SQLException

1 Answers1