2 BeginTrans at the same time

[oneshadow]

Hi,
Using VB 6, SQL Server 7.0 (no SP), ADO 2.5
I need to update into 2 databases : DB_1 and DB_2. Coz this process is critical, I used the Transaction method.

The code goes like below :
'Begin transaction
DB_1.BeginTrans
DB_2.BeginTrans

'processing something...

'See if everything is OK

If bValid Then
DB_1.CommitTrans
DB_2.CommitTrans
Else
DB_1.RollbackTrans
DB_2.RollbackTrans
End If


The problem:
-. Sometimes it update NOTHING on table at the second database (DB_2)
-. Or, it updates only a part of fields on table at DB_2

What can possible cause this bugs beside the network traffic?
The client machine runs on an Win98, with Intel 133 Pentium and 32 MB RAM. Does opening 2 transactions at the same time are too heavy for it?

 

[chiph]

The basic ADO transactioning doesn't work across multiple databases -- only within the same database.

You'll need to write a COM+ component to handle this (and use COM+ transactions). COM+ makes use of the DTC to handle this situation correctly.

Chip H.

 

[oneshadow]

Chiph: Thanks to your reply.
Currently, each database in my app has its own ADO connection. So there are two different connections for two different databases. Is this scenario apply to what you've said?

I don't think I have enough time right now to write a COM+ component as you suggested. (beside, I have no idea how to write it#-). Can it made with VB? Or VC++ will be better?)

I'm planning to test my app again without ADO transaction and see what happened.

 

[chiph]

oneshadow -

ADO transactions apply to the connection object. So if you have two connection objects, you have two separate transactions going, not one like you want.

Obviously, I can't tell you how critical it is to your app to have your database changes in a transaction, only you can. But if you really really need this functionality, COM+ is the way to go.

It's very easy to create a COM+ component in VB. All you do is start a ActiveX DLL project, add a reference to the COM+ application library, and tell your class to implement ObjectControl. Also set your class's MTSTransactionMode to "2 - Requires Transaction". ObjectControl will add three methods to your class:

Private Sub ObjectControl_Activate()
Private Function ObjectControl_CanBePooled() As Boolean
Private Sub ObjectControl_Deactivate()

For the CanBePooled, return FALSE (until VB7/.NET comes out). In the Activate method, insert code that you would have normally put in the Class_Initialize event. In the DeActivate method, insert the code you would normally have put into the Class_Terminate event.

Now add a new public method (call it DoTransaction, or something). In your method add this code:
[tt]
[tab]On Error Goto GotAnError

[tab]Dim objContext As ObjectContext
[tab]Set objContext = GetObjectContext

[tab]' Do your database stuff
[tab]' Do not use any ADO transactions, COM+ does it for you

[tab]objContext.SetComplete

[tab]Exit Function ' <-------

GotAnError:
[tab]objContext.SetAbort

End Sub
[/tt]

The context object is the COM+ context. When you call SetComplete, your changes to the database are committed. If an error occurs, the .SetAbort will roll your changes back.

Hope this helps. Please think about becoming a TekTips member -- It doesn't hurt, and you won't receive any spam as a result.

Chip H.