Hi Dw.

I am developing a client server multi-thread application. This application uses signals(certain messages as signals), in order to perform a task for a client. For instance if a client wants to login, the client send a message as: Login:<username>:<password> and on a server I first check if the message contains : and if it does then I split the message using : as a split.

I am using the client server code from http://www.vb.net-informations.com/communications/vb.net_multithreaded_server_socket_programming.htm

But I didn't use it as Console its both WinForms. I'm facing avery hug problem here. All of my activity codes are under the handleClient class inside the doChat but I've changed that to doData.

My biggest problem comes when I run more then 1 client instances and when they all send a signal to retrieve data base on each client filter settings.

The Filter is just a string word which will tell the server for this client open database and retrieve all records let's say my filter was USA, then the server will retrieve all the records containing USA under the Country field. But first I first check if that record is available.

I have 2 tables on one database.
The first table is for transactions, then the second table is for registration.
The transaction table is where the transactions take place and its where most of the data sent to client(s) comes from, so this is a very busy table. It has 9 columns as follows.

username,bookedUser,Amount,dueDate,Status,RefNumber,Who,Remainder,pp

1) username -> this is the username for the registered user, this also helps when we need to locate the user under the registration table.
2) bookedUser -> this is 0 at first then if this user is open for bookings then once a another user books this user I put that user here, but I changed that when I added the Remainder, and the pp columns. So if let's say this user wants to be booked for 2500, if the other user offering to book this user says s/he will pay 2500 then that username is entered here. But if the amount is less then 2500 then I first check if the pp is open, if this is close then that means there can be no more bookings, else if this is open I then check if the field Remaining is greater then 0, if this is true then I check if the amount offered equal the remainder and do proper update. I will post a code for this bellow.

I have this under the doData() on the server side. Will only write what I saw giving me a problem.

 Dim ClieUsername As String
 Dim requestCount As Integer
 Dim bytesFrom(10024) As Byte
 Dim dataFromClient As String
 Dim sendBytes As [Byte]()
 Dim serverResponse As String
 Dim rCount As String
 requestCount = 0

 While (True)
 Try
 requestCount = requestCount + 1
 Dim networkStream As NetworkStream = client.GetStream
 networkStream.Read(bytesFrom,0,CInt(client.ReceiveBufferSize))
 dataFromClient = System.Text.Encoding.ASCII.GetString(bytesFrom)
 dataFromClient = dataFromClient.Substring(0, dataFromClient.IndexOf("$"))

 If dataFromClient.Contains(":") Then
 Dim list() As String
 list = dataFromClient.Split(":")
 'Now I check which signal was sent.

 If list(0).Contains("Login") Then
 'Do login here.

  Else
  If list(0).Contains("bringlist") Then
  ' Checking to see if a user has specified a country.

 If list(1).Trim("<",">") = Nothing Then
 ' The user has not specified a filter so just bring everything available.

 Try
 Dim Country As String
 ' Open a database connection.

 ConnDB()
 sql = "select * from transactions;"
 cmd = New OleDbCommand(sql, conn)
 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 Do While dr.Read = True
 'Status variable is declared às global variable.

 status = dr(4)
 Dim qqrefnumber As String
 If status = "Open" Then
 Dim dDate As String
 dDate = dr(3)
 If dDate >= DateTime.Today Then
 Dim xusername As String
 xusername = dr(0)
 price = dr(2) 'Amount

 Dim Calcu As Double
 qqrefnumber = dr(5)
 Try
 ConnDB()
 Dim da As New OleDbDataAdapter(("select * from register where username ='" & xusername & "'"),conn)
 Dim dt As New DataTable
 da.Fill(dt)
 If dt.Rows.Count > 0 Then
 Country = dt.Rows(0).Item("Country") & ""
 End If
 Catch ex As Exception

 End Try
 ' Sending data to the client.

 response = "List:<" & xusername & ">:<" & price & ">:<" & Country & ">:<" & dDate & ">:<" & "Open>:<" & qqrefnumber

 serverResponse = response
 sendBytes = Encoding.ASCII.GetBytes(serverResponse)
 networkStream.Write(sendBytes,0,sendBytes.Length)
 networkStream.Flush()
 Else
 'Because there is no data meet this date
 'Send a notification so that a client won't
 'Freeze waiting for the response.

 response = "nodata:<>"
 serverResponse = response
 sendBytes = Encoding.ASCII.GetBytes(serverResponse)
 networkStream.Write(sendBytes,0,sendBytes.Length)
 networkStream.Flush()
 End If
 Else

 End If
 Loop
 Catch ex As Exception
 'The error that is most generated here use to say
 'Unspecified error message so just take it
 'As if it didn't get any data so that the client won't freeze.
 response ="nodata:<>"
 serverResponse = response
 sendBytes = Encoding.ASCII.GetBytes(serverResponse)
 networkStream.Write(sendBytes,0,sendBytes.Length)
 networkStream.Flush()
 End Try

 Else
 ' The user did specify the filter(country).
 Dim myFilter As String = list(1).Trim("<",">")
 Dim country As String
 Try
 ConnDB()
 sql = "select * from transactions;"
 cmd = New OleDbCommand(sql,conn)
 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 Do While (True)
 status = dr(4)
 Dim qqrefnumber As String
 If status = "Open" Then
 Dim dDate As String
 If dDate >= DateTime.Today Then
 Dim xusername As String
 xusername = dr(0)
 price = dr(2)
 qqrefnumber = dr(5)
 Try
 ConnDB()
 Dim da As New OleDbDataAdapter(("select * from register where username ='" & xusername & "'"),conn)
 Dim dt As New DataTable
 da.Fill(dt)
 If dt.Rows.Count > 0 Then
 Country = dt.Rows(0).Item("Country") & ""
 'Check to see if the Country match the filter.

 If Country = myFilter Then
 'Its does match so send it.
 response = "List:<" & xusername & ">:<" & price & ">:<" & Country & ">:<" & dDate & ">:<" & "Open>:<" & qqrefnumber
 serverResponse = response
 sendBytes = Enconding.ASCII.GetBytes(serverResponse)
 networkStream.Write(sendBytes,0,sendBytes.Length)
 networkStream.Flush()
 Else
 End If
 End If
 Catch ex As Exception

 End Try
 Else
 response = "nodata:<>"
 serverResponse = response
 sendBytes = Encoding.ASCII.GetBytes(serverResponse)
 networkStream.Write(sendBytes,0,sendBytes.Length)
 networkStream.Flush()
 End If
 Else
 End If
 Loop
 Catch ex As Exception
 'Send the response with nodata signal
 End Try
 End If
 End If

The problem is that if I run one client everything is fine, but if I run more then one at the same time they all freezes but the server is not freezing. Bellow is the relevant client signaling code relevant to the above server code.

Own about that ConnDB you keep seeing here is the module.

 Import System.Data.OleDb
 Module ModConnection
 Public cur As Form
 Public dt As DataTable
 Public conn As OleDbConnection
 Public cmd As OleDbCommand
 Public dr As OleDbDataReader
 Public da As OleDbDataAdapter
 Public ds As DataSet
 Public sql As String
 Public Sub ConnDB()
 Try
 conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="mydatabasefullPath;Jet OLEDB:Database Password = "myPassword")
 conn.Open()
 Catch ex As Exception
 Finally
 End Try
 End Sub

 Public Function getDataTable(ByVal SQL As String) As DataTable
 ConnDB()
 Dim cmd As New OleDBCommand(SQL, conn)
 Dim dt As New DataTable
 Dim da As New OleDbDataAdapter(cmd)
 da.Fill(dt)
 Return dt
 End Function
 End Module

Now the client side code relevant to the above first code potion.

Basically what it does is that after certain time its send a signal message to the server to retrieve the list and the list can be retrieved based on the users filter or the list can be retrieved without being filtered.

I have 2 forms for this form4 is the form that is displayed to the user and the Feeder form is a form that is hidden, its keep sending the signal to the server and if the server send list it then add the data to form4's ListView.

On Feeder I have this.
1 BackgroundWorker
1 Timer.

The BackgroundWorker tries to establish a connection with server, and if the connection is established then its call/start timer1 which is what sends the signal to the server and receive the response and based on the response its perform the appropriate action.

The timer interval is 600.

Here is the code to send the signal and receive the response.

 'First check if the user has filtered or not.

 If Form1.lblFilter.Text = Nothing Then
 'The user didn't filter.
 Try
 Dim serverStream As NetworkStream = clientSocket.GetStream
 Dim bufferSize As Integer
 Dim outStream As Byte() = System.Text.Encoding.ASCII.GetBytes("bringlist:<>" + "$")
 serverStream.Write(outStream,0,outStream.Length)
 serverStream.Flush()
 Dim inStream(10024) As Byte
 bufferSize = clientSocket.ReceiveBufferSize
 serverStream.Read(inStream,0,bufferSize)
 Dim returndata As String = System.Text.Encoding.ASCII.GetString(inStream)
 msg(returndata)
 Dim xlist() As String
 If returndata.Contains("List") Then
 xlist = returndata.Split(":")
 'I check if the listView is empty or not
 'Then if not I then loop through each item
 'To see if the new item hasn't been already added
 'This is to prevent having a long list
 'Of one thing.
 ' This is how I add data to listview on form4

 Dim data_Array(5) As String
 Dim itmData As ListViewItem
 data_Array(0) = xlist(1).Trim("<",">")
 data_Array(1) = xlist(2).Trim("<",">")
 data_Array(2) = xlist(3).Trim("<",">")
 data_Array(3) = xlist(4).Trim("<",">")
 data_Array(4) = xlist(5).Trim("<",">")
 itmData = New ListViewItem(data_Array)
 Form4.ListView1.Items.Add(itmData)

So that's how I go about this but the client freezes the first two sometimes do receive one record each and then freezes.

What am I missing/doing wrong here.
Sorry I know this post is too long now so will just stop there.

Well honestly I have never touched MySQL and don't know where to start with it.

What you mean my no backend to write at all.? Do you mean that the code I wrote on the server side or?

Well as I've said I've never used SQL so that's why I used Jet, and the database I'm using is MS Access.

So you mean this client hanging/freezing is produced by Jet/MS Access?

Another read of your code seems to tell my you are implementing your own username and password system. But it looks flawed from here. Hey, I only read it twice but it looks like it supports only one connection at a time. Moving to a classic MySQL server and a client app which you already are working on would put you into a very scaleable system.

You wrote you never used SQL but in your code, I see SQL.

The second and subsequent hanging seems to be built into your design. I offer only how I'd exit, not to redesign this one.

Ow sorry about saying I haven't used SQL I meant MySQL. Also if you say "it seems to allow one connection at a time" do you mean client connection or database connection?

I meant your code. And SQL is SQL. I learned that years ago. Once I have a SQL app, it's pretty easy to migrate to another SQL server. Now is a good time for you to learn SQL on MySQL.

So in short you install a SQL server (many use MySQL since it's free to use like this) then write your client app as you wish.

Since SQL systems have user logins that's less code for you to write.

Ok I hear you that I should change to SQL, but I think even this that I'm working on should work but maybe on a small scale. This is because I saw some billing systems(Internet cafe booking system) some use Access database but they do work where you find that some have maybe 20-25 computers all managed by one server, with this amount of clients its much possible that at least 2/3 clients will send the end command to a server at the same time, of which when the server receive this command for each client that sent it, the server should update the end time of that client to the database.

Those clients don't freeze when they send data at the sametime to the server. I think mine too shouldn't be freezing at such a small scale. I think there is something we are missing out here, because as I've said.

The first two clients on my case does receive the data but just receive one and freeze after that but the third client doesn't even get a sign data, its just freezes from start. I think I should also investigate deep into my code.

I suspect that maybe the course can be because of not closing the connections, but if I do close the connection I was getting error after sometime the system is running that I attempt to close a connection while other thread are busy, this could course data loss, that why I removed all the conn.close codes.

Ok just looked deep in to this and my finding is produced by several parts but what makes the clients freeze is the error that was thrown which says "ExecuteReader requires an open and available connection" which is thrown on this code:

 Try
 ConnDB()
 sql = "select * from transactions;"
 cmd = New OleDbCommandd(sql, conn)
 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 Do While dr.Read = True
 status = dr(4)
 Dim qqrefnumber As String
 If status = "Open" Then
 Dim dDate As String
 dDate = dr(3)
 If dDate >= DateTime.Today Then
 Dim xusername As String
 xusername = dr(0)
 price = dr(2)
 qqrefnumber = dr(5)
 Try
 ConnDB()
 Dim da As New OleDbDataAdapter(("select * from register where username = '" & xusername & "'"), conn)
 Dim dt As New DataTable
 da.Fill(dt)
 If dt.Rows.Count > 0 Then
 Dim Country As String
 Country = dt.Rows(0).Item("Country") & ""
 If Country = myFilter Then
 ' Send the data to client


 Else
 End If
 End If
 Catch ex As Exception
 MsgBox(ex.ToString) ' This is the second message I get.

 Finally
 ' I removed the conn.Close() trying to solve this problem.




 End Try
 Else
 ' Notify a client that there are no record meeting the date


 End If
 Else
 End If
 Loop
 Catch ex As Exception
 ' This is what courses the clients to freeze
 MsgBox(ex.ToString)
 Finally
 'Close connection or don't will still produce this error.
 End Try

The first error to appear is:

System.InvalidOperationException: ExecuteReader requires an open and available connection. The connection's current state is closed.

The second error appear is:

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)

This turns out to have nothing to do with the number of clients connected. I connected 1 client and it did receive data but after few seconds not closing that form that send request to server then this error is thrown.

Also now all three clients can now display data according to their filters but after few seconds not closing that form then these errors are generated to the server.

I'm not sure if this is because of the Timers interval maybe its too close but I didn't want a user to wait for so long to get the list.

This goes back to why if the clients sends a signal that prompt a server to access a database then the error ExecuteReader requires available and open connection yet I do open the connection before executing the sql command.

I think this has to do with sql command.

Also what I've found is that MS Access connection limit/restriction is 46 so I think I can work with that but I think I will have to do some sort pool where let's say there are 100 clients connected to the server but for only 46 is accessing the database then the rest can wait on a pool/queue when one of the 46 finishes then that will be disconnected/removed so that another client on the pool/queue can begin accessing the database.

This whole project does few things on a database, its insert/update records and retrieve then according to the status,date as well as according to that particular client's filter.

The main question is why do I get this error I'm getting while I do open the connection?

This turns out that the error was there ever since I started its just that I didn't notice till I attempt to run more then more then 1 instances.

Just found out this https://support.microsoft.com/en-us/kb/830133

Which I think does answer why I get the unspecified error message. That because with each signal to retrieve data sent by a client I attempt to open the connection so that I can work with it, but the client send this signal in every 600 interval which may course the MS Access database connection limit to be reached faster so I think I have to find a way to also remove the connection so that the limit won't be reached by just one client.

All this and you are repeating why folk move to a real SQL. The move was not painful for me the first time and you'll also get a server that will support many open sockets at once. Your current system will not scale and to fix it means you have to redesign it to never hold a connection open for more than a few milliseconds since it's not made for more than one user. With MySQL I had dozens of clients and never had to work hard to support them.

Ok I guess I should start changing. Won't changing force me to change my client source code? And also what I will need? Will I still need a server software that I've developed or the MySQL server also act as server too?

In my opinion the changes to the client will go much smoother than what you are trying to do now. I think your current path won't scale and if that's your other post you will always be fighting connection counts.

I suggest you dive into MySQL. All my apps that I used it made coding a joy compared to days before that. I get to do more design than actual code of the client app now.

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