Rollback transaction doesnt work when DataAdapter.Update() throws exception caused by RAISEERROR in

  • Thread starter Thread starter Mark Vuksani
  • Start date Start date
M

Mark Vuksani

Hi,

I have a trigger like this (simplified to illustrate the problem):

--------
CREATE TRIGGER Test ON [dbo].[TestTable]
FOR INSERT, UPDATE, DELETE
AS

RAISERROR( 'test trigger error !', 16, 1)
---------

And a C# code portion: (new record inserted in dsFormTest.TestTable)

---------
OdbcTransaction odbcTrans = null;

try {

if ( odbcConn.State != ConnectionState.Open ) odbcConn.Open();
odbcTrans = odbcConn.BeginTransaction();

odaTest.InsertCommand.Transaction = odbcTrans;
odaTest.Update( dsFormTest.TestTable );

odbcTrans.Commit();

}
catch( Exception xc ) {
if ( odbcTrans != null ) odbcTrans.Rollback();
MessageBox.Show( xc.ToString() );
}

odbcConn.Close();
----------

BUT, instead of rolling back transaction, the odbcTrans.Rollback() throws
new exception with message:

System.InvalidOperationException: RollbackTransaction requires an open and
available Connection. The connection's current state is Open, Fetching.

any clues why?
 
Hi Mark,

One guess would be that odbc somehow looses the connection thus an error in
odbc provider.
Does the same error occurs if you, for example, generate an error with sql
syntax?
 
Hi Miha,

I tried with SqlConnection and SqlDataAdapter, and it works fine.

It seems that the problem is with the odbc. When the exception occurs, the
connection is not closed and the transaction is still active. I've also
checked connection state in exception handler and odbcConn.State is open,
but the the rollback throws new exception saying: "The connection's current
state is Open, Fetching" .... ?


Miha Markic said:
Hi Mark,

One guess would be that odbc somehow looses the connection thus an error in
odbc provider.
Does the same error occurs if you, for example, generate an error with sql
syntax?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Mark Vuksani said:
Hi,

I have a trigger like this (simplified to illustrate the problem):

--------
CREATE TRIGGER Test ON [dbo].[TestTable]
FOR INSERT, UPDATE, DELETE
AS

RAISERROR( 'test trigger error !', 16, 1)
---------

And a C# code portion: (new record inserted in dsFormTest.TestTable)

---------
OdbcTransaction odbcTrans = null;

try {

if ( odbcConn.State != ConnectionState.Open ) odbcConn.Open();
odbcTrans = odbcConn.BeginTransaction();

odaTest.InsertCommand.Transaction = odbcTrans;
odaTest.Update( dsFormTest.TestTable );

odbcTrans.Commit();

}
catch( Exception xc ) {
if ( odbcTrans != null ) odbcTrans.Rollback();
MessageBox.Show( xc.ToString() );
}

odbcConn.Close();
----------

BUT, instead of rolling back transaction, the odbcTrans.Rollback() throws
new exception with message:

System.InvalidOperationException: RollbackTransaction requires an open and
available Connection. The connection's current state is Open, Fetching.

any clues why?
Click to expand...
Click to expand...
 
Hi Mark,

Why are not using Sql* in the first place?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

MarkV said:
Hi Miha,

I tried with SqlConnection and SqlDataAdapter, and it works fine.

It seems that the problem is with the odbc. When the exception occurs, the
connection is not closed and the transaction is still active. I've also
checked connection state in exception handler and odbcConn.State is open,
but the the rollback throws new exception saying: "The connection's current
state is Open, Fetching" .... ?


Miha Markic said:
Hi Mark,

One guess would be that odbc somehow looses the connection thus an error in
odbc provider.
Does the same error occurs if you, for example, generate an error with sql
syntax?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Mark Vuksani said:
Hi,

I have a trigger like this (simplified to illustrate the problem):

--------
CREATE TRIGGER Test ON [dbo].[TestTable]
FOR INSERT, UPDATE, DELETE
AS

RAISERROR( 'test trigger error !', 16, 1)
---------

And a C# code portion: (new record inserted in dsFormTest.TestTable)

---------
OdbcTransaction odbcTrans = null;

try {

if ( odbcConn.State != ConnectionState.Open ) odbcConn.Open();
odbcTrans = odbcConn.BeginTransaction();

odaTest.InsertCommand.Transaction = odbcTrans;
odaTest.Update( dsFormTest.TestTable );

odbcTrans.Commit();

}
catch( Exception xc ) {
if ( odbcTrans != null ) odbcTrans.Rollback();
MessageBox.Show( xc.ToString() );
}

odbcConn.Close();
----------

BUT, instead of rolling back transaction, the odbcTrans.Rollback() throws
new exception with message:

System.InvalidOperationException: RollbackTransaction requires an open and
available Connection. The connection's current state is Open, Fetching.

any clues why?
Click to expand...
Click to expand...
Click to expand...
 
Hi Miha,

The reason why i am using ODBC API is that i want my application to be able
to work with PostgreSQL or MySQL (in case customer requires something other
than MS SQL), with as minimal changes as possible. As far as I know there
is no usable OLE DB interface for PostgreSQL, so I guess I am stuck with
ODBC.

I dont understand if this is intended behaviour, or maybe it is some kind of
bug. I would surely like to find solution (workaround?) for this.


Miha Markic said:
Hi Mark,

Why are not using Sql* in the first place?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

MarkV said:
Hi Miha,

I tried with SqlConnection and SqlDataAdapter, and it works fine.

It seems that the problem is with the odbc. When the exception occurs, the
connection is not closed and the transaction is still active. I've also
checked connection state in exception handler and odbcConn.State is open,
but the the rollback throws new exception saying: "The connection's current
state is Open, Fetching" .... ?


Miha Markic said:
Hi Mark,

One guess would be that odbc somehow looses the connection thus an
Click to expand...
error
in
odbc provider.
Does the same error occurs if you, for example, generate an error with sql
syntax?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hi,

I have a trigger like this (simplified to illustrate the problem):

--------
CREATE TRIGGER Test ON [dbo].[TestTable]
FOR INSERT, UPDATE, DELETE
AS

RAISERROR( 'test trigger error !', 16, 1)
---------

And a C# code portion: (new record inserted in dsFormTest.TestTable)

---------
OdbcTransaction odbcTrans = null;

try {

if ( odbcConn.State != ConnectionState.Open ) odbcConn.Open();
odbcTrans = odbcConn.BeginTransaction();

odaTest.InsertCommand.Transaction = odbcTrans;
odaTest.Update( dsFormTest.TestTable );

odbcTrans.Commit();

}
catch( Exception xc ) {
if ( odbcTrans != null ) odbcTrans.Rollback();
MessageBox.Show( xc.ToString() );
}

odbcConn.Close();
----------

BUT, instead of rolling back transaction, the odbcTrans.Rollback() throws
new exception with message:

System.InvalidOperationException: RollbackTransaction requires an
Click to expand...
open
and
available Connection. The connection's current state is Open, Fetching.

any clues why?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top