Transactions: Puzzling Error Message

[Gene Wirchenko]

Dear C-Sharpies:

I have written a very simple program that communicates with SQL
Server Express 2008. It executes the following commands:

string[] SQLCmdList=
{
"use master" // 0
,
"drop database SQLServerAccess" // 1
,
"create database SQLServerAccess" // 2
,
"use SQLServerAccess" // 3
,
"drop table ClientBasic" // 4
,
"create table ClientBasic"+ // 5
" ("+
" CliCode char(3) not null primary key,"+
" CliName varchar(40) not null,"+
" DisbMin money not null"+
" )"
,
"insert into ClientBasic"+ // 6
" (CliCode,CliName,DisbMin)"+
"values"+
" ('ABC','A Better Company',100.00),"+
" ('BCD','Binary Code Designers',50.00),"+
" ('CDE','Computer Data Entry',200.00),"+
" ('DEF','Data Entry Functions',50.00),"+
" ('EFG','Entry for Gene',25.00)"
,
"insert into ClientBasic"+ // 7
" (CliCode,CliName,DisbMin)"+
"values"+
" ('FGH','Flowers'' Gift Honey',100.00)"
,
"select * from ClientBasic" // 8
};

and it works.

I have tried to add transactions to it (around each insert), and
I get a very puzzling error message. Here is my code:

try
{
if (i==6 || i==7) // insert
{
SqlTransaction
oTransaction=Conn.BeginTransaction();
int RowsReturned=SQLCmd1.ExecuteNonQuery();
Console.WriteLine("Rows Returned: {0}",
RowsReturned);
oTransaction.Commit();
}
else // not insert
{
int RowsReturned=SQLCmd1.ExecuteNonQuery();
Console.WriteLine("Rows Returned: {0}",
RowsReturned);
}
}
catch (Exception e)
{
Console.WriteLine("Command {0}
exception:\n"+e.Message, i);
}

and here is the relevant output:

Executing command 6
insert into ClientBasic (CliCode,CliName,DisbMin)values ('ABC','A
Better Company
',100.00), ('BCD','Binary Code Designers',50.00), ('CDE','Computer
Data Entry',2
00.00), ('DEF','Data Entry Functions',50.00), ('EFG','Entry for
Gene',25.00)
Command 6 exception:
*****ExecuteNonQuery requires the command to have a transaction when
the connection a
ssigned to the command is in a pending local transaction. The
Transaction prope
rty of the command has not been initialized.*****

Executing command 7
insert into ClientBasic (CliCode,CliName,DisbMin)values
('FGH','Flowers'' Gift H
oney',100.00)
Command 7 exception:
SqlConnection does not support parallel transactions.

Without the begin transaction and commit method calls, my code
works fine. What, please, is wrong with my transaction code? What
does the asterisk-flagged error message mean?

Sincerely,

Gene Wirchenko

 

[Gene Wirchenko]

[snip]

The exception text actually gives a good hint !

"ExecuteNonQuery requires the command to have a transaction when
the connection assigned to the command is in a pending local
transaction"

Try beginning the transaction before you create the command
that are going to be part of the transaction.

I just did. It did not work. I got the same error. My code:

for (int i=0; i<SQLCmdList.Length; i++)
{
Console.WriteLine("Executing command {0}\n{1}",
i,SQLCmdList);
SqlTransaction oTransaction=null;
if (i==6 || i==7) // transaction if insert
oTransaction=Conn.BeginTransaction();
SqlCommand SQLCmd1=new SqlCommand(SQLCmdList,Conn);
if (i+1!=SQLCmdList.Length) // All but the last are
not queries.
try
{
int RowsReturned=SQLCmd1.ExecuteNonQuery();
Console.WriteLine("Rows Returned: {0}",
RowsReturned);
if (i==6 || i==7) // commit transaction if
insert
oTransaction.Commit();
}
catch (Exception e)
{
Console.WriteLine("Command {0}
exception:\n"+e.Message, i);
}
else // query
{
SqlDataReader DataReader=SQLCmd1.ExecuteReader();
while (DataReader.Read())
Console.WriteLine(
"{0} {1} {2}",
DataReader[0],DataReader[1],DataReader[2]);
}
Console.WriteLine();
}

I also tried reordering so that the command cases are separate,
and got the same error.

The text that I am studying (Wrox's "Professional C#, 3rd
Edition") has a very simple, in-passing example, but no actual
commands being executed in it, just a comment. It has the begin
transaction method call just after the connection .Open(). I have
been just digging through the book, and maybe, some of their sample
code will have something.

Sincerely,

Gene Wirchenko

 

[Arne Vajhøj]

[snip]

The exception text actually gives a good hint !

"ExecuteNonQuery requires the command to have a transaction when
the connection assigned to the command is in a pending local
transaction"

Try beginning the transaction before you create the command
that are going to be part of the transaction.

I just did. It did not work. I got the same error. My code:

for (int i=0; i<SQLCmdList.Length; i++)
{
Console.WriteLine("Executing command {0}\n{1}",
i,SQLCmdList);
SqlTransaction oTransaction=null;
if (i==6 || i==7) // transaction if insert
oTransaction=Conn.BeginTransaction();
SqlCommand SQLCmd1=new SqlCommand(SQLCmdList,Conn);
if (i+1!=SQLCmdList.Length) // All but the last are
not queries.
try
{
int RowsReturned=SQLCmd1.ExecuteNonQuery();
Console.WriteLine("Rows Returned: {0}",
RowsReturned);
if (i==6 || i==7) // commit transaction if
insert
oTransaction.Commit();
}
catch (Exception e)
{
Console.WriteLine("Command {0}
exception:\n"+e.Message, i);
}
else // query
{
SqlDataReader DataReader=SQLCmd1.ExecuteReader();
while (DataReader.Read())
Console.WriteLine(
"{0} {1} {2}",
DataReader[0],DataReader[1],DataReader[2]);
}
Console.WriteLine();
}

I also tried reordering so that the command cases are separate,
and got the same error.

The text that I am studying (Wrox's "Professional C#, 3rd
Edition") has a very simple, in-passing example, but no actual
commands being executed in it, just a comment. It has the begin
transaction method call just after the connection .Open(). I have
been just digging through the book, and maybe, some of their sample
code will have something.

Hmm.

Then try an explicit:

SQLCmd1.Transaction = oTransaction;

right after creating SQLCmd1.

Arne

 

[Gene Wirchenko]

[snip]

The text that I am studying (Wrox's "Professional C#, 3rd
Edition") has a very simple, in-passing example, but no actual
commands being executed in it, just a comment. It has the begin
transaction method call just after the connection .Open(). I have
been just digging through the book, and maybe, some of their sample
code will have something.

Well, unpleasantness! Not a begin transaction in the whole code.

Sincerely,

Gene Wirchenko

 

[Gene Wirchenko]

On 9/14/2011 7:25 PM, Gene Wirchenko wrote:
[snip]

The text that I am studying (Wrox's "Professional C#, 3rd
Edition") has a very simple, in-passing example, but no actual
commands being executed in it, just a comment. It has the begin
transaction method call just after the connection .Open(). I have
been just digging through the book, and maybe, some of their sample
code will have something.

Hmm.

Then try an explicit:

SQLCmd1.Transaction = oTransaction;

right after creating SQLCmd1.

That makes sense. AND it worked. Thank you.

I will probably be experimenting with that some more.

Sincerely,

Gene Wirchenko

 

[Arne Vajhøj]

On 9/14/2011 7:25 PM, Gene Wirchenko wrote: [snip]

The text that I am studying (Wrox's "Professional C#, 3rd
Edition") has a very simple, in-passing example, but no actual
commands being executed in it, just a comment. It has the begin
transaction method call just after the connection .Open(). I have
been just digging through the book, and maybe, some of their sample
code will have something.

Hmm.

Then try an explicit:

SQLCmd1.Transaction = oTransaction;

right after creating SQLCmd1.

That makes sense. AND it worked.

I am surprised that it did not fetch it from the connection in
the constructor.

But there are probably some reason behind it.

I will probably be experimenting with that some more.

Maybe look at the TransactionScope class.

It is somewhat easier to use.

Arne

 

[Gene Wirchenko]

[snip]

That makes sense. AND it worked.

I am surprised that it did not fetch it from the connection in
the constructor.

So am I.

But there are probably some reason behind it.

Maybe, but what?

Maybe look at the TransactionScope class.

It is somewhat easier to use.

Thank you. I will.

The Wrox book does not go into enough detail on some things (and
mindnumbing amounts in other things). I am going to be rewriting a
Visual FoxPro system in C# and SQL Server. Do you have any
recommendations for a good C# book with enough detail in this area? I
am an experienced programmer but only now getting into .Net and SQL
Server.

Sincerely,

Gene Wirchenko

 

[Arne Vajhøj]

Thank you. I will.

The Wrox book does not go into enough detail on some things (and
mindnumbing amounts in other things). I am going to be rewriting a
Visual FoxPro system in C# and SQL Server. Do you have any
recommendations for a good C# book with enough detail in this area? I
am an experienced programmer but only now getting into .Net and SQL
Server.

Assuming that you know C# and SQL, then it really should not be that
difficult to write database code.

I think you are close to the top of the hill and even though it
may seem steep right now, then it will soon go down!

I think there are books specialized on C# and SQLServer, but I
don't think you need one.

Arne