ExecuteScalar closed my connection?

Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.sql