Why do we need oConn.RollbackTrans

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Why do we need oConn.RollbackTrans

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-16 : 13:57:10

I did the following test.

------------------------------

Dim sConnStr As String
Dim sMyQuery As String
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset

sConnStr = "UID=" & "sa" & ";PWD=" & "123" & ";driver=SQL Server;server=T;database=test"
oConn.ConnectionString = sConnStr

oConn.Open
oConn.BeginTrans

sMyQuery = "UPDATE Table1 SET Data ='6' WHERE RecordID = '1'"

oConn.Execute (sMyQuery)

Exit Sub

oConn.CommitTrans

---------------------------------

The Update to Table1 takes place, but I exit the procedure before
calling oConn.CommitTrans. Therefore the transaction did not take
place.

Why do we need the oConn.RollbackTrans function?

thanks

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-16 : 14:03:03

I just read
"The RollbackTrans method cancels all pending changes made since the last BeginTrans method call was used to initiate the transaction."

What if the connection was created inside a sub. When the sub
exits, does vb garbage the connection and the call the beginntrans?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-16 : 15:02:36

Try doing that update now from query analyzer or just running this statement. SELECT * FROM Table1 WHERE RecordID = '1'

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-16 : 15:27:35

Hi, not exactly sure what you mean.

Right now, I am correcting my code so if I exit the sub
for any reason after I call beginTrans I will call the rollback
function (aside from calling it on an error trap).

What if the program crashes before calling commitTrans or
rollbacktrans. Does SQL Server still hold the fact that a transaction was started and not yet committed or rolled back?

anolis
Starting Member

14 Posts

Posted - 2004-05-16 : 17:09:32

I cannot explain to you exactly how it works, but oConn.BeginTrans is not the same as a TSQL-Transaction. It's a different kind of transaction, which has nothing to do with SQL-Server. MSDN should help here.

hmm, I think I recall now. Since the transaction is arranged by ADO you don't need to commit. You need to commit only before you start a new trasaction.

Anolis,
Developer,
MCAD for .NET

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-16 : 17:16:10

Depends on how the provider interfaces with sql server. Often creating a transaction like this doesn't do anything and the update happens anyway.

If it works and you miss out the rollback then the transaction will probably stay open until the connection is dropped (by exiting the sub) at which time an error will be generated and the transaction rolled back - but it will hold locks until then.
Better to send a begin tran than do a oConn.BeginTrans.
in the code you have here it doesn't need a transaction as there is a single update which will complete or fail if there is an error.

It's a bit like asking why you have an exit sub when you can just go to the end sub or an oConn.Close when it will be automatically closed by going out of scope.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources