473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

There is already an open DataReader associated with this Command which must be closed first.

I've a TransactionScope in which I select some data and I want to do some
queries for each record retrieved with my select.
I'm using a DataReader and for each record I do factory.CreateCommand() and
then I execute the command, but I get the following exception message:
" There is already an open DataReader associated with this Command which
must be closed first. "
Searching on google I've found I've to create another connection, but when I
open the second connection I get this exception message:
" Failure while attempting to promote transaction "

I've found this explanation on the internet.

When the connection enlists during the Open call, since there are no other
DB transactions associated with the System transaction, the connection
becomes the root and tries to handle the work as a local transaction (in
System.Transactions parlance, this is a Lightweight Transaction).
When the second connection enlists, the first transaction must be promoted
to DTC, but the connection is busy with the reader, so fails.
To work around the issue, either ensure the initial transaction is promoted
to a DTC transaction up front, or make sure the initial connection can talk
to the server when the transaction does get promoted.
I've opened both the connections just before doing anything so nowit works.
It would be better to use a datatable to retrieve the records and then loop
on them (I do not know if a distributed transaction is very expensive or
not)?
Thanks,
Luigi.
Jun 7 '07 #1
3 13167
What DBRMS? Since using LTM, presumably SQL2005? In which case you
could enable "MARS" (via the connection string) which should allow it
to execute the second command on the existing connection, inside the
ambient transaction scope, without elevating above LTM.

Worth a shot...

Marc
Jun 7 '07 #2
"factory.CreateCommand()" means I do not want to use DBMS specific
workarounds.
Thanks,
Luigi.
Jun 7 '07 #3
Does anything else support promoteable transactions? (I honestly don't
know - I am pretty-much constrained to SqlServer). In which case, the
LTM (vs DTC) behavior is RDBMS-specific already...

?

Marc
Jun 7 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Grant | last post by:
Hi, I keep getting the following error message: InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first. I have read up...
20
by: fniles | last post by:
I am using VB.NET 2003, SQL 2000, and SqlDataReader. As I read data from tblA, I want to populate tblB. I use SQLDataReader for both tables. I do not use thread. When I ExecuteReader on tblB, I...
10
by: jimmy | last post by:
Hi again, sorry for posting two questions so close together but im working on a school project which is due in soon and running into some difficulties implementing the database parts. I have the...
13
by: Bart | last post by:
Hi, i get the error: "There is already an open DataReader associated with this Command which must be closed first" Thanks Bart ----------------------------------------- Imports...
11
by: =?Utf-8?B?QXNhZg==?= | last post by:
Hello, I have two Table Adapters that I am storing in a Cache using .NET 2.0 Web Service and the Database is SQL Server on Windows 2003 IIS6. Each of the two table adapters has its own...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.