已经有一个打开的datareader与此命令必须首先关闭 [英] There is already an open datareader with this command which must be closed first
问题描述
嗨再次，
抱歉发布了两个如此接近的问题，但是我正在研究一个
的学校项目，该项目即将到期并遇到一些问题困难
实现数据库部分。我有下面的代码当
执行时会生成以下错误消息：''已经有一个
open datareader with this command必须先关闭''br $>
Private Sub MainMenu_Load（ByVal sender As System.Object，ByVal e As
System.EventArgs）Handles MyBase.Load
''声明新的连接变量
Dim cnn As SqlConnection
''声明字符串以保存SQL预订查询
Dim sqlreservation As String
' '声明命令执行查询
Dim cmdMain As SqlCommand
''声明数据读取器以保存查询结果
Dim dr As SqlDataReader
''将insteger声明为循环计数器
Dim i As Integer = 0
''声明字符串以保留切割的主题
昏暗主题作为字符串
''声明数组以保存切割ID
昏暗的cids（）作为字符串
''向临时主义者声明arraylist ily hold ids
Dim list As ArrayList = New ArrayList（）
''将容器从屏幕顶部放置100像素
MenuContainer.Top = Me.Height - （Me.Height - 100）
''将容器放在屏幕的水平中心
MenuContainer.Left = Me.Width / 2 - （MenuContainer.Width / 2）
''实例化连接对象
cnn =新的SqlConnection
' '尝试阻止错误
尝试
''将连接连接字符串属性设置为全局cnn
varialbe
cnn.ConnectionString = My.Settings.cnn
''尝试打开连接
cnn.Open（）
Catch ex作为例外
''通过显示消息框处理异常（如果有的话）
有异常细节
MsgBox（&试图连接到你的
SQL Server + Environment.NewLine + ex.Message，MsgBoxStyle.Exclamation
+ MsgBoxStyle.OkOnly，Cutting Library）
''停止执行此代码的其余部分
退出Sub
结束尝试
''设置查询以查找是否存在此用户的预订
sqlreservation =" SELECT CuttingID FROM CuttingReservation
WHERE StudentID ='''1000''"
''实例化命令
cmdMain =新的SqlCommand
''设置try块来捕获错误
尝试
''设置连接以获取命令使用
cmdMain.Connection = cnn
''设置命令文本
cmdMain.CommandText = sqlreservation
''执行命令并将结果存储在数据读取器中
dr = cmdMain.ExecuteReader（）
''添加数据读取器的内容到数组列表
当dr.Read（）
list.Add（dr.GetValue（0）.ToString（） ）
结束时
''调整数组大小并将arraylist的内容复制到
数组
ReDim cids（list.Count - 1）
list.CopyTo（cids）
Catch ex As Exception
''向用户ecxplaining错误显示错误消息
MsgBox（"查询数据库时发生错误。
+ ex.Message，MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly，Cutting
Library）
''停止进一步执行代码
退出Sub
结束尝试
尝试
''迭代所有ID数组
For each id As String in cids
''设置sqlreservation查询以查找每个的状态
cut
sqlreservation =" SELECT主题从切割WHERE
CuttingID =''" + id +"''AND Status =''True''"
''将命令文本设置为新查询
cmdMain.CommandText = sqlreservation
''设置连接属性
cmdMain.Connection = cnn
''执行查询并捕获结果datareader
dr = cmdMain.ExecuteReader（）
当dr.Read（）
''显示消息框告诉用户切割是
avaialble
MsgBox（dr（0）.ToString（））
MsgBox（"以下保留的剪切现在是
可用：" + dr.GetValue（0）.ToString（），MsgBoxStyle.Information +
MsgBoxStyle.OkOnly，Cutting Library）
结束时
下一页
Catch ex As Exception
MsgBox（" failed" + ex.Message）
结束尝试
结束子
对于大量的代码很抱歉但没有发布它的所有
可能很难得到任何想法我在做什么
提前致谢
詹姆斯
Hi again,
sorry for posting two questions so close together but im working on a
school project which is due in soon and running into some difficulties
implementing the database parts. I have the code below which when
executed generates the following error message: ''There is already an
open datareader with this command which must be closed first''
Private Sub MainMenu_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
''Declare new connection variable
Dim cnn As SqlConnection
''Declare string to hold SQL reservation query
Dim sqlreservation As String
''Declare command to execute query
Dim cmdMain As SqlCommand
''Declare data reader to hold query results
Dim dr As SqlDataReader
''Declare insteger as loop counter
Dim i As Integer = 0
''Declare string to hold topic of reserved cutting
Dim topic As String
''Declare array to hold cutting ids
Dim cids() As String
''Declare arraylist to temporarily hold ids
Dim list As ArrayList = New ArrayList()
''Place container 100 pixels from top of screen
MenuContainer.Top = Me.Height - (Me.Height - 100)
''Place container in horizontal centre of screen
MenuContainer.Left = Me.Width / 2 - (MenuContainer.Width / 2)
''Instantiate the conection object
cnn = New SqlConnection
''Try block to catch errors
Try
''Set connections connection string property to global cnn
varialbe
cnn.ConnectionString = My.Settings.cnn
''Try to open connection
cnn.Open()
Catch ex As Exception
''Deal with exceptions if any occur by showing a message box
with exception details
MsgBox("An error occurred whilst trying to connect to your
SQL Server" + Environment.NewLine + ex.Message, MsgBoxStyle.Exclamation
+ MsgBoxStyle.OkOnly, "Cutting Library")
''Stop exucting rest of this code
Exit Sub
End Try
''Set up query to find if any reservations for this user exist
sqlreservation = "SELECT CuttingID FROM CuttingReservation
WHERE StudentID = ''1000''"
''Instantiate command
cmdMain = New SqlCommand
''Set up try block to catch errors
Try
''Set connection for command to use
cmdMain.Connection = cnn
''Set comand text
cmdMain.CommandText = sqlreservation
''Execute command and store results in data reader
dr = cmdMain.ExecuteReader()
''Add contents of data reader to array list
While dr.Read()
list.Add(dr.GetValue(0).ToString())
End While
''Resize the array and copy the arraylist''s contents to
array
ReDim cids(list.Count - 1)
list.CopyTo(cids)
Catch ex As Exception
''Display error message to user ecxplaining error
MsgBox("An error occurred whilst querrying the database. "
+ ex.Message, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Cutting
Library")
''stop further executiong of code
Exit Sub
End Try
Try
''Iterate through all ids in array
For Each id As String In cids
''Set sqlreservation query to find status of each
cutting
sqlreservation = "SELECT Topic FROM Cutting WHERE
CuttingID = ''" + id + "'' AND Status = ''True''"
''Set command text to new query
cmdMain.CommandText = sqlreservation
''Set connection property
cmdMain.Connection = cnn
''execute query and catch results in datareader
dr = cmdMain.ExecuteReader()
While dr.Read()
''display message box telling user cutting is
avaialble
MsgBox(dr(0).ToString())
MsgBox("The following reserved cutting is now
available: " + dr.GetValue(0).ToString(), MsgBoxStyle.Information +
MsgBoxStyle.OkOnly, "Cutting Library")
End While
Next
Catch ex As Exception
MsgBox("failed " + ex.Message)
End Try
End Sub
Sorry for the large amount of code but without posting it all its
probably hard to get any idea as to what im doing
Thanks in advance
James
推荐答案
你知道在新组上发布作业问题是不行（imo）
对吗？如果我们现在给你答案，当你在工作期间遇到
时你会怎么做？
无论如何，我还是会帮助你的在这一个。你是错误信息
告诉'你确切需要发生什么 - 你需要关闭
datareader！所以拉起对象浏览器（F2我相信）并搜索
SqlDataReader并浏览可用的方法。人们应该坚持使用关闭作为关闭的方式。 datareader ......
此外，在发布之前，您应该始终查看提供的帮助和对象浏览器的
。然后我建议搜索msdn.com然后
这个新闻组的档案
http://groups.google.com/group/micro.../topics?lnk=sg 。
大部分时间都有类似的问题被提出/回答。
谢谢，
Seth Rowe
jimmy写道：
You do know it''s a no-no (imo) to post homework questions on newgroups
right? If we give you the answers now, what will you do when you get
stumped during a job?
Irregardless, I''ll still help you out on this one. You''re error message
tell''s you exactly what needs to happen - you need to close the
datareader! So pull up the object browser (F2 I believe) and search for
SqlDataReader and browse through the available methods. One should
stick out as a way to "Close" the datareader......
Also, before posting you should always check out the provided help and
the object browser. Then I would recommend searching msdn.com and then
the archives of this newsgroup at
http://groups.google.com/group/micro.../topics?lnk=sg.
Most of the time a similar question has been asked/answered.
Thanks,
Seth Rowe
jimmy wrote:
嗨再次，
抱歉发布两个问题如此接近但我即时通讯在一个即将到期的学校项目上工作并遇到一些困难
实施数据库部分。我有下面的代码当
执行时会生成以下错误消息：''已经有一个
open datareader with this command必须先关闭''br />
Private Sub MainMenu_Load（ByVal sender As System.Object，ByVal e As
System.EventArgs）处理MyBase.Load
''声明新的连接变量
Dim cnn As SqlConnection
''声明字符串以保存SQL预留查询
Dim sqlreservation As String
''声明执行查询的命令
Dim cmdMain As SqlCommand
''声明数据读取器以保存查询结果
Dim dr As SqlDataReader
''将insteger声明为循环计数器
Dim i As Integer = 0
''声明字符串以保留切割的主题
昏暗话题为字符串
'声明数组以保存切割ID
Dim cids（）As String
''声明arraylist暂时保存id
Dim list As ArrayList = New ArrayList（）
''将容器放置在屏幕顶部100像素
MenuContainer.Top = Me.Height - （Me.Height - 100）
''将容器放在屏幕的水平中心
MenuContainer.Left = Me.Width / 2 - （MenuContainer.Width / 2）
''实例化连接对象
cnn =新的SqlConnection
''尝试阻止捕获错误
试试
''将连接连接字符串属性设置为全局cnn
varialbe
cnn.ConnectionString = My.Settings.cnn
''尝试打开连接
cnn.Open（）
Catch ex As Exception
''通过显示消息框来处理异常（如果有的话）
有异常详细信息
MsgBox（尝试连接到您的
$时出错b $ b SQL Server + Environment.NewLine + ex.Message，MsgBoxStyle.Exclamation
+ MsgBoxStyle.OkOnly，Cutting Library）
''停止执行此代码的其余部分
退出Sub
结束尝试
''设置查询以查找是否存在此用户的预订
sqlreservation =" SELECT CuttingID FROM CuttingReservation
WHERE StudentID ='''1000''"
''实例化命令
cmdMain =新的SqlCommand
''设置try块来捕获错误
尝试
''设置连接以获取命令使用
cmdMain.Connection = cnn
''设置命令文本
cmdMain.CommandText = sqlreservation
''执行命令并将结果存储在数据读取器中
dr = cmdMain.Execute读者（）
''将数据读取器的内容添加到数组列表
当dr.Read（）
list.Add （Dr.GetValue（0）.ToString（））
结束时
''调整阵列大小并将arraylist的内容复制到
数组
ReDim cids（list.Count - 1）
list.CopyTo（cids）
Catch ex As Exception
''向用户ecxplaining错误显示错误消息
MsgBox（"查询数据库时发生错误。
+ ex.Message，MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly，Cutting
Library）
''停止进一步执行代码
退出Sub
结束尝试
尝试
''迭代所有ID数组
For each id As String in cids
''设置sqlreservation查询以查找每个的状态
cut
sqlreservation =" SELECT主题从切割WHERE
CuttingID =''" + id +"''AND Status =''True''"
''将命令文本设置为新查询
cmdMain.CommandText = sqlreservation
''设置连接属性
cmdMain.Connection = cnn
''执行查询并捕获结果datareader
dr = cmdMain.ExecuteReader（）
当dr.Read（）
''显示消息框告诉用户切割是
avaialble
MsgBox（dr（0）.ToString（））
MsgBox（"以下保留的剪切现在是
可用：" + dr.GetValue（0）.ToString（），MsgBoxStyle.Information +
MsgBoxStyle.OkOnly，Cutting Library）
结束时
下一页
Catch ex As Exception
MsgBox（失败） + ex.Message）
结束尝试
结束子
对于大量的代码很抱歉但没有发布所有代码
可能很难知道我在做什么
提前致谢
James
Hi again,
sorry for posting two questions so close together but im working on a
school project which is due in soon and running into some difficulties
implementing the database parts. I have the code below which when
executed generates the following error message: ''There is already an
open datareader with this command which must be closed first''
Private Sub MainMenu_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
''Declare new connection variable
Dim cnn As SqlConnection
''Declare string to hold SQL reservation query
Dim sqlreservation As String
''Declare command to execute query
Dim cmdMain As SqlCommand
''Declare data reader to hold query results
Dim dr As SqlDataReader
''Declare insteger as loop counter
Dim i As Integer = 0
''Declare string to hold topic of reserved cutting
Dim topic As String
''Declare array to hold cutting ids
Dim cids() As String
''Declare arraylist to temporarily hold ids
Dim list As ArrayList = New ArrayList()
''Place container 100 pixels from top of screen
MenuContainer.Top = Me.Height - (Me.Height - 100)
''Place container in horizontal centre of screen
MenuContainer.Left = Me.Width / 2 - (MenuContainer.Width / 2)
''Instantiate the conection object
cnn = New SqlConnection
''Try block to catch errors
Try
''Set connections connection string property to global cnn
varialbe
cnn.ConnectionString = My.Settings.cnn
''Try to open connection
cnn.Open()
Catch ex As Exception
''Deal with exceptions if any occur by showing a message box
with exception details
MsgBox("An error occurred whilst trying to connect to your
SQL Server" + Environment.NewLine + ex.Message, MsgBoxStyle.Exclamation
+ MsgBoxStyle.OkOnly, "Cutting Library")
''Stop exucting rest of this code
Exit Sub
End Try
''Set up query to find if any reservations for this user exist
sqlreservation = "SELECT CuttingID FROM CuttingReservation
WHERE StudentID = ''1000''"
''Instantiate command
cmdMain = New SqlCommand
''Set up try block to catch errors
Try
''Set connection for command to use
cmdMain.Connection = cnn
''Set comand text
cmdMain.CommandText = sqlreservation
''Execute command and store results in data reader
dr = cmdMain.ExecuteReader()
''Add contents of data reader to array list
While dr.Read()
list.Add(dr.GetValue(0).ToString())
End While
''Resize the array and copy the arraylist''s contents to
array
ReDim cids(list.Count - 1)
list.CopyTo(cids)
Catch ex As Exception
''Display error message to user ecxplaining error
MsgBox("An error occurred whilst querrying the database. "
+ ex.Message, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Cutting
Library")
''stop further executiong of code
Exit Sub
End Try
Try
''Iterate through all ids in array
For Each id As String In cids
''Set sqlreservation query to find status of each
cutting
sqlreservation = "SELECT Topic FROM Cutting WHERE
CuttingID = ''" + id + "'' AND Status = ''True''"
''Set command text to new query
cmdMain.CommandText = sqlreservation
''Set connection property
cmdMain.Connection = cnn
''execute query and catch results in datareader
dr = cmdMain.ExecuteReader()
While dr.Read()
''display message box telling user cutting is
avaialble
MsgBox(dr(0).ToString())
MsgBox("The following reserved cutting is now
available: " + dr.GetValue(0).ToString(), MsgBoxStyle.Information +
MsgBoxStyle.OkOnly, "Cutting Library")
End While
Next
Catch ex As Exception
MsgBox("failed " + ex.Message)
End Try
End Sub
Sorry for the large amount of code but without posting it all its
probably hard to get any idea as to what im doing
Thanks in advance
James
我已经尝试了dataReader.close方法，但我仍然得到错误！
你会建议放置这个吗？在我第一次使用它之后，我已经尝试过它了.b $ b。它是否正确？我还尝试了使用新的dataReader和命令对象的
，它仍然给我
相同的错误信息。数据读取器关闭后可以重复使用吗？
关于作业的事情，它是一个更大的项目的一部分，它可以接受外部接收帮助（只要我说从哪里来）
以及如果我发现我现在哪里出错我以后找到工作
我遇到同样的错误，我应该知道该怎么做！那就是我对这件事的意见了吗？
谢谢
詹姆斯
I have tried the dataReader.close method however i stil get the error!
where abouts would you recommend placing this? i''ve tried putting it
after the first time i used it. is this correct? I have also tried
using a new dataReader and command object and it still gives me the
same error message. Can the dataReader be reused once it is closed?
And about the homework thing, its part of a much larger project, which
i am allowed to receive external help for (as long as i say where from)
and also if i find out where i''m going wrong now when i get a job later
and i encounter the same error i should know what to do! thats just my
opinion on the matter though!
Thanks
james
jimmy写道：
jimmy wrote:
嗨再次，
很抱歉发布了两个如此接近的问题，但我正在研究一个即将到期的b $ b学校项目，并且遇到了一些困难
实施数据库部分。我有下面的代码当
执行时会生成以下错误消息：''已经有一个
open datareader with this command必须先关闭''br />
Private Sub MainMenu_Load（ByVal sender As System.Object，ByVal e As
System.EventArgs）处理MyBase.Load
''声明新的连接变量
Dim cnn As SqlConnection
''声明字符串以保存SQL预留查询
Dim sqlreservation As String
''声明执行查询的命令
Dim cmdMain As SqlCommand
''声明数据读取器以保存查询结果
Dim dr As SqlDataReader
''将insteger声明为循环计数器
Dim i As Integer = 0
''声明字符串以保留切割的主题
昏暗话题为字符串
'声明数组以保存切割ID
Dim cids（）As String
''声明arraylist暂时保存id
Dim list As ArrayList = New ArrayList（）
''将容器放置在屏幕顶部100像素
MenuContainer.Top = Me.Height - （Me.Height - 100）
''将容器放在屏幕的水平中心
MenuContainer.Left = Me.Width / 2 - （MenuContainer.Width / 2）
''实例化连接对象
cnn =新的SqlConnection
''尝试阻止捕获错误
试试
''将连接连接字符串属性设置为全局cnn
varialbe
cnn.ConnectionString = My.Settings.cnn
''尝试打开连接
cnn.Open（）
Catch ex As Exception
''通过显示消息框来处理异常（如果有的话）
有异常详细信息
MsgBox（尝试连接到您的
$时出错b $ b SQL Server + Environment.NewLine + ex.Message，MsgBoxStyle.Exclamation
+ MsgBoxStyle.OkOnly，Cutting Library）
''停止执行此代码的其余部分
退出Sub
结束尝试
''设置查询以查找是否存在此用户的预订
sqlreservation =" SELECT CuttingID FROM CuttingReservation
WHERE StudentID ='''1000''"
''实例化命令
cmdMain =新的SqlCommand
''设置try块来捕获错误
尝试
''设置连接以获取命令使用
cmdMain.Connection = cnn
''设置命令文本
cmdMain.CommandText = sqlreservation
''执行命令并将结果存储在数据读取器中
dr = cmdMain.Execute读者（）
''将数据读取器的内容添加到数组列表
当dr.Read（）
list.Add （Dr.GetValue（0）.ToString（））
结束时
''调整阵列大小并将arraylist的内容复制到
数组
ReDim cids（list.Count - 1）
list.CopyTo（cids）
Catch ex As Exception
''向用户ecxplaining错误显示错误消息
MsgBox（"查询数据库时发生错误。
+ ex.Message，MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly，Cutting
Library）
''停止进一步执行代码
退出Sub
结束尝试
Hi again,
sorry for posting two questions so close together but im working on a
school project which is due in soon and running into some difficulties
implementing the database parts. I have the code below which when
executed generates the following error message: ''There is already an
open datareader with this command which must be closed first''
Private Sub MainMenu_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
''Declare new connection variable
Dim cnn As SqlConnection
''Declare string to hold SQL reservation query
Dim sqlreservation As String
''Declare command to execute query
Dim cmdMain As SqlCommand
''Declare data reader to hold query results
Dim dr As SqlDataReader
''Declare insteger as loop counter
Dim i As Integer = 0
''Declare string to hold topic of reserved cutting
Dim topic As String
''Declare array to hold cutting ids
Dim cids() As String
''Declare arraylist to temporarily hold ids
Dim list As ArrayList = New ArrayList()
''Place container 100 pixels from top of screen
MenuContainer.Top = Me.Height - (Me.Height - 100)
''Place container in horizontal centre of screen
MenuContainer.Left = Me.Width / 2 - (MenuContainer.Width / 2)
''Instantiate the conection object
cnn = New SqlConnection
''Try block to catch errors
Try
''Set connections connection string property to global cnn
varialbe
cnn.ConnectionString = My.Settings.cnn
''Try to open connection
cnn.Open()
Catch ex As Exception
''Deal with exceptions if any occur by showing a message box
with exception details
MsgBox("An error occurred whilst trying to connect to your
SQL Server" + Environment.NewLine + ex.Message, MsgBoxStyle.Exclamation
+ MsgBoxStyle.OkOnly, "Cutting Library")
''Stop exucting rest of this code
Exit Sub
End Try
''Set up query to find if any reservations for this user exist
sqlreservation = "SELECT CuttingID FROM CuttingReservation
WHERE StudentID = ''1000''"
''Instantiate command
cmdMain = New SqlCommand
''Set up try block to catch errors
Try
''Set connection for command to use
cmdMain.Connection = cnn
''Set comand text
cmdMain.CommandText = sqlreservation
''Execute command and store results in data reader
dr = cmdMain.ExecuteReader()
''Add contents of data reader to array list
While dr.Read()
list.Add(dr.GetValue(0).ToString())
End While
''Resize the array and copy the arraylist''s contents to
array
ReDim cids(list.Count - 1)
list.CopyTo(cids)
Catch ex As Exception
''Display error message to user ecxplaining error
MsgBox("An error occurred whilst querrying the database. "
+ ex.Message, MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Cutting
Library")
''stop further executiong of code
Exit Sub
End Try
dr.Close
dr.Close
>
尝试
''遍历数组中的所有ID
For each id As String in cids
''设置sqlreservation查询以查找每个的状态
切割
sqlreservation =" SELECT主题从切割WHERE
CuttingID =''" + id +"''AND Status =''True''"
''将命令文本设置为新查询
cmdMain.CommandText = sqlreservation
''设置连接属性
cmdMain.Connection = cnn
''执行查询并捕获结果datareader
dr = cmdMain.ExecuteReader（）
当dr.Read（）
''显示消息框告诉用户切割是
avaialble
MsgBox（dr（0）.ToString（））
MsgBox（"以下保留的剪切现在是
可用：" + dr.GetValue（0）.ToString（），MsgBoxStyle.Information +
MsgBoxStyle.OkOnly，Cutting Library）
结束时
下一页
Catch ex As Exception
MsgBox（失败） + ex.Message）
结束尝试
结束子
对于大量的代码很抱歉但没有发布所有代码
可能很难知道我在做什么
提前致谢
James
>
Try
''Iterate through all ids in array
For Each id As String In cids
''Set sqlreservation query to find status of each
cutting
sqlreservation = "SELECT Topic FROM Cutting WHERE
CuttingID = ''" + id + "'' AND Status = ''True''"
''Set command text to new query
cmdMain.CommandText = sqlreservation
''Set connection property
cmdMain.Connection = cnn
''execute query and catch results in datareader
dr = cmdMain.ExecuteReader()
While dr.Read()
''display message box telling user cutting is
avaialble
MsgBox(dr(0).ToString())
MsgBox("The following reserved cutting is now
available: " + dr.GetValue(0).ToString(), MsgBoxStyle.Information +
MsgBoxStyle.OkOnly, "Cutting Library")
End While
Next
Catch ex As Exception
MsgBox("failed " + ex.Message)
End Try
End Sub
Sorry for the large amount of code but without posting it all its
probably hard to get any idea as to what im doing
Thanks in advance
James
这篇关于已经有一个打开的datareader与此命令必须首先关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
