How to commit and rollback transaction using ADO

[Cyberseduct]

I'm currently developing a system whereby i'm using Adodc connection components. Now my problem starts when i want to update a record whose data must be inserted in many tables at one go. I cannot roolback the transaction whenever a transaction did not occur completely. I know that with ado it is not possible to commit and rollback a transaction. Is there any other solution to this problem?

 

[sunaj]

Hi,

I'm not sure exactly what you're asking, but it is possible to commit/rollback transactions with ADO:

-----------------------------------------------------------
connection.BeginTrans
'Do your recordset manipulations here
If MsgBox("Commit changes to database?",VbYesNo)=VbYes then
Connection.CommitTrans
else
Connection.RollbackTrans
end if
-----------------------------------------------------------

Sunaj

 

[dellyjm]

CyberSeduct i think a transaction for our purposes here would be for each time a database table is altered (records changed/deleted/added).

As Sunaj is saying instead of commiting each 'transaction' do a conn.BeginTrans which will assign one big trans id (background) and record everything that has been done under that transid e.g. 900 inserts and 2000 updates.

If something goes wrong anytime during those 2900 things issue a rollback which will in effect undo everything and restore DB, if everything's ok then commit all 2900 things one time. That's when the data is written.

Delton Phillips.
New Generation Solutions
ngs@cwjamaica.com
deltonphillips@hotmail.com

Better thinking...better results.

 

[chiph]

Not every ADO, OLEDB provider allows nested transactions. So if you wanted to do something like:

[tab]Begin Trans
[tab][tab]BeginTrans
[tab][tab]CommitTrans
[tab][tab]:
[tab][tab]BeginTrans
[tab][tab]CommitTrans
[tab]CommitTrans

I think what would happen is when it saw the first CommitTrans, it would commit both. When it got to the final CommitTrans call, you'd get a "not in transaction" error.

Cyberseduct --
If you're doing a mass update, and want to do it inside a transaction, be aware that in most databases uncommitted transactions get stored in the Temp tablespace before being committed. So you need twice as much database storage as you might think -- 100% for storing the uncommitted records, and another 100% for storing the records after being committed. The temp space obviously gets freed afterwards, but for that little bit of time you need twice the storage.

Also be aware that it's actually slower doing all inserts in one large transaction. The database has to write the uncommitted record to the temp space, and then when the commit is issued, has to re-write it into it's final location. That's twice as many disk accesses needed.

Hope this helps.

Chip H.