Transactions: Puzzling Error Message

  • Thread starter Thread starter Gene Wirchenko
  • Start date Start date
G

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
 
[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.
Click to expand...

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
 
[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.
Click to expand...

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.
Click to expand...


Hmm.

Then try an explicit:

SQLCmd1.Transaction = oTransaction;

right after creating SQLCmd1.

Arne
 
[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.
Click to expand...

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

Sincerely,

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.
Click to expand...

Hmm.

Then try an explicit:

SQLCmd1.Transaction = oTransaction;

right after creating SQLCmd1.
Click to expand...

That makes sense. AND it worked. Thank you.

I will probably be experimenting with that some more.

Sincerely,

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.
Click to expand...

Hmm.

Then try an explicit:

SQLCmd1.Transaction = oTransaction;

right after creating SQLCmd1.
Click to expand...

That makes sense. AND it worked.
Click to expand...

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.
Click to expand...

Maybe look at the TransactionScope class.

It is somewhat easier to use.

Arne
 
[snip]
That makes sense. AND it worked.
Click to expand...

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

So am I.
But there are probably some reason behind it.
Click to expand...

Maybe, but what?
Maybe look at the TransactionScope class.

It is somewhat easier to use.
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top