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.
