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(B yVal sender As System.Object, ByVal e As
System.EventArg s) 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.ConnectionS tring = 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
CuttingReservat ion WHERE StudentID = '1000'"
'Instantiate & configure command
cmdMain = New SqlCommand
cmdMain.Connect ion = con
cmdMain.Command Text = sqlReservation
'Execute command and return DataReader to iterate over results
dr = cmdMain.Execute Reader()
'Copy contents of DataReader to ArrayList
Dim list As ArrayList = New ArrayList()
While dr.Read()
list.Add(dr.Get Value(0).ToStri ng())
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(cid s)
'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 cmdCuttingStatu s As New SqlCommand 'executes a query against a
SQL DB
cmdCuttingStatu s.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.Execute Reader()
While dr.Read()
'display message box telling user cutting is avaialble
MsgBox(dr(0).To String())
MsgBox("The following reserved cutting is now available: " &
dr.GetValue(0). ToString(), MsgBoxStyle.Inf ormation & MsgBoxStyle.OkO nly,
"Cutting Library")
End While
Next
'Configure menu container
'Place container 100 pixels from top of screen
MenuContainer.T op = Me.Height - (Me.Height - 100)
'Place container in horizontal centre of screen
MenuContainer.L eft = 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.New Line & ex.Message,
MsgBoxStyle.Exc lamation & MsgBoxStyle.OkO nly, "Cutting Library")
End Try
End Sub
"jimmy" <ja************ **@tiscali.co.u kwrote in message
news:11******** *************@m 58g2000cwm.goog legroups.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(B yVal sender As System.Object, ByVal e As
System.EventArg s) 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.T op = Me.Height - (Me.Height - 100)
'Place container in horizontal centre of screen
MenuContainer.L eft = 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.ConnectionS tring = 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.New Line + ex.Message, MsgBoxStyle.Exc lamation
+ MsgBoxStyle.OkO nly, "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 CuttingReservat ion
WHERE StudentID = '1000'"
'Instantiate command
cmdMain = New SqlCommand
'Set up try block to catch errors
Try
'Set connection for command to use
cmdMain.Connect ion = cnn
'Set comand text
cmdMain.Command Text = sqlreservation
'Execute command and store results in data reader
dr = cmdMain.Execute Reader()
'Add contents of data reader to array list
While dr.Read()
list.Add(dr.Get Value(0).ToStri ng())
End While
'Resize the array and copy the arraylist's contents to
array
ReDim cids(list.Count - 1)
list.CopyTo(cid s)
Catch ex As Exception
'Display error message to user ecxplaining error
MsgBox("An error occurred whilst querrying the database. "
+ ex.Message, MsgBoxStyle.Exc lamation + MsgBoxStyle.OkO nly, "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.Command Text = sqlreservation
'Set connection property
cmdMain.Connect ion = cnn
'execute query and catch results in datareader
dr = cmdMain.Execute Reader()
While dr.Read()
'display message box telling user cutting is
avaialble
MsgBox(dr(0).To String())
MsgBox("The following reserved cutting is now
available: " + dr.GetValue(0). ToString(), MsgBoxStyle.Inf ormation +
MsgBoxStyle.OkO nly, "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
