Click here to Skip to main content
14,486,801 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

       Using 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
               chatreader.Close()
           End While
       End Using



   End Sub


What I have tried:

i've tried many different ways of fixing this....i've tried opening and closing all the connection...i've checked all datareader if they are left open and i've used using block on all the datareader....yet it is still showing same error......
Posted
Updated 20-Mar-20 4:09am
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
I told you this three days ago!

Look through your entire code, and delete your "common" SqlConnection - replace it with individual ones created inside Using blocks when you need them.

And your loop is still weird: why do you close the reader at the end of the first iteration?

Quote:
should i put connection property inside using block or is there a way to put property in connection class and connect it with new connection inside using block ??????

Please do not use multiple question marks in communications with me again: it is "a sign of a diseased mind" according to pTerry - and he's right.

You can use a class to encapsulate a connection - if you must - and put teh construction code into a using block:
            using (MyConnection con = MyConnection.GetConnection())
                {
                Console.WriteLine("Created");
                ...
                }
...
    internal class MyConnection : IDisposable
        {
        private MyConnection() { }
        public static MyConnection GetConnection() 
            {
            return new MyConnection(); 
            }

        #region IDisposable Support
        private bool disposedValue = false; // To detect redundant calls

        protected virtual void Dispose(bool disposing)
            {
            Console.WriteLine("Disposing");
 ...
            }

        public void Dispose()
            {
            // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
            Dispose(true);
            // TODO: uncomment the following line if the finalizer is overridden above.
            // GC.SuppressFinalize(this);
            }
        }
And the system will automatically discard it when it goes out of scope.


Sorry, forgot you use VB.
   Using con As MyConnection = MyConnection.GetConnection()
            Console.WriteLine("Created")
        End Using
...
    Friend Class MyConnection
        Inherits IDisposable

        Private Sub New()
        End Sub

        Public Shared Function GetConnection() As MyConnection
            Return New MyConnection()
        End Function

        Private disposedValue As Boolean = False

        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            Console.WriteLine("Disposing")
            ...
        End Sub

        Public Sub Dispose()
            Dispose(True)
        End Sub
    End Class
   
v3
Comments
Xion1624 20-Mar-20 10:19am
   
should i put connection property inside using block or is there a way to put property in connection class and connect it with new connection inside using block ??????
ZurdoDev 20-Mar-20 11:06am
   
VB does not have the type of using he is referring to.

As I said in my solution, you have to remove the close code from within your loop.
Xion1624 20-Mar-20 11:13am
   
already did that bro still same error
OriginalGriff 20-Mar-20 11:15am
   
Um ... are you sure:
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement
Xion1624 20-Mar-20 11:25am
   
sorry for multiple question mark
ZurdoDev 20-Mar-20 11:26am
   
Those stinkers. When did they sneak that in there. I'm clearly out of date with my VB syntax. :(
OriginalGriff 20-Mar-20 11:30am
   
.NET 2.0, in 2005 ... :laugh:
Xion1624 20-Mar-20 11:30am
   
it that code same for module too??
Dave Kreskowiak 20-Mar-20 12:23pm
   
Ummm... yes it does.
ZurdoDev 20-Mar-20 12:28pm
   
Ya, someone already pointed that out. You lose. :)
Xion1624 20-Mar-20 11:36am
   
Imports System.Security.Cryptography
Imports System.IO
Imports MySql.Data.MySqlClient

Module Con_Add
Public usrnameFriend As String
Public Conn As New MySqlConnection

Public ConTemp As New MySqlConnection
Public MsgSubject As String = String.Empty

Sub ConTempOpen()
ConTemp.ConnectionString = "server=" & My.Settings.Host & "; user id=" & My.Settings.User & "; password=" & My.Settings.Pass & "; database= " & My.Settings.dbName
Try
ConTemp.Open()
Catch ex As Exception
MsgBox(ex.Message & vbNewLine & "Check your connection.", MsgBoxStyle.Critical, "Can not connect to the Database")
Application.Exit()
End Try
End Sub

Sub OpenConnection()
Conn.ConnectionString = "server=" & My.Settings.Host & "; user id=" & My.Settings.User & "; password=" & My.Settings.Pass & "; database= " & My.Settings.dbName

Try
Conn.Open()
Catch ex As Exception
MsgBox(ex.Message & vbNewLine & "Check your connection." & vbNewLine & "Application will close.", MsgBoxStyle.Critical, "Can not connect to the Database")
Application.Exit()
End Try

End Sub


Function MD5Encrypter(ByVal strPass As String) As String


Dim Hasher As New MD5CryptoServiceProvider
Dim PasswordBytes As Byte() = New Byte(strPass.Length + 3) {}
Dim HashBytes As Byte()

For i As Integer = 0 To strPass.Length - 1
PasswordBytes(i) = CByte(Asc(strPass(i)))
Next
PasswordBytes(strPass.Length) = CByte(90)
PasswordBytes(strPass.Length + 1) = CByte(85)
PasswordBytes(strPass.Length + 2) = CByte(66)
PasswordBytes(strPass.Length + 3) = CByte(73)

HashBytes = Hasher.ComputeHash(PasswordBytes)

Dim NewHashBytes As Byte() = New Byte(HashBytes.Length + 3) {}

For j As Integer = 0 To hashBytes.Length - 1
newhashBytes(j) = hashBytes(j)
Next
newhashBytes(hashBytes.Length) = CByte(90)
newhashBytes(hashBytes.Length + 1) = CByte(85)
newhashBytes(hashBytes.Length + 2) = CByte(66)
newhashBytes(hashBytes.Length + 3) = CByte(73)

strPass = Convert.ToBase64String(newhashBytes)
Return strPass
End Function

Function ImageToBytes(ByVal imgPath As String) As String
Try
Dim Image_DP As Image = Image.FromFile(imgPath)
Dim Memories As MemoryStream = New MemoryStream()
Image_DP.Save(Memories, System.Drawing.Imaging.ImageFormat.Png)
Dim ImageBytes As Byte() = Memories.ToArray()

Dim StringImage As String = Convert.ToBase64String(ImageBytes)

Return StringImage


Catch ex As Exception
MsgBox(ex.Message & vbNewLine & "Failed to convert image to bytes.", MsgBoxStyle.Critical, "Error Occurred")
End Try

End Function




End Module
(this is my connection module)
Dave Kreskowiak 20-Mar-20 12:28pm
   
You're still doing what I told you not to do 4 days ago.

See that "Public ConTemp As New MySqlConnection" line at the top of your code? DO NOT DO THAT! This is why you're having the problem you can't solve.

ALWAYS create the connection object where you need it, NEVER globally in a Module or Class.


...and something else you're not understanding is MD5 is not an encryption. It is a cryptographic hash. Encryption can be reversed to get the original content back. Hashes cannot be reversed.
Rate this:
Please Sign up or sign in to vote.

Solution 1

You are closing the reader inside of the loop where you are reading from it. Move your close code to outside of the loop.
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

Try enabling MutipleActiveResultSet in your connection string add this
MultipleActiveResultSets=True
   
Comments
Xion1624 20-Mar-20 11:06am
   
Sub ConTempOpen()
ConTemp.ConnectionString = "server=" & My.Settings.Host & "; user id=" & My.Settings.User & "; password=" & My.Settings.Pass & "; database= " & My.Settings.dbName
Try
ConTemp.Open()
Catch ex As Exception
MsgBox(ex.Message & vbNewLine & "Check your connection.", MsgBoxStyle.Critical, "Can not connect to the Database")
Application.Exit()
End Try
End Sub

Sub OpenConnection()
Conn.ConnectionString = "server=" & My.Settings.Host & "; user id=" & My.Settings.User & "; password=" & My.Settings.Pass & "; database= " & My.Settings.dbName

Try
Conn.Open()
Catch ex As Exception
MsgBox(ex.Message & vbNewLine & "Check your connection." & vbNewLine & "Application will close.", MsgBoxStyle.Critical, "Can not connect to the Database")
Application.Exit()
End Try

End Sub



(where do i add that???)
codejet 23-Mar-20 8:50am
   
Sub ConTempOpen()
ConTemp.ConnectionString = "server=" & My.Settings.Host & "; user id=" & My.Settings.User & "; password=" & My.Settings.Pass & "; database= " & My.Settings.dbName & ";MultipleActiveResultSets=True"

'--It can be added anywhere in the connection string

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