vb.net 错误显示已与此命令关联的打开的 DataReader 必须先关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19023480/
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
error show on There is already an open DataReader associated with this Command which must be closed first
提问by user2655643
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim n As Integer = 0
str = "select Vote from vote where party='Green'"
cmd = New SqlCommand(str, con)
con.Open()
dr = cmd.ExecuteReader()
if dr.HasRows Then
dr.Read()
n = dr("Vote").ToString()
n = n + 1
Label8.Text = n.ToString()
End If
str = "update Vote set vote='" + n.ToString() + "' where party='Green'"
cmd = New SqlCommand(str, con)
cmd.ExecuteNonQuery()
dr.Read()
cmd.Dispose()
con.Close()
End Sub
回答by Steve
The message seems to be pretty clear. You have to close the DataReader before trying to use the connection for another operation
该消息似乎非常清楚。在尝试将连接用于其他操作之前，您必须关闭 DataReader
While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.
在使用SqlDataReader 的同时，关联的SqlConnection 正忙于为SqlDataReader 服务，除了关闭SqlConnection 之外，无法对SqlConnection 执行其他操作。在调用 SqlDataReader 的 Close 方法之前都是这种情况。例如，只有在调用 Close 之后才能检索输出参数。
....
dr.Close()
str = "update Vote set vote='" + n.ToString() + "' where party='Green'"
cmd = New SqlCommand(str, con)
cmd.ExecuteNonQuery()
' ??? dr.Read()
cmd.Dispose()
con.Close()
By the way, your whole code could be simplified using an ExecuteScalar to retrieve the last vote value and dumping the DataReader
顺便说一句，可以使用 ExecuteScalar 来简化您的整个代码以检索最后一个投票值并转储 DataReader
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim n As Integer = 0
str = "select Vote from vote where party='Green'"
Using cmd = New SqlCommand(str, con)
con.Open()
Dim n as Integer
Dim result = cmd.ExecuteScalar()
if result IsNot Nothing Then
n = Convert.ToInt32(result) + 1
else
n = 1
End If
Label8.Text = n.ToString()
str = "update Vote set vote='" + n.ToString() + "' where party='Green'"
cmd.CommandText = str
cmd.ExecuteNonQuery()
con.Close()
End Using
End Sub
In this scenario you use a global connection object and this should be avoided. Remember that it is always better to create, open, use and destroy the connection when you need to use it. SqlClient classes could use the Connection Poolinginfrastructure that could help a lot in managing precious resources like a connection
在这种情况下，您使用全局连接对象，应该避免这种情况。请记住，在需要使用连接时创建、打开、使用和销毁连接总是更好。SqlClient 类可以使用连接池基础结构，这在管理连接等宝贵资源方面有很大帮助
回答by Nadeem_MK
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
try
Dim n As Integer = 0
str = "select Vote from vote where party='Green'"
cmd = New SqlCommand(str, con)
con.Open()
dr.Close()
dr = cmd.ExecuteReader()
if dr.HasRows Then
dr.Read()
n = dr("Vote").ToString()
n = n + 1
Label8.Text = n.ToString()
End If
str = "update Vote set vote='" + n.ToString() + "' where party='Green'"
cmd = New SqlCommand(str, con)
cmd.ExecuteNonQuery()
dr.Read()
Finally
cmd.Dispose()
con.Close()
End Try
End Sub
回答by Steven Rotelli
Add multiple active results set to your connection string as shown below
将多个活动结果集添加到您的连接字符串，如下所示
Example:
例子：
"Server=Server;Database=MyDatabase;Trusted_Connection=True;MultipleActiveResultSets=true;"
