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

From: MarkV (markvN0_at_SP4Mdardaniaonline.com)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 13:43:44 +0100

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:%23jtg5iPBEHA.2940@TK2MSFTNGP11.phx.gbl...
> 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" <markvN0@SP4Mdardaniaonline.com> wrote in message
> news:uDHcElOBEHA.1600@tk2msftngp13.phx.gbl...
> > 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:uYuVdROBEHA.1544@TK2MSFTNGP09.phx.gbl...
> > > 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" <markv@dardaniaonline.com> wrote in message
> > > news:%23BJQeyJBEHA.580@TK2MSFTNGP11.phx.gbl...
> > > > 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?
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages