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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Miha Markic [MVP C#] (miha)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 11:33:09 +0100

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