Click here to Skip to main content
14,494,314 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 16-Mar-20 6:02am
v2
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.
Gerry Schmitz 16-Mar-20 10:46am
   
Maybe you need a "longer" timer tick interval, for starters.
Rate this:
Please Sign up or sign in to vote.

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.
   
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.
Rate this:
Please Sign up or sign in to vote.

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.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100