CODEWITHSUNDEEP
databasevb.netms-accessreader
Lettuce.W
Lettuce.W

Reputation: 19

'Data reader' closed but error says not

The program is able to run. However when the function button is clicked, the error says that the Data Reader is not closed, which I actually did.1

Any solutions kind souls ?

Error: There is already an open DataReader associated with this Command which must be closed first.

 Else
                    If checkoutdate.Value >= checkindate.Value Then

                        cmdsearch.CommandText = "SELECT * FROM [Reservations] where [ReservationID] = " & reservationidlbl.Text
                        cmdupdate.CommandType = CommandType.Text
                        cmdsearch.Connection = cnnoledb
                        Dim read3 As OleDbDataReader = cmdsearch.ExecuteReader()

                        If checkindate.Value & checkoutdate.Value >= read3(5) & read3(6) Then
                            If read3(5) & read3(6) <= checkindate.Value & checkoutdate.Value Then
                                cmdupdate.CommandText = "INSERT INTO [Reservations] (ReservationID, [RoomNo], CustomerName, [IC/Passport], ContactNo, [CheckIn_Date], [CheckOut_Date], RoomType, Deposit, ReservationDate,[Status]) values ( '" & reservationidlbl.Text & "' , '" & roomtxt.Text & "', '" & nametxt.Text & "', '" & passporttxt.Text & "', '" & contacttxt.Text & "','" & checkindate.Text & "','" & checkoutdate.Text & "','" & roomtype2 & "','" & deposittxt.Text & "','" & DateAndTime.Now.ToString & "', '" & status & "')"
                                cmdupdate.CommandType = CommandType.Text
                                cmdupdate.Connection = cnnoledb
                                cmdupdate.ExecuteNonQuery()

                                MsgBox("Reservation made.")
                            Else
                                MsgBox("This room is reserved for the specified date.")
                            End If
                            read3.Close()
                        Else
                            MsgBox("This room is reserved for the specified date.")
                        End If
                        read3.Close()

Upvotes: 0

Views: 54

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

You're using the same cnnoledb object for both cmdsearch and cmdupdate. That's not okay. It's not good practice to re-use one connection object throughout your app. You need to create a new connection object for each call to the database, and instead just share the connection string.

Additionally, it's a really bad idea for MS Access to insert into a table while holding open a connection that reads from it. This isn't Sql Server; concurrency isn't a strong point for Access. Best to either 1) go through the whole DataReader and hold onto the info for the insert in a collection, and then insert it when the first connection is done, or 2) combine all of this instead a single statement that does both the INSERT and SELECT (yes, this is possible).

Finally... string concatention? Really? I thought we were past that. Everyone should know how to use parameterized queries by now. It ought to be one of the first thing they teach you, the first things you come across when learning for yourself. It's really really bad to use string concatention to put values into a query like that... even for Access.

Upvotes: 0

jmcilhinney
jmcilhinney

Reputation: 54417

Clearly there is another data reader that you created somewhere and neglected to close. This is a demonstration of why you should ALWAYS using a Using block to create such objects, because you then cannot neglect to close them, e.g.

Using myDataReader = myCommand.ExecuteReader()
    'Read data here.
End Using 'Data reader is implicitly closed here.

Upvotes: 1

Related Questions