BeginTrans inside a For Loop - Transaction problem :-) 1

[Deadline]

Hi,
Here is a problem for you.

I get the following error. There ain't any documentation in MSDN also. Can you help me ?


[red]Cannot start more transactions on this session. Error number 2147168237[/red]

Can I have BeginTrans inside a FOR loop ? Because, currently that's what I am doing. Is it due to that ?

Have you encountered this situation earlier ?

Thanks in advance.


RR.


Keywords:

BeginTrans
RollbackTrans
CommitTrans
Transaction

 

[chiph]

festivista97 -

I suspect you're doing something like:
[tt]
Do
[tab]BeginTrans
[tab]... other code ...
Loop
CommitTrans
[/tt]
The problem with this is that Begin/Commit mostly need to go in pairs. By putting the BeginTrans inside the loop, it's executed many more times than the Commit. Why don't you try something like this:
[tt]
Dim bFirst as boolean

bFirst = True
Do
[tab]If bFirst then
[tab][tab]BeginTrans
[tab][tab]bFirst = False
[tab]End If
[tab]... other code ...
Loop
CommitTrans
[/tt]

Chip H.

 

[Deadline]

Thank you ChipH.

That worked.!!


BTW,
How many times can I BeginTrans on a Connection ?

Best Wishes.
RR.

 

[Deadline]

Hello ChipH

Just checkout this link. What is your opinion on that ?

I am using Access + JET OLEDB to connect.

http://support.microsoft.com/support/kb/articles/Q177/1/38.ASP

RR.

 

[chiph]

How many times can I BeginTrans on a Connection ?

Sounds like only once, unless you're using JET

This is also a common problem in MTS/COM+. What happens there is a programmer starts a transaction via MTS, and then calls code that explicitly starts an ADO transaction.

Chip H.