VB6 Transactions

roughan

we develop a system that uses Acces Links Tables with an sql server 2000 DB

if i begin a transaction(begintrans)

and do a select statement from a table
and update or insert

and another select it iwll crash can anybody help me
if i skip over the begintrans its fine

anybody any ideas ?

irlrobins

Post code up, and any error messages that display. The more info you give the better

Seth Brundle

do you know at what point it crashes?

stevenmu

I'd guess at the second select ?

I think I remember something about this. If this is selecting some of the data which has already been updated/inserted I think it will cause an error. You need to commit the changes before you can select on them again.

Ginger

Sounds like the second select alright.. you can set the isolation level of the transaction to read_uncommitted

But possibly best to have a commit trans or rollback before the the second select

bonkey

stevenmu wrote: »

I think I remember something about this. If this is selecting some of the data which has already been updated/inserted I think it will cause an error. You need to commit the changes before you can select on them again.

I also vaguely remember something about this style of problem.

At one point, there was a bug (VB? Java? C#? I can't remember) where if you didn't go to the end of the first recordset, inside a transaction, a subsequent SELECT would cause a crash.

As general advice, you should be questioning the design-logic which has you running a select inside a transaction. There are occasions where it really is unavoidable, but they're pretty few and far between.