已经有一个与此Connection关联的开放DataReader，必须先关闭它 [英] There is already an open DataReader associated with this Connection which must be closed first
问题描述
我正在使用VB.NET 2003，SQL 2000和SqlDataReader。
当我从tblA读取数据时，我想填充tblB。我使用SQLDataReader来表示
两个表。我不使用线程。
当我在tblB上的ExecuteReader时，我得到错误已经有一个打开的
DataReader与此Connection相关联，必须先关闭它。
如何修复此错误？
对于每个DataReader，我是否要打开和关闭连接（在此
的情况下adoCon）避免这个错误？
谢谢。
m_cmdSQL =新的SqlClient.SqlCommand
使用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 =新的SqlClient.SqlCommand
使用m_cmdSQL2
.Connection = adoCon
.CommandText = sSQL
结束
m_drSQL2 = m_cmdSQL2.ExecuteReader（）---错误：已经有
与此Connection相关联的开放DataReader必须先关闭
。
rs.CloseRS（）
rs = Nothing
循环
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
推荐答案
fniles写道：
fniles wrote:
Do while m_drSQL.Read
sSQL =" insert into tblB（Account，name，Company）"
sSQL = sSQL&am磷; （VALUES（''"& m_drSQL.Item（" Account"）&"''"）
sSQL = sSQL&（"，''" & m_drSQL.Item（" Name"）&"''"）
sSQL = sSQL&（"，''"& m_drSQL.Item（" company"） ;）&"''）"）
m_cmdSQL2 =新的SqlClient.SqlCommand
使用m_cmdSQL2
.Connection = adoCon
.CommandText = sSQL
结束
m_drSQL2 = m_cmdSQL2.ExecuteReader（）---错误：已经有
与此Connection相关联的开放DataReader必须先关闭
。
rs.CloseRS（）
rs = Nothing
循环
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
在INSERT语句上调用ExecuteReader是没有意义的。
ExecuteReader用于检索数据进入DataReader。要执行你的INSERT语句，请执行你的INSERT语句，调用ExecuteNonQuery。
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.
谢谢大家。
我将它从ExecuteReader更改为ExecuteNonQuery，但是没有打开另外的数据库连接，我仍然得到错误已经有一个打开的
DataReader与此Connection相关联，必须是首先关闭。
请确认，如果以下内容在打开另一个方面看起来是正确的
数据库连接：
m_cmdSQL =新的SqlClient.SqlCommand
使用m_cmdSQL
.Connection = adoCon
.CommandText =" SELECT * FROM tblA"
结束
m_drSQL = m_cmdSQL.ExecuteReader（）
''------------打开另一个数据库连接---- ------------
DBCon =新的SqlClient.SqlConnection
使用DBCon
.ConnectionString = DB_Path
。打开（）
结束
''------------------- ---------
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 =新的SqlClient.SqlCommand
使用m_cmdSQL2
.Connection = DBCon - - 使用第二个DB连接
.CommandText = sSQL
结束
m_cmdSQL2.ExecuteQuery（）
rs.CloseRS（）
rs =没什么
循环
''------------关闭2ND DB CONNECTION ----------------
如果不是DBConIs则没有那么
如果DBConn.ConnectionString<""然后
DBConn.Close（）
DBConn =没什么
结束如果
结束如果
''-------------------------------------
" Chris Dunaway" < du ****** @ gmail.com写信息
新闻：11 ********************* @ j72g2000cwa。 googlegro ups.com ...
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写道：
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。项目（名称）&"''"）
sSQL = sSQL&（"，''"& m_drSQL.Item（" company"）&"'' ）"）
m_cmdSQL2 =新的SqlClient.SqlCommand
使用m_cmdSQL2
。Connect = adoCon
.CommandText = sSQL
结束
m_drSQL2 = m_cmdSQL2。 ExecuteReader（）---错误：已经
已经打开与此Connection相关的DataReader，必须首先关闭它。
rs.CloseRS（）
rs = Nothing
循环
>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
在INSERT语句上调用ExecuteReader是没有意义的。
ExecuteReader用于检索数据到一个DataReader。要执行你的INSERT语句，请执行你的INSERT语句，调用ExecuteNonQuery。
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.
一般情况下，你不能有两个打开的查询来自您的应用程序的相同SQL Server数据库
。即使您使用不同的连接，ADO.NET也可能是b $ b，可能正在进行连接池/共享。如果你绝对必须同时打开
查询（或者你需要打开一个查询，同时向同一个数据库发出
NonQuery更新），有两种选择。
1）将第一个查询加载到DataTable或DataSet对象中，而不是使用
a DataReader。这可能不是一个好主意，你从查询中返回大量的
数据。
2）启用MARS在您的SQL Server连接中。 （我认为MARS代表多个
活动结果集。）MARS是2005版SQL Server中的新功能，而
需要版本2.0。 NET Framework。其他数据库，例如Oracle，
支持类似MARS的功能，尽管名称不同。您可以在Visual Studio文档中找到有关MARS的
信息。基本上，你
为你的连接字符串添加一个额外的参数来启用它。
-----
Tim Patrick - www.timaki.com
从头到尾视觉Basic 2005
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
谢谢大家。
我将它从ExecuteReader更改为ExecuteNonQuery，但没有
开放
另一个数据库连接，我仍然收到错误已经打开
打开
与此连接关联的DataReader必须关闭
优先。
请确认，如果以下看起来正确无法打开
另一个
数据库连接：
m_cmdSQL =新的SqlClient.SqlCommand
使用m_cmdSQL
.Connection = adoCon
.CommandText =" SELECT * FROM tblA"
结束
m_drSQL = m_cmdSQL.ExecuteReader（）
'' ------------打开另一个数据库连接----------------
DBCon =新的SqlClient.SqlConnection
使用DBCon
.ConnectionString = DB_Path
。打开（）
结束
'' ----------------------------
do while m_drSQL.Read
sSQL ="插入tblB（帐户，名称，公司）"
sSQL = sSQL& （VALUES（''"& m_drSQL.Item（" Account"）&
"''"）
sSQL = sSQL& （"，''"& m_drSQL.Item（" Name"）&"''"）
sSQL = sSQL&（"，''"& ; m_drSQL.Item（" company"）&"''）"）
m_cmdSQL2 =新的SqlClient.SqlCommand
使用m_cmdSQL2
.Connection = DBCon ----使用第二个DB连接
.CommandText = sSQL
结束
m_cmdSQL2.ExecuteQuery（）
rs.CloseRS（）
rs =没什么
循环
''------- -----关闭第二个数据库连接----------------
如果不是DBConIs则没有任何结果
如果是DBConn。 ConnectionString<""然后
DBConn.Close（）
DBConn =没什么
结束如果
结束如果
''-------------------------------------
" Chris Dunaway" < du ****** @ gmail.com写信息
新闻：11 ********************* @ j72g2000cwa。 googlegro ups.com ...
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写道：
>fniles wrote:
>> Do While While m_drSQL.Read
sSQL =" insert into tblB（Account，name，Company）"
sSQL = sSQL& （VALUES（''"& m_drSQL.Item（" Account"）&"''"）
sSQL = sSQL&（"，''"& m_drSQL。项目（名称）&"''"）
sSQL = sSQL&（"，''"& m_drSQL.Item（" company"）&"'' ）"）
m_cmdSQL2 =新的SqlClient.SqlCommand
使用m_cmdSQL2
。Connect = adoCon
.CommandText = sSQL
结束
m_drSQL2 = m_cmdSQL2。 ExecuteReader（）---错误：已经
已经打开与此连接关联的DataReader，必须先关闭
rs.CloseRS（）< br => rs = Nothing
循环
>>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
在INSERT语句上调用ExecuteReader是没有意义的。
ExecuteReader旨在将数据检索到DataReader中。要执行INSERT语句，请调用ExecuteNonQuery。
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.
这篇关于已经有一个与此Connection关联的开放DataReader，必须先关闭它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
