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]

fniles wrote:

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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...

fniles wrote:

>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.

[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.

-----
Tim Patrick - www.timaki.com
Start-to-Finish Visual Basic 2005

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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...

>fniles wrote:

>>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 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" <in*****@invalid.com.invalidwrote in message
news:e3*************************@newsgroups.comcas t.net...

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.

-----
Tim Patrick - www.timaki.com
Start-to-Finish Visual Basic 2005

>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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegr oups.com...

>>fniles wrote:

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.

[Tim Patrick]

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

-----
Tim Patrick - www.timaki.com
Start-to-Finish Visual Basic 2005

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" <in*****@invalid.com.invalidwrote in message
news:e3*************************@newsgroups.comcas t.net...

>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.

[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

"fniles" <fn****@pfmail.comschreef in bericht
news:%2****************@TK2MSFTNGP02.phx.gbl...

>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

[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.

"fniles" <fn****@pfmail.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...

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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...

>fniles wrote:

>>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.

[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.
---------------------------------------
"Stephany Young" <noone@localhostwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

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.

[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)
-----------------------------------------------------------------------------------------------------------------------

"Stephany Young" <noone@localhostwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

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.

"fniles" <fn****@pfmail.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...

>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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegr oups.com...

>>fniles wrote:

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.

[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
fniles wrote:

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

[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 fucked

-Aaron
Cor Ligthert [MVP] wrote:

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

"fniles" <fn****@pfmail.comschreef in bericht
news:%2****************@TK2MSFTNGP02.phx.gbl...

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

[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

"Kerry Moorman" <Ke**********@discussions.microsoft.comschreef in bericht
news:9D**********************************@microsof t.com...

Cor,

I'm confused as to what you mean by "In VB.Net 2003 you can only use one
connection at a time".

Can you elaborate?

Kerry Moorman
"Cor Ligthert [MVP]" wrote:

>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

"fniles" <fn****@pfmail.comschreef in bericht
news:%2****************@TK2MSFTNGP02.phx.gbl...

>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

[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.
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:ua*************@TK2MSFTNGP02.phx.gbl...

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

"Kerry Moorman" <Ke**********@discussions.microsoft.comschreef in
bericht news:9D**********************************@microsof t.com...

>Cor,

I'm confused as to what you mean by "In VB.Net 2003 you can only use one
connection at a time".

Can you elaborate?

Kerry Moorman
"Cor Ligthert [MVP]" wrote:

>>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

"fniles" <fn****@pfmail.comschreef in bericht
news:%2****************@TK2MSFTNGP02.phx.gbl.. .
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

[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
William (Bill) Vaughn wrote:

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)
-----------------------------------------------------------------------------------------------------------------------

"Stephany Young" <noone@localhostwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

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.

"fniles" <fn****@pfmail.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...

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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
fniles wrote:

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.

[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

"Stephany Young" <noone@localhostschreef in bericht
news:Oc*************@TK2MSFTNGP04.phx.gbl...

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.
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:ua*************@TK2MSFTNGP02.phx.gbl...

>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

"Kerry Moorman" <Ke**********@discussions.microsoft.comschreef in
bericht news:9D**********************************@microsof t.com...

>>Cor,

I'm confused as to what you mean by "In VB.Net 2003 you can only use one
connection at a time".

Can you elaborate?

Kerry Moorman
"Cor Ligthert [MVP]" wrote:

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

"fniles" <fn****@pfmail.comschreef in bericht
news:%2****************@TK2MSFTNGP02.phx.gbl. ..
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

[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 fucking hate the overhead of ADO.net personally

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

-Aaron
Kerry Moorman wrote:

Cor,

Yes, you have misunderstood that.

You can have as many open and active connections in your client that the
server will allow.

Many times you absolutely need to work with more than one active connection
at a time. An example is to deal with the original question in this series:
to be able to process two datareaders at the same time. Each datareader
requires its own connection, at least pre-.Net 2005. I'm not sure if that is
still the case.

Kerry Moorman
"Cor Ligthert [MVP]" wrote:

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

"Stephany Young" <noone@localhostschreef in bericht
news:Oc*************@TK2MSFTNGP04.phx.gbl...

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.
>
>
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:ua*************@TK2MSFTNGP02.phx.gbl...
>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
>>
>"Kerry Moorman" <Ke**********@discussions.microsoft.comschreef in
>bericht news:9D**********************************@microsof t.com...
>>Cor,
>>>
>>I'm confused as to what you mean by "In VB.Net 2003 you can only use one
>>connection at a time".
>>>
>>Can you elaborate?
>>>
>>Kerry Moorman
>>>
>>>
>>"Cor Ligthert [MVP]" wrote:
>>>
>>>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
>>>>
>>>"fniles" <fn****@pfmail.comschreef in bericht
>>>news:%2****************@TK2MSFTNGP02.phx.gbl. ..
>>>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
>>>
>>>
>>>
>>>
>>>>
>>>>
>>>>
>>
>>
>
>

[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

"Kerry Moorman" <Ke**********@discussions.microsoft.comschreef in bericht
news:A4**********************************@microsof t.com...

Cor,

Yes, you have misunderstood that.

You can have as many open and active connections in your client that the
server will allow.

Many times you absolutely need to work with more than one active
connection
at a time. An example is to deal with the original question in this
series:
to be able to process two datareaders at the same time. Each datareader
requires its own connection, at least pre-.Net 2005. I'm not sure if that
is
still the case.

Kerry Moorman
"Cor Ligthert [MVP]" wrote:

>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

"Stephany Young" <noone@localhostschreef in bericht
news:Oc*************@TK2MSFTNGP04.phx.gbl...

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.
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:ua*************@TK2MSFTNGP02.phx.gbl...
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

"Kerry Moorman" <Ke**********@discussions.microsoft.comschreef in
bericht news:9D**********************************@microsof t.com...
Cor,

I'm confused as to what you mean by "In VB.Net 2003 you can only use
one
connection at a time".

Can you elaborate?

Kerry Moorman
"Cor Ligthert [MVP]" wrote:

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

"fniles" <fn****@pfmail.comschreef in bericht
news:%2****************@TK2MSFTNGP02.phx.gbl. ..
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

[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...

"Master Programmer" <ma***************@outgun.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

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
William (Bill) Vaughn wrote:

>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)
-----------------------------------------------------------------------------------------------------------------------

"Stephany Young" <noone@localhostwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...

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.

"fniles" <fn****@pfmail.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...
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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegr oups.com...
fniles wrote:

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.

[aaron.kempf]

your ** new data model that has failed in the marketplace ** (ADO.NET)
didn't need to be different than classic ADO.

it should have been the same; and it should be compatabile syntax.
changing the diameter of your houses' plumbing, after you're moved in--
it's not fun-- it's just called INEFFICIENT

there was nothing wrong with the old model.

I'm so sorry that those dipshits in Redmond thought that WE HAD TROUBLE
REMEMBERING TO rst.moveNext

that's fucking laughable; maybe they should stop trying to train retard
Indians and Chinese; and hire someone with a clue

-Aaron
William (Bill) Vaughn wrote:

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...

"Master Programmer" <ma***************@outgun.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

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
William (Bill) Vaughn wrote:

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)
-----------------------------------------------------------------------------------------------------------------------

"Stephany Young" <noone@localhostwrote in message
news:eb**************@TK2MSFTNGP03.phx.gbl...
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.

"fniles" <fn****@pfmail.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...
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
'-------------------------------------

"Chris Dunaway" <du******@gmail.comwrote in message
news:11*********************@j72g2000cwa.googlegro ups.com...
fniles wrote:

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.