ExamGuru IT/.Net Guru : Rollback transaction doesnt work when DataAdapter.Update() throws exception caused by RAISEERROR in a trigger on microsoft.public.dotnet.framework.odbcnet

.NETGURU

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

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.odbcnet.

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?

Reply to this message...

Miha Markic [MVP C#]

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" <Click here to reveal e-mail address> wrote in message
news:%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

MarkV

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 [MVP C#]" <miha at rthand com> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Miha Markic [MVP C#]

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" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

MarkV

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 [MVP C#]" <miha at rthand com> wrote in message
news:%Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Bobby

Miha Markic [MVP C#] wrote:

[Original message clipped]

Reply to this message...

	System.Data.ConnectionState
	System.Data.Odbc.OdbcTransaction
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataAdapter
	System.InvalidOperationException
	System.Windows.Forms.MessageBox

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification