0

I'm trying to get data from database but I got an error:

There is already an open DataReader associated with this Connection which must be closed first

what I did is the following codes:

1: I have a module that contains the following sub:

Public Function CheckServerConn() As Boolean
        Try
            _ServerConnStr = New MySqlConnection(My.Settings.DbPath)
            If _ServerConnStr.State = ConnectionState.Open Then
                _ServerConnStr.Close()
            End If

            If _ServerConnStr.State = ConnectionState.Closed Then
                _ServerConnStr.Open()
                Return True
            End If

        Catch ex As Exception
            MsgBox("Check The Conn " & ex.Message, Me_MsgInfoStyle, Me_MsgCaptionStr)
            Return False
        End Try
#Disable Warning BC42353 ' Function doesn't return a value on all code paths
    End Function

2: I have this subroutine in class called "ClsMySql":

'GetData
    Public Sub GetData(ByVal SqlStr As String, ByVal xDt As DataTable, ByVal xPar() As MySqlParameter)
        Using xCmd As New MySqlCommand() With {
                    .CommandType = CommandType.Text,
                    .CommandText = SqlStr,
                    .Connection = _ServerConnStr,
                    .CommandTimeout = 5000000
                    }

            If xPar IsNot Nothing Then
                For i As Integer = 0 To xPar.Length - 1
                    xCmd.Parameters.Add(xPar(i))
                Next
            End If

            Using xDa As New MySqlDataAdapter(xCmd)
                xDa.Fill(xDt)
            End Using
            xDt.Dispose()
        End Using
    End Sub

3: I have a class for the table that have the following method:

Public Sub MySql_Get_Daf()
    xClsMySql = New ClsMySql
    Dim SqlStr As String = "SELECT RegID,RegType, tblregs1.`RegRef`,`RegDate`, COUNT(`RegdID`) AS xCount 
                        ,IF(COUNT(`RegdID`) =3,'Ok','Error') AS xResult FROM tblregs1
                        INNER JOIN tblregs2 ON tblregs2.RegRef = tblregs1.RegRef
                        WHERE `RegType` = 'Daf'
                        GROUP BY tblregs1.`RegRef`  
                        ORDER BY COUNT(`RegdID`)  ASC"
    Dt_Get_Daf = New DataTable()
    xClsMySql.GetData(SqlStr, Dt_Get_Daf, Nothing)

End Sub

Public Sub MySql_Get_Qbd()
    xClsMySql = New ClsMySql
    Dim SqlStr As String = "SELECT RegID,RegType, tblregs1.`RegRef`,`RegDate`, COUNT(`RegdID`) AS xCount 
                        ,IF(COUNT(`RegdID`) =3,'Ok','Error') AS xResult FROM tblregs1
                        INNER JOIN tblregs2 ON tblregs2.RegRef = tblregs1.RegRef
                        WHERE `RegType` = 'Qbd'
                        GROUP BY tblregs1.`RegRef`  
                        ORDER BY COUNT(`RegdID`)  ASC"
    Dt_Get_Qbd = New DataTable()
    xClsMySql.GetData(SqlStr, Dt_Get_Qbd, Nothing)
End Sub

Public Sub MySql_Get_All()
    Dim xThread As Thread = New Thread(Sub() MySql_Get_Daf())
    Dim xThread2 As Thread = New Thread(Sub() MySql_Get_Qbd())

    xThread.Start()
    xThread2.Start()

End Sub

when I call MySql_Get_All by a button it gives me the next error:

There is already an open DataReader associated with this Connection which must be closed first

can anybody tell me what's the wrong here

4
  • 3
    Don't use a common connection. When you create a command, create a new connection for it. Commented Dec 14, 2022 at 13:31
  • @jmcilhinney ok, but could you please tell me how to check the connection for one time and then do what you say? Commented Dec 14, 2022 at 16:02
  • As per @jmcilhinney comment, just don't use common connections. Then you don't need to worry about checking the connection for one time (whatever that actually means). Define your connection with a Using block when and only when it's needed. There are literal thousands of examples and tutorials on the subject Commented Dec 14, 2022 at 19:29
  • The following are in C#, but it's quite similar in VB.NET: stackoverflow.com/a/74576869/10024425, stackoverflow.com/a/71540398/10024425, and stackoverflow.com/a/66987061/10024425 Commented Dec 14, 2022 at 21:29

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.