Transaction problem new post

[Rotsey]

Hi,

I did not know how to get my post to the top again so I have reposted this
problem

I was getting a execution cancelled error when running the code using
transactions below.

Well the code is quite long. But the crux of it is like this.


Public Sub ProcessInvoiceData(CustNoDetails)

objws.Begintrans

For cntr = 1 to NmberOfInvoiceToCreate

invno = CreateInvoiceNumber(CustNoDetails(cntr))

SQL = "SELECT * FROM Invoices WHERE invNumber = " & invno
Set rs2 = gobjDBodbc.OpenRecordset(sql, dbOpenSnapshot)
IT BOMBS HERE AT THE 2ND ITERATION AS THE invno HAPPENS TO BE THE SAME AS
THE FIRST
INTERATION INVOICE

if rs2.bof then
CreateInvoice(invno)
Endif

Next

objws.CommiTrans

As you can see it fails to do with querying the first invoice added to the
transaction.

If you need more info let me know.

rotsey

 

[Brendan Reynolds]

This is probably because the Database variable, 'gobjDBodbc', is
instantiated outside of the transaction. Changes made within the transaction
are visible only within the transaction, so a recordset opened using the
'gobjDBodbc' object will not see any records that were added within the
transaction.

 

[Rotsey]

I see.

well this is how those variables get instantiated

Set objws= CreateWorkspace("ODBCWorkspace", "dbo", "", dbUseODBC)
Workspaces.Append objws
objws.DefaultCursorDriver = dbUseDefaultCursor
Set gobjDBodbc = objws.OpenDatabase(rs!DSN, dbDriverNoPrompt, False, _
"ODBC;DSN=" & rs!DSN & ";SERVER=" & rs!Server &
";WSID=LESTER34;DATABASE=" & rs!Database &
";TranslationName=Yes;QueryLogFile=Yes")

Can you explain how it should be??

rotsey

 

[Brendan Reynolds]

I could be wrong about the source of the problem. As a test to attempt to
narrow it down a bit, could you try temporarily running the code without
using a transaction, by commenting out the lines indicated below. The result
should at least confirm whether the problem is related to the use of
transactions or not.

'temporarily comment out this line
'objws.Begintrans

For cntr = 1 to NmberOfInvoiceToCreate

invno = CreateInvoiceNumber(CustNoDetails(cntr))

SQL = "SELECT * FROM Invoices WHERE invNumber = " & invno
Set rs2 = gobjDBodbc.OpenRecordset(sql, dbOpenSnapshot)


if rs2.bof then
CreateInvoice(invno)
Endif

Next

'temporarily comment out this line
'objws.CommiTrans

 

[Rotsey]

I removed the transaction and the code ran fine.

So then I tried this. Added this global variable

Set gobjConn = gobjWSodbc.OpenConnection(mConnectName, dbDriverNoPrompt,
False, _
"ODBC;DSN=" & rs!DSN & ";SERVER=" & rs!Server &
";WSID=LESTER34;DATABASE=" & rs!Database &
";TranslationName=Yes;QueryLogFile=Yes")

Then changed this line to

Set rs2 = gobjDBodbc.OpenRecordset(sql, dbOpenSnapshot)

to
Set rs2 = gobjConn .OpenRecordset(sql, dbOpenSnapshot)

And all works fine.


rotsey