There is already an open datareader associated with this command which must be closed first. Please provide easiest way of fixing this with explanation

Question

1.00/5 (1 vote)

See more:

Private Sub tmchat_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tmchat.Tick
       Dim cmdreadchat As New MySqlCommand("SELECT chat FROM tb_chat", Conn)
       Dim chatreader As MySqlDataReader
       chatreader = cmdreadchat.ExecuteReader

       While chatreader.Read()
           If Not chatreader(0).ToString = ChatBoxBackup.Text Then
               ChatBoxBackup.Text = chatreader(0).ToString
           End If

       End While
       chatreader.Close()

   End Sub

What I have tried:

i've already tried chatreader.Close();
please provide easiest way of fixing this with explanation

Posted 16-Mar-20 4:33am

Xion1624

Updated 16-Mar-20 6:02am

v2

Add a Solution

Comments

Richard MacCutchan 16-Mar-20 10:41am

Somewhere in the actual code that you are running is an open DataReader. You need to scan all the code to find it.

[no name] 16-Mar-20 10:46am

Maybe you need a "longer" timer tick interval, for starters.

2 solutions

Solution 2

It appears that you made the mistake of using a single SqlConnection object throughout your entire class. That's the wrong way to do it and leads to problems like this.

ALWAYS create your connection when you need it, like in this method you posted, create your command, execute your query, then tear it all down, including the connection, when you're done.

Posted 16-Mar-20 4:58am

Dave Kreskowiak

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-03-16T04:57:00

Solution 1

An SqlConnection can only support one DataReader at a time: when you "share" a connection you have to make sure that the reader is closed and disposed when you are finished with it at all times.

The best approach is not to "recycle" SQL connections: create it in a Using block, open it, use it, and let the system Dispose it when it goes out of scope.

But why are you looping at all? Your loop just overwrites all previous messages with the last one it reads from the DB ... and given this is a chat app you are retrieving all the messages in the system each time which is a very, very inefficient way to do anything.

Posted 16-Mar-20 4:57am

OriginalGriff

Comments

Xion1624 16-Mar-20 12:08pm

The best approach is not to "recycle" SQL connections: create it in a Using block, open it, use it, and let the system Dispose it when it goes out of scope.

can you please describe above sentence a bit more clearly?

But why are you looping at all? Your loop just overwrites all previous messages with the last one it reads from the DB ... and given this is a chat app you are retrieving all the messages in the system each time which is a very, very inefficient way to do anything.

and also can you please tell me how do i do this since i've just started learning vb and i'm doing this for my school project

OriginalGriff 16-Mar-20 12:35pm

The Using block:
See here:
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement

It explains what it does and how to use it in good detail, with an example.

Basically, you declare a variable in the Using block, and the system ensures that when it goes out of scope - for any reason - it is Disposed of automatically.
So a normal exit from the block: Dispose.
A Return in the block: Dispose.
An error in the block: Dispose.

Create your SqlConnection at the start of the Using block, and the system will Dispose of it for you when you are finished with it.

The Loop:
If you had a pile of coins and you wanted the bottom one, would you take each coin individually off the stack and place it in a new stack until you got to the last one? Or would you turn the stack upside down and just take the "bottom one"?

If your DB has a Timestamp column - and if it doesn't, add one, and fill it with the UTC Datetime when you INSERT a row - then you can use that to just return the latest row:

SELECT TOP 1 chat FROM tb_Chat ORDER BY PostDate DESC

That way, just the one row you are interested in gets fetched, and your whole system becomes a lot more efficient because you aren't throwing loads of unnecessary data around, and processing it only to throw it away.

It's also worth noting that unless you explicitly include a ORDER BY clause in your SQL SELECT, the system is at liberty to return rows in andy order which seems efficient to it - normally that ends up as "oldest first", but it doesn't have to be. If SQL reorganises it's row indexes, the order could be very different. If you want a specific order always use ORDER BY and have a column to order it by.

Xion1624 20-Mar-20 8:31am

Thanks a lot sir.

There is already an open datareader associated with this command which must be closed first. Please provide easiest way of fixing this with explanation

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

There is already an open datareader associated with this command which must be closed first. Please provide easiest way of fixing this with explanation

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us