已经有一个与此连接关联的打开的 DataReader 必须先关闭 VB.NET
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15218671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
There is already an open DataReader associated with this Connection which must be closed first VB.NET
提问by imsome1
I get this error message
我收到此错误消息
"There is already an open DataReader associated with this Connection which must be closed first"
“已经有一个与此连接关联的打开的 DataReader，必须先关闭它”
Please help me
My code is:
请帮帮我
我的代码是：
Public Sub update_qty(ByVal qry1 As String)
Dim dr As MySqlDataReader 'SQLiteDataReader
Dim comm As MySqlCommand 'SQLiteCommand
Try
comm = New MySqlCommand(qry1, conn)
dr = comm.ExecuteReader()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Do While dr.Read()
exe_query("call cargosys.paymentsAdd('" & var1 & "', " & dr("inNo") & ")")
Loop
dr.Close()
End Sub
Public Sub exe_query(ByVal qry As String) As String
Dim cmd As MySqlCommand
Try
cmd = New MySqlCommand(qry, conn)
cmd.ExecuteNonQuery()
Catch ex As MySqlException
MessageBox.Show(ex.ToString)
End Try
End Sub
回答by Iswanto San
Your problem is that your code open a DataReaderand then execute the SqlCommandwhen the DataReaderread
您的问题是您的代码打开一个DataReader然后SqlCommand在DataReader读取时执行
Try to change this line:
尝试更改此行：
dr = comm.ExecuteReader()
to:
到：
dr = comm.ExecuteReader(CommandBehavior.CloseConnection)
More: DataReader CommandBehavior
Or change your connection string to enable MARS (Multiple Active Result Sets). This setting will allow for the retrieval of multiple forward-only, read-only result sets on the same connection.
或者更改您的连接字符串以启用 MARS（多个活动结果集）。此设置将允许在同一连接上检索多个只进、只读结果集。
For example :
例如 ：
connectionString=
"Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|Northwind.MDF;
Integrated Security=True;
User Instance=True;
MultipleActiveResultSets=True"
More: MARS
更多：火星
EDIT
编辑
Since MARS keyword is not supported, try to change your code to this:
由于不支持 MARS 关键字，请尝试将代码更改为：
Public Sub update_qty(ByVal qry1 As String)
Dim dr As MySqlDataReader 'SQLiteDataReader
Dim comm As MySqlCommand 'SQLiteCommand
Try
comm = New MySqlCommand(qry1, conn)
dr = comm.ExecuteReader()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Dim myList As New List(Of String)
Do While dr.Read()
myList.Add("call cargosys.paymentsAdd('" & var1 & "', " & dr("inNo") & ")")
Loop
dr.Close()
End Sub
Public Sub exe_query(myList As List(Of String))
Dim cmd As MySqlCommand
For Each query As String In myList
Try
cmd = New MySqlCommand(query, conn)
cmd.ExecuteNonQuery()
Catch ex As MySqlException
MessageBox.Show(ex.ToString)
End Try
Next
End Sub
Instead to doing DataReader.Read->SqlCommand.ExecuteNonQuerysimultaneously, this code will be read all the data first and then run SqlCommand.ExecuteNonQuery.
不是同时执行DataReader.Read-> SqlCommand.ExecuteNonQuery，此代码将首先读取所有数据然后运行SqlCommand.ExecuteNonQuery。
