Hi Dw.

I'm deeply in need of your help, I'm having a problem that is giving me a very serious problem with my code.

My program is a multithreaded program and its a TCP/IP based application. Client has no problem, only a server that is being so weid to me now. The client sends signals to the server and a server will perform an action according to the client's signal, and as it's a multithreaded app the server can receive many different signals as well as many same signals at the same time. When I used Microsoft Access it was working fine, mean it did everything the way i coded it to. The only problem I had was the connection limit which I was then advised to change to MySQL server and I did download and set it according to my MS Access database.

Now the problem is within the connection request to server database it says Connection already open What I want is to make this support multithread access to the database. Here's my connection code:

Public Sub ConnDB()
        Try
            conn = New MySqlConnection
            conn.ConnectionString = "server=localhost;userid=root;password=mypassword;database=mydatabasename"
            conn.Open()
        Catch ex As Exception
            Debug.Write(ex.ToString())
            MsgBox(ex.ToString & " Im ConnDB")
        Finally

            conn.Dispose()
            conn.Close()
            'MsgBox(ex.Message)
            'MsgBox("Failed in Connecting to database")
            'conn.Open()
        End Try
        conn.Close()
    End Sub

Here's my code which I get a problem with.

If pp = "" Or pp = "0" Then
                                                                            If CInt(amount) = CInt(OriginalAmount) Then
                                                                                REM: The user want to deposit all the amount
                                                                                Try
                                                                                    MsgBox("3")
                                                                                    conn.Open()
                                                                                    Dim cmd As New MySqlCommand("INSERT INTO tms.mytransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber,@Who,@Remaining,@pp)", conn)
                                                                                    'Dim lcmd As New OleDbCommand("INSERT INTO MyTransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber)", conn)
                                                                                    cmd.Parameters.AddWithValue("@username", Donator)
                                                                                    cmd.Parameters.AddWithValue("@ReservedUser", Donatee)
                                                                                    cmd.Parameters.AddWithValue("@Amount", amount)
                                                                                    cmd.Parameters.AddWithValue("@dueDate", duedate)
                                                                                    cmd.Parameters.AddWithValue("@Status", "Reserved")
                                                                                    cmd.Parameters.AddWithValue("@RefNumber", DonRefNumber)
                                                                                    cmd.Parameters.AddWithValue("@Who", "Doner")
                                                                                    cmd.Parameters.AddWithValue("@Remaining", "0")
                                                                                    cmd.Parameters.AddWithValue("@pp", "0")
                                                                                    'Dim rreserveduser, ramount, rduedate, rstatus, rrefnumber As String
                                                                                    cmd.ExecuteNonQuery()
                                                                                    'conn.Close()
                                                                                Catch ex As Exception
                                                                                    MsgBox(ex.ToString & " Im18")
                                                                                Finally
                                                                                    conn.Close()
                                                                                    conn.Dispose()
                                                                                End Try
                                                                                Try
                                                                                    MsgBox("4")
                                                                                    conn.Open()
                                                                                    sqL = "UPDATE tms.mytransactions SET Status = '" & "Reserved'," & "Amount = '" & amount & "', Who = '" & "Owner', " & "Remaining = '0', pp ='Closed" & "', ReservedUser = '" & "" & "' WHERE username ='" & Donatee & "'"
                                                                                    cmd = New MySqlCommand(sqL, conn)
                                                                                    Dim si As Integer
                                                                                    si = cmd.ExecuteNonQuery
                                                                                Catch ex As Exception
                                                                                    MsgBox(ex.ToString & " Im19")
                                                                                Finally
                                                                                    conn.Close()
                                                                                    conn.Dispose()
                                                                                End Try
                                                                                'conn.Close()
                                                                                response = "Done:<>"
                                                                                serverResponse = response
                                                                                sendBytes = Encoding.ASCII.GetBytes(serverResponse)
                                                                                networkStream.Write(sendBytes, 0, sendBytes.Length)
                                                                                networkStream.Flush()
                                                                            Else
                                                                                If CInt(amount) < CInt(OriginalAmount) Then
                                                                                    MsgBox("2.5")
                                                                                    REM: Because the amount is less I will have to do some verifications including:
                                                                                    REM: First check if the donation hasn't been closed yet.
                                                                                    REM: Check if the amount will settle all the remaining balance
                                                                                    REM: Check if the remainder will not be less then 500 if this will leave a remainder.
                                                                                    If CInt(OriginalAmount) - CInt(amount) < 500 Then
                                                                                        'MsgBox("The remainder is less then 500")
                                                                                        response = "Less:<>"
                                                                                        serverResponse = response
                                                                                        sendBytes = Encoding.ASCII.GetBytes(serverResponse)
                                                                                        networkStream.Write(sendBytes, 0, sendBytes.Length)
                                                                                        networkStream.Flush()

                                                                                    Else
                                                                                        MsgBox("2.6")
                                                                                        Dim xRemainder As Double
                                                                                        xRemainder = CInt(OriginalAmount) - CInt(amount)
                                                                                        Try
                                                                                            'Try
                                                                                            MsgBox("3")
                                                                                            conn.Open()
                                                                                            Dim cmd As New MySqlCommand("INSERT INTO tms.mytransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber,@Who,@Remaining,@pp)", conn)
                                                                                            'Dim lcmd As New OleDbCommand("INSERT INTO MyTransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber)", conn)
                                                                                            cmd.Parameters.AddWithValue("@username", Donator)
                                                                                            cmd.Parameters.AddWithValue("@ReservedUser", Donatee)
                                                                                            cmd.Parameters.AddWithValue("@Amount", amount)
                                                                                            cmd.Parameters.AddWithValue("@dueDate", duedate)
                                                                                            cmd.Parameters.AddWithValue("@Status", "Reserved")
                                                                                            cmd.Parameters.AddWithValue("@RefNumber", DonRefNumber)
                                                                                            cmd.Parameters.AddWithValue("@Who", "Doner")
                                                                                            cmd.Parameters.AddWithValue("@Remaining", "0")
                                                                                            cmd.Parameters.AddWithValue("@pp", "0")
                                                                                            'Dim rreserveduser, ramount, rduedate, rstatus, rrefnumber As String
                                                                                            cmd.ExecuteNonQuery()
                                                                                            'conn.Close()
                                                                                            'Catch ex As Exception
                                                                                            'MsgBox(ex.ToString & " Im18")
                                                                                            'Finally
                                                                                            'conn.Close()
                                                                                            'conn.Dispose()
                                                                                            'End Try
                                                                                            'conn.Close()
                                                                                            Try
                                                                                                MsgBox("2.8")
                                                                                                conn.Open()
                                                                                                sqL = "UPDATE tms.mytransactions SET Status = '" & "Open'," & "Amount = '" & OriginalAmount & "', Who = '" & "Owner', " & "Remaining = '" & xRemainder & "', pp ='Open" & "', ReservedUser = '" & Donator & "' WHERE username ='" & Donatee & "'"
                                                                                                cmd = New MySqlCommand(sqL, conn)
                                                                                                Dim si As Integer
                                                                                                si = cmd.ExecuteNonQuery
                                                                                            Catch ex As Exception
                                                                                                MsgBox(ex.ToString & " Im44")
                                                                                            Finally
                                                                                                conn.Close()
                                                                                                conn.Dispose()
                                                                                            End Try
                                                                                            'conn.Close()
                                                                                            response = "Done:<>"
                                                                                            serverResponse = response
                                                                                            sendBytes = Encoding.ASCII.GetBytes(serverResponse)
                                                                                            networkStream.Write(sendBytes, 0, sendBytes.Length)
                                                                                            networkStream.Flush()
                                                                                        Catch ex As Exception
                                                                                            MsgBox(ex.ToString & " Im13")
                                                                                        Finally
                                                                                            conn.Close()
                                                                                        End Try
                                                                                    End If
                                                                                End If
                                                                            End If
                                                                        Else
                                                                            If pp = "Open" Then
                                                                                REM: Because the pp is Open I will have to now check how much is the remaining balance.
                                                                                Try
                                                                                    conn.Open()
                                                                                    Dim dazzw As New MySqlDataAdapter(("select * from tms.mytransactions where username = '" & Donatee & "'"), conn)
                                                                                    Dim dtzzw As New DataTable
                                                                                    dazzw.Fill(dtzzw)
                                                                                    Dim CurrentRemainder As Integer
                                                                                    CurrentRemainder = CInt(dtzzw.Rows(0).Item("Remaining") & "")
                                                                                    'conn.Close()
                                                                                    'MsgBox("Current Remainder is: R" & CurrentRemainder)
                                                                                    If amount < CurrentRemainder Then
                                                                                        REM: Lets check if the the remander now when performing this transaction will not be less then 500
                                                                                        Dim vRemainder As Double
                                                                                        vRemainder = CurrentRemainder - CInt(amount)
                                                                                        If vRemainder < 500 And vRemainder > 0 Then
                                                                                            REM: This transaction does not meet our conditions so lets not allow it.
                                                                                            response = "Less:<>"
                                                                                            serverResponse = response
                                                                                            sendBytes = Encoding.ASCII.GetBytes(serverResponse)
                                                                                            networkStream.Write(sendBytes, 0, sendBytes.Length)
                                                                                            networkStream.Flush()
                                                                                        Else
                                                                                            If vRemainder >= 500 Then
                                                                                                REM: Well lets procceed by adding this transaction to the database.
                                                                                                Try
                                                                                                    Try
                                                                                                        conn.Open()
                                                                                                        Dim cmd As New MySqlCommand("INSERT INTO tms.mytransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber,@Who,@Remaining,@pp)", conn)
                                                                                                        'Dim lcmd As New OleDbCommand("INSERT INTO MyTransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber)", conn)
                                                                                                        cmd.Parameters.AddWithValue("@username", Donator)
                                                                                                        cmd.Parameters.AddWithValue("@ReservedUser", Donatee)
                                                                                                        cmd.Parameters.AddWithValue("@Amount", amount)
                                                                                                        cmd.Parameters.AddWithValue("@dueDate", duedate)
                                                                                                        cmd.Parameters.AddWithValue("@Status", "Reserved")
                                                                                                        cmd.Parameters.AddWithValue("@RefNumber", DonRefNumber)
                                                                                                        cmd.Parameters.AddWithValue("@Who", "Doner")
                                                                                                        cmd.Parameters.AddWithValue("@Remaining", "0")
                                                                                                        cmd.Parameters.AddWithValue("@pp", "0")
                                                                                                        'Dim rreserveduser, ramount, rduedate, rstatus, rrefnumber As String
                                                                                                        cmd.ExecuteNonQuery()
                                                                                                        'conn.Close()
                                                                                                    Catch ex As Exception
                                                                                                        MsgBox(ex.ToString & " Im20")
                                                                                                    Finally
                                                                                                        conn.Close()
                                                                                                        conn.Dispose()
                                                                                                    End Try

                                                                                                    Try
                                                                                                        conn.Open()
                                                                                                        sqL = "UPDATE tms.mytransactions SET Status = '" & "Open'," & "Amount = '" & OriginalAmount & "', Who = '" & "Owner', " & "Remaining = '" & vRemainder & "', pp ='Open" & "', ReservedUser = '" & "" & "' WHERE username ='" & Donatee & "'"
                                                                                                        cmd = New MySqlCommand(sqL, conn)
                                                                                                        Dim si As Integer
                                                                                                        si = cmd.ExecuteNonQuery
                                                                                                    Catch ex As Exception
                                                                                                        MsgBox(ex.ToString & " Im21")
                                                                                                    Finally
                                                                                                        conn.Close()
                                                                                                        conn.Dispose()
                                                                                                    End Try

                                                                                                    response = "Done:<>"
                                                                                                    serverResponse = response
                                                                                                    sendBytes = Encoding.ASCII.GetBytes(serverResponse)
                                                                                                    networkStream.Write(sendBytes, 0, sendBytes.Length)
                                                                                                    networkStream.Flush()
                                                                                                Catch ex As Exception
                                                                                                    MsgBox(ex.ToString & " Im22")
                                                                                                Finally
                                                                                                    conn.Close()
                                                                                                    conn.Dispose()
                                                                                                End Try
                                                                                            Else
                                                                                                If vRemainder = 0 Then
                                                                                                    Try
                                                                                                        Try
                                                                                                            conn.Open()
                                                                                                            Dim cmd As New MySqlCommand("INSERT INTO tms.mytransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber,@Who,@Remaining,@pp)", conn)
                                                                                                            'Dim lcmd As New OleDbCommand("INSERT INTO MyTransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber)", conn)
                                                                                                            cmd.Parameters.AddWithValue("@username", Donator)
                                                                                                            cmd.Parameters.AddWithValue("@ReservedUser", Donatee)
                                                                                                            cmd.Parameters.AddWithValue("@Amount", amount)
                                                                                                            cmd.Parameters.AddWithValue("@dueDate", duedate)
                                                                                                            cmd.Parameters.AddWithValue("@Status", "Reserved")
                                                                                                            cmd.Parameters.AddWithValue("@RefNumber", DonRefNumber)
                                                                                                            cmd.Parameters.AddWithValue("@Who", "Doner")
                                                                                                            cmd.Parameters.AddWithValue("@Remaining", "0")
                                                                                                            cmd.Parameters.AddWithValue("@pp", "0")
                                                                                                            'Dim rreserveduser, ramount, rduedate, rstatus, rrefnumber As String
                                                                                                            cmd.ExecuteNonQuery()
                                                                                                            'conn.Close()
                                                                                                        Catch ex As Exception
                                                                                                            MsgBox(ex.ToString & " Im23")
                                                                                                        Finally
                                                                                                            conn.Close()
                                                                                                            conn.Dispose()
                                                                                                        End Try
                                                                                                        Try
                                                                                                            conn.Open()
                                                                                                            sqL = "UPDATE tms.mytransactions SET Status = '" & "Reserved'," & "Amount = '" & OriginalAmount & "', Who = '" & "Owner', " & "Remaining = '" & vRemainder & "', pp ='Closed" & "', ReservedUser = '" & "" & "' WHERE username ='" & Donatee & "'"
                                                                                                            cmd = New MySqlCommand(sqL, conn)
                                                                                                            Dim si As Integer
                                                                                                            si = cmd.ExecuteNonQuery
                                                                                                        Catch ex As Exception
                                                                                                            MsgBox(ex.ToString & " Im24")
                                                                                                        Finally
                                                                                                            conn.Close()
                                                                                                            conn.Dispose()
                                                                                                        End Try

                                                                                                        response = "Done:<>"
                                                                                                        serverResponse = response
                                                                                                        sendBytes = Encoding.ASCII.GetBytes(serverResponse)
                                                                                                        networkStream.Write(sendBytes, 0, sendBytes.Length)
                                                                                                        networkStream.Flush()
                                                                                                    Catch ex As Exception
                                                                                                        MsgBox(ex.ToString & " Im25")
                                                                                                    Finally
                                                                                                        conn.Close()
                                                                                                        conn.Dispose()
                                                                                                    End Try
                                                                                                End If
                                                                                            End If
                                                                                        End If
                                                                                    Else
                                                                                        REM: The user just wish to deposit the whole remainder balance.
                                                                                        If amount = CurrentRemainder Then
                                                                                            Try
                                                                                                Try
                                                                                                    conn.Open()
                                                                                                    Dim cmd As New MySqlCommand("INSERT INTO tms.mytransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber,@Who,@Remaining,@pp)", conn)
                                                                                                    'Dim lcmd As New OleDbCommand("INSERT INTO MyTransactions VALUES(@username,@ReservedUser,@Amount,@dueDate,@Status,@RefNumber)", conn)
                                                                                                    cmd.Parameters.AddWithValue("@username", Donator)
                                                                                                    cmd.Parameters.AddWithValue("@ReservedUser", Donatee)
                                                                                                    cmd.Parameters.AddWithValue("@Amount", amount)
                                                                                                    cmd.Parameters.AddWithValue("@dueDate", duedate)
                                                                                                    cmd.Parameters.AddWithValue("@Status", "Reserved")
                                                                                                    cmd.Parameters.AddWithValue("@RefNumber", DonRefNumber)
                                                                                                    cmd.Parameters.AddWithValue("@Who", "Doner")
                                                                                                    cmd.Parameters.AddWithValue("@Remaining", "0")
                                                                                                    cmd.Parameters.AddWithValue("@pp", "0")
                                                                                                    'Dim rreserveduser, ramount, rduedate, rstatus, rrefnumber As String
                                                                                                    cmd.ExecuteNonQuery()
                                                                                                    'conn.Close()
                                                                                                Catch ex As Exception
                                                                                                    MsgBox(ex.ToString & " Im26")
                                                                                                Finally
                                                                                                    conn.Close()
                                                                                                    conn.Dispose()
                                                                                                End Try
                                                                                                Try
                                                                                                    conn.Open()
                                                                                                    sqL = "UPDATE tms.mytransactions SET Status = '" & "Reserved'," & "Amount = '" & OriginalAmount & "', Who = '" & "Owner', " & "Remaining = '" & "0" & "', pp ='Closed" & "', ReservedUser = '" & "" & "' WHERE username ='" & Donatee & "'"
                                                                                                    cmd = New MySqlCommand(sqL, conn)
                                                                                                    Dim si As Integer
                                                                                                    si = cmd.ExecuteNonQuery
                                                                                                Catch ex As Exception
                                                                                                    MsgBox(ex.ToString & " Im27")
                                                                                                Finally
                                                                                                    conn.Close()
                                                                                                    conn.Dispose()
                                                                                                End Try

                                                                                                response = "Done:<>"
                                                                                                serverResponse = response
                                                                                                sendBytes = Encoding.ASCII.GetBytes(serverResponse)
                                                                                                networkStream.Write(sendBytes, 0, sendBytes.Length)
                                                                                                networkStream.Flush()
                                                                                            Catch ex As Exception
                                                                                                MsgBox(ex.ToString & " Im28")
                                                                                            Finally
                                                                                                conn.Close()
                                                                                                conn.Dispose()
                                                                                            End Try
                                                                                        End If
                                                                                    End If
                                                                                Catch ex As Exception
                                                                                    MsgBox(ex.ToString & " Im29")
                                                                                Finally
                                                                                    conn.Close()
                                                                                    conn.Dispose()
                                                                                End Try
                                                                            End If
                                                                        End If

As you can see in some part I have to open the connection and read data and from that data I read I need to check what data it has and based on the data I have to perform another action(database reader) with the corret format to suite the client request. The above code do more database reading/updating and its the most essential part of the program. The problem is that I get an error that says connection has already open and even if I do close the connection it will then jump to another code not related to this signal. Its normally jump to a code potion on a register signal, but it sould not do that.

Any help with this problem, its a bit urgent now that I complete this project because I still need to do app development for this project.

Edited 6 Months Ago by Mr.M: correction

Is conn perhaps a global variable? When you are trying to access the same variable over multiple threads, such errors are bound to happen. It would be more straightforward for every thread to have it's own connection.

Edited 6 Months Ago by pritaeas

Comments
Great suggestion there. Thumbs up...

Yes my conn is a global variable. But the error just occure even if I run one app. About your suggestion of every thread to have it's own connection. I sense an even more code to write, also won't that when I run multiple applications/clients also give me the very same problem. What I was thinking is to make the connection also a multithreaded so that each thread will have its own connection. But my problem is how can I achieve that?

This question has already been answered. Start a new discussion instead.