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

[fniles]

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 get the error "There is already an open
DataReader associated with this Connection which must be closed first."
How can I fix this error ?
For each DataReader, do I want to open and close the connection (in this
case adoCon) to avoid this error ?
Thank you.

m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = "SELECT * FROM tblA"
End With
m_drSQL = m_cmdSQL.ExecuteReader()
Do While m_drSQL.Read
sSQL = "insert into tblB (Account,name,Company)"
sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'")
sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'")
sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')")
m_cmdSQL2 = New SqlClient.SqlCommand
With m_cmdSQL2
.Connection = adoCon
.CommandText = sSQL
End With
m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is already
an open DataReader associated with this Connection which must be closed
first.
rs.CloseRS()
rs = Nothing
Loop

 

[Chris Dunaway]

Do While m_drSQL.Read
sSQL = "insert into tblB (Account,name,Company)"
sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'")
sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'")
sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')")
m_cmdSQL2 = New SqlClient.SqlCommand
With m_cmdSQL2
.Connection = adoCon
.CommandText = sSQL
End With
m_drSQL2 = m_cmdSQL2.ExecuteReader() ---> ERROR: There is already
an open DataReader associated with this Connection which must be closed
first.
rs.CloseRS()
rs = Nothing
Loop

It makes no sense to call ExecuteReader on an INSERT statement.
ExecuteReader is designed to retrieve data into a DataReader. To
execute your INSERT statement, call ExecuteNonQuery.

 

[fniles]

Thank you, all.
I changed it from ExecuteReader to ExecuteNonQuery, but without opening
another Database connection, I still get the error "There is already an open
DataReader associated with this Connection which must be closed first."

Please confirm, if the following looks correct in terms of opening another
Database connection:
m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = "SELECT * FROM tblA"
End With
m_drSQL = m_cmdSQL.ExecuteReader()
'------------ OPENING ANOTHER DB CONNECTION ----------------
DBCon= New SqlClient.SqlConnection
With DBCon
.ConnectionString = DB_Path
.Open()
End With
'----------------------------
Do While m_drSQL.Read
sSQL = "insert into tblB (Account,name,Company)"
sSQL = sSQL & (" VALUES ('" & m_drSQL.Item("Account") & "'")
sSQL = sSQL & (" ,'" & m_drSQL.Item("Name") & "'")
sSQL = sSQL & (" ,'" & m_drSQL.Item("company") & "')")
m_cmdSQL2 = New SqlClient.SqlCommand
With m_cmdSQL2
.Connection = DBCon ----> USE THE 2ND DB CONNECTION
.CommandText = sSQL
End With
m_cmdSQL2.ExecuteQuery()
rs.CloseRS()
rs = Nothing
Loop
'------------ CLOSING THE 2ND DB CONNECTION ----------------
If Not DBConIs Nothing Then
If DBConn.ConnectionString <> "" Then
DBConn.Close()
DBConn = Nothing
End If
End If
'-------------------------------------

 

[Tim Patrick]

In general, you cannot have two open queries to the same SQL Server database
from your application. Even if you use a different connection, ADO.NET is
probably doing connection pooling/sharing. If you absolutely must have both
queries open at once (or you need to have one query open while you issue
NonQuery updates to the same database), there are two alternatives.

1) Load the first query into a DataTable or DataSet object instead of using
a DataReader. This might not be a good idea of you have massive amounts of
data coming back from the query.

2) Enable "MARS" in your SQL Server connect. (I think MARS stands for "Multiple
Active Result Sets.") MARS is new in the 2005 version of SQL Server, and
requires version 2.0 of the .NET Framework. Other databases, such as Oracle,
support MARS-like features, although under a different name. You can find
information about MARS in the Visual Studio documentation. Basically, you
add an extra parameter to your connection string to enable it.

 

[fniles]

Thank you for your reply.

Even if you use a different connection, ADO.NET is probably doing
connection pooling/sharing.

That's what I am thinking, by opening the 2nd connection, it actually using
the connection pooling.
Is there anything bad with connection pooling ?

Thanks

 

[Tim Patrick]

It has its advantages and disadvantages. But whatever the benefits, you will
still probably need to adjust your code to compensate.

 

[Cor Ligthert [MVP]]

Fniles,

In VB.Net 2003 you can only use one connection at a time, therefore as you
wrote already, you have to close and open them when you need them. Which is
by the best way normal practise while this is as well good for the
connection pooling.

Cor

 

[Stephany Young]

Am I the only one who is thinking 'what the hell is he doing this for?'?

A much more efficient approach would be to get the Sql Server to do the
work.

As far as I can see it is as simple as:

Dim _con As New SqlConnection(<connection string>)

Dim _com As New SqlCommand("insert into tblB(Account,name,Company) select
Account,Name,company from tblA", _con

_con.Open()

_com.ExecuteNonQuery()

_con.Close()

But aside from that here is your code with the problem areas annotated:

' Turn both Option Strict and Option Explicit on

m_cmdSQL = New SqlCommand()
' variable used without being explicitly defined
'should be
Dim m_cmdSQL As New SqlCommand()

With m_cmdSQL
.Connection = adoCon
' adoCon is assumed to be a properly constructed SqlConnection object
.CommandText = "SELECT * FROM tblA"
End With

m_drSQL = m_cmdSQL.ExecuteReader()
' variable used without being explicitly defined
'should be
Dim m_drSQL As SqlDataReader = m_cmdSQL.ExecuteReader()

DBCon = New SqlConnection()
' variable used without being explicitly defined
'should be
Dim DBCon As New SqlConnection()

With DBCon
.ConnectionString = DB_Path
.Open()
End With

' the above can be better written as
Dim DBCon As New SqlConnection(adoCon.ConnectionString)
DBCon.Open()
' this way ensures that the connection string used for DBCon is the same as
the connectionstring used adoCon

' moved to here because the SqlCommand object need to be instantiated only
once
Dim m_cmdSQL2 As New SqlCommand("insert into tblB (Account,name,Company)
values(@account,@name,@company)", DBCon
' but this way some parameters are required and these need their values
populated on each iteration
m_cmdSQL2.Parameters.Add("@account")
m_cmdSQL2.Parameters.Add("@name")
m_cmdSQL2.Parameters.Add("@company")

' although Do While/Loop is effectively the same as While/End While, I
consider the While/End While to be more intuitive and easier to read
While m_drSQL.Read()
' supply the values for the parameters from the incoming values
m_cmdSQL2.Parameters("@account").Value = m_drSQL("Account")
m_cmdSQL2.Parameters("@name").Value = m_drSQL("Name")
m_cmdSQL2.Parameters("@company").Value = m_drSQL("company")
' execute the insert statement
m_cmdSQL2.ExecuteNonQuery()
End While

' close the connection that was opened
DBCon.Close()

' close the SqlDataReader object
drSQL.Close()

Now for the grumbly bit!

The number of typos and other syntactically incorrectly elements in the code
you posted indicates that the code you posted is not the code that you are
trying to run because it would never compile.

In future, if you are going to post a code fragment, the copy it and paste
it verbatiom from your IDE. Once you do so, it might look like rubbish, so
select the text in question, select Format/Rich Text (HTML) from the menu in
Outlook Express and then select Format/Plain Text.

 

[RobinS]

Not *that's* a handy tip. I usually paste it into a text editor
and then copy and paste it into OE, which fixes the readability
problem too.

Thanks!
Robin S.

 

[William \(Bill\) Vaughn]

Thanks, I hate to be one of the few that ask that all-important
question--why are you doing that?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[dbahooker]

yeah sorry

they 'supposedly fixed it in SQL 2005' its called MARS and it's a new
feature that we can't live without

OF COURSE IT DOESNT WORK

go back to classic ADO; ADO.net is the worst library ever.. I would
rather use DAO and i --HATE-- DAO

-Aaron

 

[dbahooker]

you can only use one DATAREADER per connection

either open a 2nd connection or close the 1st datareader

in ADO it works just perfectly

but MS had to break everything and convince us to rewrite everything so
you're ****ed

-Aaron

 

[Cor Ligthert [MVP]]

Kerry,

Per client program of course. The server can use much more connections
althoug that seems to be mostly be set to 100 maximum.

Cor

 

[Stephany Young]

That's not correct Cor. You can instantiate and open as many concurrent
connection objects as you want in a single application and, in .NET, this
has been the case since the .Net Framework 1.0.

The only thing that you're limited by is available resources whether thay be
at the client end or the server end.

 

[Master Programmer]

Sorry, the ADO.NET dataReader is crippled and you cannot have more than
one dataReader open for a single connection. Stupid but true !

Thats just one of the disadvantages of ADO.NET.

Thats what happened when MS stupidly decided to make the underlying
structure of ADO.NET XML based.

The Grand Master

 

[master.programmer]

My bad, I take that back. Forgive my turrets.

 

[Cor Ligthert [MVP]]

Stephany,

Maybe I have understood it wrong, I always thought that it was only possible
to work with one open and active connection at a time. (You can of course
have thousands connections if you like).

Maybe was VENUS already there before MARS.



Cor

 

[aaron.kempf]

it SHOULD be fixed now-- they sold us on that feature

but it doesn't WORK

it works PERFECTLY in the old ADO.. forward only firehouse was plenty
fast for me; I ****ing hate the overhead of ADO.net personally

I only ever use DataReaders; because datasets are just ridiculous I
think

-Aaron

 

[Cor Ligthert [MVP]]

Stephany and Kerry,

I tested it this morning, you both are right, therefore I learned again
something in this newsgroups.

Thanks both,

Cor

 

[William \(Bill\) Vaughn]

The power is still out here in parts of Redmond so please pardon my
prolonged absence (since Thursday last week)...

While some providers (not ADO.NET) do support multiple operations on a
single connection (like Oracle), they do so because (for the most part)
their connections are so complex. SQL Server (and others have far simpler
(and cheaper) connection classes that can only support a single operation at
a time. This means it often makes sense for applications to open two or more
connections to the server to handle independent operations.

Ok, that said, whenever I see someone trying to open more than one
connection I start to look more closely at the problem they're trying to
solve. In many (too many) cases, the operation should have been done on the
server--not on the client or with bulk copy.

That's why I asked, is this trip really necessary...