vb.net 错误“已经有一个与此命令关联的打开的 DataReader，必须先关闭它。”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28274348/
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 'There is already an open DataReader associated with this Command which must be closed first.'
提问by Pradnya Bolli
I use below code but it gives error on sentence icount = cmd.ExecuteNonQuery
我使用下面的代码，但它在句子 icount = cmd.ExecuteNonQuery 上给出错误
cn.Open()
str = "SELECT [srno],[caste]FROM [SchoolERP].[dbo].[caste] where (caste ='" + (TextBox1.Text) + "')"
cmd = New SqlCommand(str, cn)
dr1 = cmd.ExecuteReader()
If Not dr1.HasRows Then
str = "INSERT INTO [SchoolERP].[dbo].[caste]([caste])VALUES('" + TextBox1.Text + "')"
cmd = New SqlCommand(str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
Else
MsgBox("Record Exists")
cn.Dispose()
End If
cn.Close()
回答by Disha
Try always calling the Close method when you have finished using the DataReader object.
使用完 DataReader 对象后，请尝试始终调用 Close 方法。
dr1.Close();
Another optionis to turn on MARS , in your connection stringjust add "MultipleActiveResultSets=True;"
另一种选择是打开 MARS ，在您的连接字符串中添加"MultipleActiveResultSets=True;"
回答by Mak
Try this:
尝试这个：
Try
insert data in combobox
cmd.Connection = con
cmd.CommandText = "select name from party"
dr = cmd.ExecuteReader()
cb_ms.Items.Add("---Select---")
cb_ms.SelectedIndex = 0
While dr.Read() 'get error here.'
cb_ms.Items.Add(dr("name"))
End While
dr.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
回答by Pradnya Bolli
I got Answer ... i only close connection before icount = cmd.ExecuteNonQuerythis sentence and again open connection...and its works..
thanks disha..
我得到了答案......我只在icount = cmd.ExecuteNonQuery这句话之前关闭连接并再次打开连接......及其作品......谢谢disha......
回答by Joel Coehoorn
You could just close the datareader before the 2nd query, but it's better still to get this all into a single query in the first place. Moreover, you reallyneed to close that awful sql injection issue.
您可以在第二个查询之前关闭数据读取器，但最好首先将所有这些都放入一个查询中。此外，您确实需要关闭那个可怕的 sql 注入问题。
Try this to fix both issues:
试试这个来解决这两个问题：
Dim sql As String = _
"IF NOT EXISTS
(
SELECT 1 FROM [SchoolERP].[dbo].[caste] where (caste = @caste )
)
BEGIN
INSERT INTO [SchoolERP].[dbo].[caste]([caste])VALUES( @caste)
END"
Using cn As New SqlConnection("connection string here"), _
cmd As New SqlCommand(sql, cn)
'Use actual columnn type from the database here
cmd.Parameters.Add("@caste", SqlDbType.NVarChar, 50).Value = TextBox1.Text
cn.Open()
icount = cmd.ExecuteNonQuery()
MessageBox.Show(icount)
End Using
回答by dran1979
Use Try.. Catch.. Finally... End Try somehing like this:
使用 Try..Catch..Finally...End 尝试这样的事情：
Try
cn.Open()
str = "SELECT [srno],[caste]FROM [SchoolERP].[dbo].[caste] where (caste ='" + (TextBox1.Text) + "')"
cmd = New SqlCommand(str, cn)
dr1 = cmd.ExecuteReader()
If Not dr1.HasRows Then
str = "INSERT INTO [SchoolERP].[dbo].[caste]([caste])VALUES('" + TextBox1.Text + "')"
cmd = New SqlCommand(str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
Else
MsgBox("Record Exists")
cn.Dispose()
End If
cn.Close()
Catch error As Exception
........
Finally
dr1.Close
End Try
