By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

There is already an open datareader with this command which must be closed first

P: n/a
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

Jan 22 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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:
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
Jan 22 '07 #2

P: n/a
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

Jan 22 '07 #3

P: n/a

jimmy wrote:
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
>
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
Jan 22 '07 #4

P: n/a
Well, think about the message for a moment. It seems to be indicating that
you already have an open DataReader, right? That's because you do. You are
using dr to refer to one DataReader and then you are using it again to refer
to another and you haven't closed it in between. While many error messages
don't really provide clear information about what the problem is, this isn't
one of them.

Also, I have to say that if your teacher is teaching you to write code like
you've shown, I'd ask for my money back for the course! A couple of
pointers...

Avoid "Exit Sub" statements when possible (and in your code it is very
possible) as they are considered by many to be a nasty way of controlling
program flow. Try...Catch statements can help here. Instead of using
bunches of them, just use one and you won't need those ugly Exit Subs
anymore.

Although the plus sign (+) is acceptable as a concatenation charictor, VB
distinguishes concatenation from mathmatical addition by providing two
different operators, the plus sign for mathematical addition and the
ampersand for string concatenation.

Next, in your comments, you refer to using a DataReader to "store" the
results of your query. DataReaders do not store anything, they allow you to
have a connected look at the original data that matches your query criteria.

You've also attempted to use your connection (con.Open()) before
instantiating it (con = New SQLConnection) - that will never work.

You've certainly got the commenting thing down! But don't just write
comments because you are supposed to, write them in a way that will help you
understand the code (and others as well). You really don't need to comment
*every* line of code. That will just make more clutter and take much more
time. Instead of declaring all your variables at the beginning of your
code, I (and others) prefer to declare variables in the general proximity of
where they will be needed. This becomes significant when you get to writing
routines that are hundreds of lines of code long. Also, be aware of your
capitalization. Just because VB isn't case-sensitive doesn't mean we don't
care about it. Be consistent is the main thing. In general, it is common
to use Camel Case (cameCase) on private variables and Pascal Case
(PascalCase) on public variables. So, instead of: your code, how about
this:

Private Sub MainMenu_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

'ADO.NET variable declarations
Dim con As SqlConnection 'gets me connected to SQL
Dim cmdMain As SqlCommand 'executes a query against a SQL DB
Dim dr As SqlDataReader 'used to iterate over query results

Try
'Instantiate the conection object
cnn = New SqlConnection '<--- You had this after you were attempting
to open the connection!

'Set connection's connection string property to global cnn varialbe
con.ConnectionString = My.Settings.con

cnn.Open()

'Set up & store query to find if any reservations for this user exist
Dim sqlReservation As String = "SELECT CuttingID FROM
CuttingReservation WHERE StudentID = '1000'"

'Instantiate & configure command
cmdMain = New SqlCommand
cmdMain.Connection = con
cmdMain.CommandText = sqlReservation

'Execute command and return DataReader to iterate over results
dr = cmdMain.ExecuteReader()

'Copy contents of DataReader to ArrayList
Dim list As ArrayList = New ArrayList()
While dr.Read()
list.Add(dr.GetValue(0).ToString())
End While

dr.Close() '<--- This is what you didn't have in your code

'Declare string to hold topic of reserved cutting
Dim topic As String

'Declare String array to hold cutting id's
Dim cids() As String

'Resize the array and copy the arraylist's contents to array
ReDim cids(list.Count - 1)
list.CopyTo(cids)

'Get ready to query the database again
'Never a good idea to re-use commands and data readers..it just causes
problems!
'Good idea to re-use connections though.
Dim cmdCuttingStatus As New SqlCommand 'executes a query against a
SQL DB
cmdCuttingStatus.connection = con
Dim dr2 As SqlDataReader 'used to iterate over query results
Dim sqlQueryText As String

'Iterate through all ids in array
For Each id As String In cids

'Set sqlQueryText to query that finds status of each cutting
sqlQueryText = "SELECT Topic FROM Cutting WHERE CuttingID = '" &
id & "' AND Status = 'True'"

'Execute command with new query and access results via a
DataReader
dr2 = 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

'Configure menu container
'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)
Catch ex As Exception
'Deal with exceptions if any occur by showing a message box with
exception details
MsgBox("An error occurred ..." & Environment.NewLine & ex.Message,
MsgBoxStyle.Exclamation & MsgBoxStyle.OkOnly, "Cutting Library")
End Try
End Sub


"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11*********************@m58g2000cwm.googlegro ups.com...
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

Jan 22 '07 #5

P: n/a
Thanks for the code, it isnt working at the moment however it looks
more promising than mine!

As for the way i write my code its probably because i am completely
self taught, the course i'm doing is a GCSE course and doesn't involve
any programming, however i chose to do a more advanced project to
challenge myself.

I use several Try...Catch blocks because i find it makes it easier when
added more detailed error catching at a later date, by adding handlers
for specific errors however i could probably do it with just one block,
it would just require a bit more thinking. I know the error message was
obvious but i had already tried closing the datareader i guess i just
did it in the wrong place.

Thanks anyway

Jan 22 '07 #6

P: n/a
"jimmy" <ja**************@tiscali.co.ukschrieb
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'
As the message says, first close the reader by calling it's close method
before opening another Datareader.
Armin

Jan 22 '07 #7

P: n/a
Thanks, but i have solved the problem now. I tried closing the
datareaders and even used the code the Scott provided but it still gave
me the same error! Instead i just created a more advanced query to get
all the info in one go!
Thanks

James

Jan 22 '07 #8

P: n/a
Forgot to mention that the 2nd DataReader needs to be closed after you
finish using it and just before the End Try, you should add

Finally
con.close
End Try
"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
Thanks for the code, it isnt working at the moment however it looks
more promising than mine!

As for the way i write my code its probably because i am completely
self taught, the course i'm doing is a GCSE course and doesn't involve
any programming, however i chose to do a more advanced project to
challenge myself.

I use several Try...Catch blocks because i find it makes it easier when
added more detailed error catching at a later date, by adding handlers
for specific errors however i could probably do it with just one block,
it would just require a bit more thinking. I know the error message was
obvious but i had already tried closing the datareader i guess i just
did it in the wrong place.

Thanks anyway

Jan 22 '07 #9

P: n/a
You still need to close your DataReader and connection objects. And,
although I'm happy you got your code working, I would really consider making
the changes I suggested. I'm not surprised my code didn't work (as I was
writing it off the top of my head) to give you an idea of the direction to
go to. I know it's close though. You may want to work through it.
"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11**********************@11g2000cwr.googlegro ups.com...
Thanks, but i have solved the problem now. I tried closing the
datareaders and even used the code the Scott provided but it still gave
me the same error! Instead i just created a more advanced query to get
all the info in one go!
Thanks

James

Jan 22 '07 #10

P: n/a
yeah.. this FEATURE is _UNWANTED_

give me back ADO any day of the week.

and for the record, MARS-- the solution for this-- it doesn't work AT
ALL

assholes sold us on a feature that DOESNT WORK

-Aaron

Scott M. wrote:
Forgot to mention that the 2nd DataReader needs to be closed after you
finish using it and just before the End Try, you should add

Finally
con.close
End Try
"jimmy" <ja**************@tiscali.co.ukwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
Thanks for the code, it isnt working at the moment however it looks
more promising than mine!

As for the way i write my code its probably because i am completely
self taught, the course i'm doing is a GCSE course and doesn't involve
any programming, however i chose to do a more advanced project to
challenge myself.

I use several Try...Catch blocks because i find it makes it easier when
added more detailed error catching at a later date, by adding handlers
for specific errors however i could probably do it with just one block,
it would just require a bit more thinking. I know the error message was
obvious but i had already tried closing the datareader i guess i just
did it in the wrong place.

Thanks anyway
Jan 22 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.