ADODB Transaction Help

[aco636]

Hi there

Have written small script to add records to various tables.
Run as a SQL Script (on Advantage Database OLEDB connection), SQL script works fine. However, I want to run this as a processed transaction , if one fails then roll all back.

However, when using commandtext, will not rollback if errors occur.

SQL is broken in 5, Update, Insert(Using select), Insert(Using Select), Insert(Using Values), Update. All in this order.

In order to run script I have imported the SQL in to an array split on ";". then I use

cn.Open 'Open DB Connection
cn.BeginTransaction
For i = Lbound(arrMyArray) to UBound(arrMyArray)
cmd.CommandText = arrMyArray(i)
cmd.Execute
Next
If cn.Errors.Count <> 0 Then
Blah Blah....
cn.RollbackTrans
cn.Errors.Clear
Else
'All OK
cn.CommitTrans
cn.Close

 

[dilettante]

Is cn the connection that cmd is using?

 

[aco636]

Thanks in advance for any help

Answer to your question is yes
Code follows

	Set cn = CreateObject("ADODB.Connection")
	Set cmd = CreateObject("ADODB.Command")


cn.Open
cn.BeginTrans
For i = LBound(arrSQL) To UBound(arrSQL)-1
cmd.CommandText = arrSQL(i)
cmd.Execute
'Trap any errors to report later
Next
If cn.Errors.Count <> 0 Then
WScript.Echo cn.Errors.Count & " Errors Found, Transaction/s failed and were rolled back"
cn.RollbackTrans
cn.Errors.Clear
Else
cn.CommitTrans
WScript.Echo "No Errors Found: " & strSQLErrors
End If

 

[PHV]

You have to set the connection property of the command object (before the For loop):
Set cmd.ActiveConnection = cn

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aco636]

Hi PHV

Have to say you have helped me out more than once in the past. Yes you are quite right.

Cant see the wood for the trees!
REgards ACO
ps thanks to dilettante for your input also