Cannot find data after commit

[dtqui]

Hi,
I'm using VB5 and ADO. I have 2 tables, one to store the transactions, the other to store the last reference number assigned to the transactions.

Main:
call A until EOF

Function A:
oData.BeginsTrans
call getRef 'to get the next reference number
oData.Execute "Insert ..." 'to insert transactions with reference number as primary key.
oData.CommitTrans

Function getRef:
Select the last reference number used from database (eg 32)
Increment by one (eg 33)
Update the last reference number to 33
Return the reference number to function A.

My problems starts on the 2nd time function A is called. If I run in VB debug mode step by step, I'm able to select the last reference number (33). But if I run the exe, my selection returns 32.

Can anyone please tell me where my 33 went to even after I committed the transactions? :-( Thank you..

 

[bobhilt]

If you are using separate connections (or data-centric controls--What is oData?) for the two functions, they will not have visibility to one another when within a transaction.

Using an ado connection (not a data control), pass the connection to getRef(), or make the scope such that it's visible to both functions. Then use the same connection in both places.

To answer the last question, if you inserted 33 and committed it, it should be visible. Are you committing in getref?

 

[dtqui]

Hi,

I'm using only one Global ADO Connection throughout the whole program. (oData is set as ADO.Connection in Main).

I also thought that 33 should be visible after I commit ( I only commit in Function A). It seems that if I run the exe version of the program, it won't 'see' 33 but if I use VB step-by-step debug mode, it can 'see' 33. Very weird!!

Anyone come across such weird cases??

 

[listener22]

is your exe multi-thread? Jim

oracle, vb

 

[bobhilt]

Yeah, that last part about stepping through in code and not working in .exe is pretty strange! Is it possible for you to try a different ado provider? (OLE DB, ODBC, etc.?) It sounds like you're doing the right stuff (probably not what you needed to hear!) Sorry. Best of luck.