Maybe I'm making this harder than it should be, but I'm really struggling 

with getting transactions to work.  It is supposed to be a simple matter 

of wrapping a transaction around some database manipulation statements.  

But I get error messages like "Transaction cannot start because more than 

one ODBC connection is in use" -2147168227, "[Midrosoft][ODBC SQLServer 

Driver]Cursor type changed" and "transaction cannot start while in 

firehose mode" -2147367259



First of all, these error messages are not documented on Microsoft's web 

site http://www.microsoft.com/data/ado/.  



Having spent a bunch of time researching terms like ADO and transactions, 

I have discovered some clues:  Sqlserver can only support one forward only 

cursor on a connection.  Sqlserver will silently open another connection 

if the current connection is busy with a forward only cursur.  Forward 

only cursors are opened whenever you use command.exec and connection.exec 

syntax.  The only way to get a different cursor type is to use 

recordset.open.  A transaction can only exist on a single connection.  

Nowhere have I found a cogent discussion of these points, just hints 

scattered here and there.



From a design standpoint, One screen of my application presents a pick 

list of items.  The user selects one, which creates recordsets on two 

tables with a parent/child one/many relationship.  These recordsets are 

used to populate a work screen where the user can add/change/delete 

certain information in both tables.  What this means is that I need an 

open connection to support the two recordsets.  Along the way there are 

other bits of SQL being executed, like recalculating a running total.  I 

have modified the app so all the misc SQL opens  recordsets, trying to 

avoid the forward only cursors popping up, but I still get error messages 

as above.  (I am considering redesign to avoid the standing connection and 

conform to a recommendation I read to open the connection, do your work 

and immediately close the connection.  But that redesign is a big deal.)



What finally seems to have helped is to use connection.execute("Begin 

Transaction") instead of connection.BeginTrans.  Which makes no sense to 

me.  They should be precisely identical.  One speaks to Sqlserver, the 

other speaks to Sqlserver via ADO (I think).



Has anybody experienced theses problems?  And figured out solutions?  

Found any documentation?  Thank you for reading through all this, but I 

didn't see how to make it any shorter/simpler.

I highly recommend getting in and out as fast as possible.  Open your

connection, read the data and then close the connection.  You don't want

to keep a connection open while your user is staring at the screen (or

getting a cup of coffee).  You also won't need a transaction when your

only reading data.  If you hold on to that connection, your application

won't be scalable.  When your user is ready to save the data that is

modified, use an update sql statement.  Put this in a transaction if

necessary.

 



Shane Berry

Spectrum Tech

"Build a prototype database application in seconds!"

For information goto:   http://www.protopro.net





-----Original Message-----

From: bret brunner [mailto:bbrunner@b...] 

Sent: Tuesday, March 12, 2002 10:01 AM

To: ActiveX_Data_Objects

Subject: [activex_data_objects] ado transactions, cursors and too many

connections





Maybe I'm making this harder than it should be, but I'm really

struggling 

with getting transactions to work.  It is supposed to be a simple matter



of wrapping a transaction around some database manipulation statements.



But I get error messages like "Transaction cannot start because more

than 

one ODBC connection is in use" -2147168227, "[Midrosoft][ODBC SQLServer 

Driver]Cursor type changed" and "transaction cannot start while in 

firehose mode" -2147367259



First of all, these error messages are not documented on Microsoft's web



site http://www.microsoft.com/data/ado/.  



Having spent a bunch of time researching terms like ADO and

transactions, 

I have discovered some clues:  Sqlserver can only support one forward

only 

cursor on a connection.  Sqlserver will silently open another connection



if the current connection is busy with a forward only cursur.  Forward 

only cursors are opened whenever you use command.exec and

connection.exec 

syntax.  The only way to get a different cursor type is to use 

recordset.open.  A transaction can only exist on a single connection.  

Nowhere have I found a cogent discussion of these points, just hints 

scattered here and there.



From a design standpoint, One screen of my application presents a pick 

list of items.  The user selects one, which creates recordsets on two 

tables with a parent/child one/many relationship.  These recordsets are 

used to populate a work screen where the user can add/change/delete 

certain information in both tables.  What this means is that I need an 

open connection to support the two recordsets.  Along the way there are 

other bits of SQL being executed, like recalculating a running total.  I



have modified the app so all the misc SQL opens  recordsets, trying to 

avoid the forward only cursors popping up, but I still get error

messages 

as above.  (I am considering redesign to avoid the standing connection

and 

conform to a recommendation I read to open the connection, do your work 

and immediately close the connection.  But that redesign is a big deal.)



What finally seems to have helped is to use connection.execute("Begin 

Transaction") instead of connection.BeginTrans.  Which makes no sense to



me.  They should be precisely identical.  One speaks to Sqlserver, the 

other speaks to Sqlserver via ADO (I think).



Has anybody experienced theses problems?  And figured out solutions?  

Found any documentation?  Thank you for reading through all this, but I 

didn't see how to make it any shorter/simpler.




$subst('Email.Unsub').