Problem with SqlCommand and T-SQL transaction

[Andre Ranieri]

I'm having trouble with what should be a simple task; beginning and
committing T-SQL transactions using the SQLClient. I'm using a SqlCommand
(cmd) to begin the transaction and delete records from two tables. if both
records are successfully deleted, I'd like to commit the transaction.
Everything works fine until I attempt to commit the first time the foreach
loop runs, at this point I get the following exception. I haven't been able
to make sense of what I've seen on msdn or other newsgroups; this seems like
it should be an easy process since I'm creating a new SqlCommand every time
the foreach loop runs.

Any help or suggestions would be much appreciated.

Thanks, Andre
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
foreach (DataRow dsRow in Class1.myDS.MasterAccounts.Rows)
{
//begin SQL Transaction
sSQL = "BEGIN TRAN;";
Class1.cmd = new SqlCommand(sSQL, Class1.cn);
Class1.cmd.CommandTimeout = 0;
Class1.cmd.ExecuteNonQuery();

//declare @ServiceID param.
Class1.cmd.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value =
Convert.ToInt32(dsRow["ServiceID"]);

sSQL = "DELETE FROM tblMainServices " +
"WHERE ServiceID = @ServiceID AND Service LIKE '920%';";
Class1.cmd.CommandText = sSQL;
Class1.cmd.ExecuteNonQuery();

sSQL = "DELETE FROM tblMainServices_B " +
"WHERE ServiceID = @ServiceID;";
Class1.cmd.CommandText = sSQL;
Class1.cmd.ExecuteNonQuery();

//Commit SQL Transaction
sSQL = "COMMIT TRAN;";
Class1.cmd.CommandText = sSQL;
Class1.cmd.ExecuteNonQuery();

iCount ++;

int iProgValue = Convert.ToInt16(20 * Convert.ToSingle(iCount / iRecords));
if (iProgValue != progBar.Value)
{
progBar.Value = iProgValue;
this.Update();}
}

[David Browne]

"Andre Ranieri" <An**********@discussions.microsoft.com> wrote in message
news:F5**********************************@microsof t.com...

I'm having trouble with what should be a simple task; beginning and
committing T-SQL transactions using the SQLClient. I'm using a SqlCommand
(cmd) to begin the transaction and delete records from two tables. if
both
records are successfully deleted, I'd like to commit the transaction.
Everything works fine until I attempt to commit the first time the foreach
loop runs, at this point I get the following exception. I haven't been
able
to make sense of what I've seen on msdn or other newsgroups; this seems
like
it should be an easy process since I'm creating a new SqlCommand every
time
the foreach loop runs.

Any help or suggestions would be much appreciated.

Thanks, Andre

Try this instead:

public static SqlConnection Connect()
{
SqlConnection con = new SqlConnection("..");
con.Open();
return con;
}

......
using (SqlConnection con = Connect())
foreach (DataRow dsRow in Class1.myDS.MasterAccounts.Rows)
{
using (SqlTransaction tran = con.BeginTransaction() )
{
SqlCommand cmd1 = new SqlCommand( "DELETE FROM tblMainServices " +
"WHERE ServiceID = @ServiceID AND Service LIKE '920%';",con,tran);
cmd1.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value =
Convert.ToInt32(dsRow["ServiceID"]);

SqlCommand cmd2 = new SqlCommand( "DELETE FROM tblMainServices_B " +
"WHERE ServiceID = @ServiceID AND Service LIKE '920%';",con,tran);
cmd2.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value =
Convert.ToInt32(dsRow["ServiceID"]);

cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tran.Commit();
iCount ++;
//...
}
}
David

[Andre Ranieri]

David,

I wanted to thank you for your reply, I was able to get the code working
based on your feedback.

I'm still not sure why my T-SQL transactions didn't work and why the ADO.NET
tran did.