Sub countR()
ConnDB()
Dim sql As String = "select COUNT (*) FROM register"
Dim cmd As New OleDbCommand(sql, conn)
dr = cmd.ExecuteReader()
While (dr.Read())
lblTotalR.Text = (dr(0).Tostring())
End While
End Sub
Tafadzwa_3 commented: Where is dr declared and how do you delcare dr+0
Just as Minimalist has said. Or another way is to now use the .Trim("br>") to remove the br> or use the replace as well like . Replace("br>","") at your code you posted.
I've saw that mysql.exe command line tool under bin folder. I opened it and typed help and it showed many options. What must I do first, followed by what with this?
Because when I tried to create a user it said something with privileges, when I tried creating a database it produced another error with ''@'localhost' which I think means there's no user to create database under.
Also when I installed mysql server it was installed inside the Canon Programs File folder. Canon is my Printer and I don't know why it didn't create its own folder as it uses the Canon folder.
Ok thank. So I guess I will need both of my apps which is client and server. The server app should/can be installed on the same machine with the MySQL server, and it will directly communicate with MySQL server for database access, accessing it with local domain/ip. Am I right?
Ok thanks. I've downloaded and installed MySQL server v5 32bit, its only has 2 command line apps, one named Shell and another is client. Is this the right tool and how can I create, locate database or there's another tool I need to download despite the .Net connector?
This is because when I try to run any or the command line that was installed, they just flush and closes.
I'm developing a client/server applications which communicates to each other, my project is a multi-thread based and I'm currently using MS Access and database but I have hit a very challenging problem as this was the wrong database to use at first due to its limitations.
My program uses signals(predefined messages) to perform the appropriate task to make a very simple example to login to your account you need to fill in the username and password and click login button.
The client will send: LOGIN:<txtusername.Text>:<txtpassword.Tex> using LOGIN as signal a server first check the signal and go to appropriate code for that signal so that it will perform the desired task. As to this sample, the application will try to login.
Now there is another part which keep sending the data retrieval signal after or in every 600 interval. So by doing so this courses the server to reach the maximum MS Access database connection limit by just one client, so I was adviced to move to MySQL server.
But upon doing some research I get confused more and more. First I think I need a proper explanation or clarification of the differences between:
MySQL MySQL Server MySQL Client
And to what each do, I mean its purpose and which one exactly I can use.
Upon the research I found out that MySQL consist of both Server and Client. Now if I download and use this …
Ow well I notice that on your insert code you want to add each CheckListBox item on its own fields. That can be achieved but not how you are trying.
This is a very simple example which is only limited to 3 as you said you have 3 items on a CheckListBox.
On that global variable, you will need to add other two variables. Or the other better way to do this so that even if you have many items. This method uses Array technique.
But enough with stuff that may confuse you.
You need to add two more variables as global variables.
Dim myItem2 As String
Dim myItem3 As String
'Under the Private Sub CheckListBox_CheckIndexChange()
'First check if the first variable has something on it.
If myItems = "" Then
'Because this is the first time click add what was clicked here.
myItems = CheckListBox1.SelectedItem.ToString
Else
'This variable has something so this means that this is not the first time click so now
'We need to check our second variable if its has anything on it.
If myItem2 = "" Then
'The variable has nothing, that means its now the second time the user click another item so go ahead add data to it.
myItem2 = CheckListBox1.SelectedItem.ToString
Else
'The variable has something so that means now the user has clicked the third item so let's go ahead and add it.
'First check if it has anything on it.
If myItem3 = "" Then
myItem3 = CheckListBox1.SelectedItem.ToString
End If …
If you want to save them into same field on a database then you will need to add a delimiter to separate them like John:Sam:Steve
Where in a CheckListBox its John Sam Steve
In any order so if you. So to do so you will need to do something like this myItems = myItems & ":" then when you retrieve it you can then split it on those delimiters so that you can be able to work with each if there's a need.
You need to declare that variable as global, meaning at this part.
Public Class
Dim myItems As String
Private Sub CheckListBox1_SelectedIndexChenge()
'Add that code I posted first here.
End Sub
Now on your code before attempting to add the data to the database you need to first verify if its null or not.
If myItem = "" Then
'The variable is null/has nothing on it.
'So don't attempt to insert this
Else
'Now this variable has some item so you can now add the insert code to add to database.
Ok. As of CheckListBox, its a big complicated to tangle it. But this will depend on how you are adding the data from the CheckListBox.
What I mean is that if you want to select more then 1 item and add them to the same column on a database you will need to add them to one variable so that when selecting the second time it won't replace the previously selected item text. Also if you will later on need to retrieve this and set the CheckListBox as per data retrieved, then I think you will need a separator as well. Because you want to select more then one item you can do this.
First thing to do is do a tracing of items by counting them because we get the items by index so double click the CheckListBox and add this code, also Declare Dim myItems As String as global variable so that you will be able to work with it on other controls.
Dim selectedIndex As Integer = CheckListBox1.SelectedIndex
If selectedIndex <> -1 Then
'Loop through items to get their check states.
For i As Integer = 0 To CheckListBox1.Items.Count -1
Dim checkstate As CheckState
checkstate = CheckListBox1.GetItemCheckState(i)
If (checkstate = checkstate.Checked) Then
' Now we know this item is checked so let's add it to our global variable.
myItems = myItems & CheckListBox1.SelectedItem.ToString
End If
Next
End If
I personally don't understand the question or problem here. Can you describe more into details as to what you want to achieve, how, and whats/where you currently stand. I see on your code that you want to insert data, but what is the problem exactly?
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?
I have a timer that in every 600 interval access the database and the database I'm using is MS Access database. The problem I have is that with this being said, the MS Access database has a limitation of 64 connections on each thread so this 600 interval does reaches the limitation, but I don't want that to happen.
This is what I have and I think its where I should be removing the connection. Code:
Public conn As OleDbConnection
Public Sub ConnDB()
Try
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="c:\test.mdb;Jet OLEDB.Database Password = 1234;")
conn.Open()
Catch ex As Exception
End Try
Now as you can see its says conn = New OleDbConnection which means when ever the timer fires a ConnDb() then a new connection is made, but keep in mind that doing this adds numbers of current connected connections and the limit is 64 so when ever the timer fires the new connection is created.
Now what I want is that when the accessing of the database is complete as per that command that was fired by the timer, I need to remove the connection so that one form/thread won't reach the limit and that I want to remove unused connections to prevent this.
Because if I remove these connections then that will means I can have exactly 64 threads running accessing the database.
I thinks this is clear of what I want to achieve, if there is any confusion you are more then welcome to …
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.
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.
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 …
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 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.
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.
The biggest problem is that I've moved from where I was staying to another province and here I don't have internet connection. My phone doesn't allow me to connect my laptop via bluetooth so that I can be able to access internet.
Long time ago I think late last year my computer was infected and I had installed a VM from Oracle and had a number of OS(VMs) installed one of them was with Win7 but I don't know what happened one day, when I open the VM it was blank as if its new there is now VM instance that has been installed. When I went on the folder where its keeps these instances I saw that they are still there and when you double click that orange file it opens the VM with the instance.
Now the problem is that ever since that happened, my VM instance especially the one that has Win7 do recognize the RW DVD but it says it has 0 free space. Every external device I try to connect its doesn't allow me to copy a file from an instance into a removable media.
I really need a solution to this problem. There is a very important file I want to copy. I no longer have it as backup, this file is on my VM win7 but can't copy it. Also USBs now do not show on My computer within this VM win7 instance.
Is there a solution with perhaps a step by step guide. I'm using Home Basic in both Guest and host machines so I can't do folder sharing.
Just solved this problem. I simply assigned the values from database to the response then instead of sending the response before the End Sub I simply send response after assigning the values to variables.
Hi guys. Well this turns out to be the client that's having a problem. Just retyped the code on the server side and I used this:
sql = "select * from mytable;"
cmd = New OleDbCommand(sql, conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Do While dr.Read = True
Username = dr(0)
response = "Shop:<" & Username & ">"
Loop
This works great. On the server side before response if I put MsgBox(Username) I'm able to get all the usernames as a message box. The response is just a string variable which I use to send formatted response to the client. The problem is that, the client only gets the last record. Meaning it won't get any usernames except the last username.
I use a BackgroundWorker to send request and receive response and assign it to global variables and I have a timer which keeps trying to add these global variables contents into ListView infinity. But my problem is that client only receive one data which is the last record on the database. I have 5 records but it won't get records from 1 to 4 only gets the 5th record which is the last record.
Well in your file you will need a separator that will separates the fields so that you will know how to identify fields(columns) in your text file. I will make an example of this. Since you said your text file will have 3 columns(Description, Part Number, Quantity) you need a way of separating this like this: sample|123|3 now in this example I used | as a separator and my code would be like this to read this text file, remember that you will read the fields into array.
If My.Computer.FileSystem.FileExist("c:\test.txt") Then
Dim sFile As String = IO.File.ReadAllText("C:\test.txt")
' The above will help to read all lines but will not be able to read or split each line, at least that what happened to me because of ReadAllText.
Dim f As New System.IO.StreamReader("c:\test.txt")
Dim lines() As String = Split(sFile, vbCrLf)
For Each line As string In lines
Dim x As String = f.ReadLine
' Now this is for giving the fields
If x <> String.Empty Then
Dim myArray(3) As String
' This is the array that will contain the fields
Dim xz As String = x.Split("|")
' Now we split or form a field on this identifier |
myArray = xz
' Now you can call or assign or do what ever you want with the data read. Suppose you had 3 text boxs.
txtDescr.Text = myArray(0)
txtPartNum.Text = myArray(1)
txtQuant.Text = myArray(2)
End If
Next
x.Close
That a sample of how to read the data and split it.
Ow also just to mention. The problem of client freezing, I just increased the timer interval and that solved my problem because now a timer fire or sends the request signal after 5000 interval instead of 100 interval because that was causing a server to send data in a very shot time in such a point that the signal was received while the server thread hasn't done the prev request and that was producing problems in opening the database.
The only problem I'm having or remains is that of retrieving all data from the entire database but only populate/retrieve data from certain columns.
Wildcards is actually not what I wanted, just saw some samples of wildcards and it not what I want. I think I'm lost in this. Don't know which to go with. Will try now the dataset stuff and see if it be of any help.
I didn't think that this would be so difficult. The only thing prevent completion of my project is this problem. In all samples, tutorials I've seen so far there is not even one that shows similar to what I want, those that are a bit close to it, their problem is that they don't show how you would assign or add what was retrieved from database to variables.
Just made some modifications on my code as follows:
ConnDB()
Dim da As New OleDbDataAdapter(("select username, regDate, birthdate, LastSeen from mytable;"), conn)
Dim dt As New DataTable
da.Fill(dt)
Try
If dt.Rows.Count > 0 Then
Musername = dt.Rows(0).Item("username") & ""
MregDate = dt.Rows(0).Item("regDate") & ""
Mbirthdate = dt.Rows(0).Item("birthdate") & ""
MlastSeen = dt.Rows(0).Item("lastSeen") " ""
Now this does give me the record on my client side but the problem is that its only give one record. Currently I have 5 records on my database, what I'm trying to do is get all these records data but only the selected columns.
I don't want to send out the complete person record but only a selected columns data I want to send.
I'm not sure if I have to use a For Each and if I have to use it. How could I apply to my scenario.
I've also looked through this https://www.commandprompt.com/ppbook/x5802 and found the plan that I've used on this code post. I saw another with select * from mytabe WHERE scondition = condition which would have perfectly answered/solved my problem if it had worked on me but it didn't. The client side just froze.
This would have answered because I would use the scondition where let say I want to get all the currently online users I would have done it like this: select * FROM myTable WHERE lastSeen = Online;" that would have solved my problem but this didn't work as I've stated that the …
I have a MS Access database that has 9 columns what I want to do is to retrieve all data from a database only on 5 columns.
I think I have to use the select * from data but I don't know how to select the columns I want to retrieve because I can't use the WHERE test = '" because I want to retrieve all database records but only selected columns.
A very good example of what I want to achieve is thisselect all usernames, birthdates, last_seen_status now this is just an example where a database has users records in the database you may have as many columns as possible including name, surname, sex, location, username, last_seen_status,birthdates, registration_Date and more but only need to retrieve data from 'username,birthdates,last_seen_status` from the database for all the users that had registered or is registered in this database.
Hope I didn't confuse you on this. I saw something with wildcards but was a bit complicated the problem is that I didn't know how to apply it to my scenario. I saw it here: http//www.codeguru.com/columns/vb/doing-data-extraction-with-visual-basic.net.htm
I think the possible way to tell if a server is up or not is to attempt to send some sort of signal like ACK message and wait for limited time to see if you will get a respond from server which could be a signal telling client that its can proceed sending data because its live.
Also if the client is not connected you could detect that by attempting to connect to the server, on error you know the server maybe down or unavailable for whatever reason.
Thanks everyone. Just woke up this morning and wrote this plan down then I managed to fix this. Here is what I wanted and how I accomplished it. I have 2 declarations that reads the file that because what I found was that in order to do what I wanted I shouldn't have used the ReadAllText but since the variable declared within this which is test works in getting or looping through all lines in a text file I than declared another variable I called cs and this is how I declared it.
Dim cs As New System.IO.StreamReader("c:\test.txt") then under my For Each line As String In lines I put this.
' Firstly because my text file also has some blank lines I need to deal with that first.
Dim cline As String = cs.ReadLine
If cline <> String.Empty Then
Dim sArry(12) As String
Dim xz As String() = cline.Split("*")
sArry = xz
' Now I can assign the variables correctly.
' For this demonstration use one at a time
' So that you will see what I wanted.
ListBox1.Items.Add("sArry(0) has: " & sArry(0))
ListBox1.Items.Add("sArry(1) has: " & sArry(1))
' And so on.
That's how I wanted but thanks for your efforts guys. Will mark this thread answered.
@Minimilist. Thanks, but that's not how I want it. The sample code I provided on my first post does that except those two top textboxes. The app I'm developing does not or won't be accessed by human so it won't have those tools like textboxes, listview or so.
I have a file that has many lines its a text file so what I'm trying to do for example, Please don't be confused by this its just an example I'm making for you to understand this let's say my text file is test.txt and its have 3 lines and each line has 13 fields. Now what I want is for each line have these fields read and assigned to variables correctly so that I can call these variables when I'm inserting this data to a database.
@Jim Oops I think I made a mistake when making that sample line but its ok, so that you won't be confused let's keep on working with 15 fields. I will correct it to the fields I have. As an example let's work with that 15.
Lol @Jim I used the 15 as per the sample you provided where u said suppose I had a ListView with 15 columns, so I didn't want to course confusion. But in my case its 13. As you can see on the sample line I provide on my first post. If you count all the fields within the * they are 13.
Dim myFirstNumber, mySecondNumber As Integer
Dim rate As Double
Dim Answer As Double
myFirstNumber = 10
mySecondNumber = 30
rate = 0.20
Answer = (myFirstNumber + mySecondNumber) * rate
@Jim. Well at the time of reply to this post I used a cellphone. But basically what I want is a bit similar to your first example where if I were to use the ListView with 15 columns I would have done it like that but now what I have is that I have the exactly 15 variables for the example you provided sake. Now I need a way to do this:
Dim MyFirstVariable As String
' This variable should contain field D1
' To achieve that is something like this
MyFirstVariable = Fields(0)
' That because Fields(0) will contain the
' First field before the * which is where
' Each field will be broken.
Dim MySecondVariable As String
' This variable should contain the field
' X584635 and that is retrieved like this
MySecondVariable = Fields(1)
Dim MyThirdVariable As String
' This Variable should contain the field
' test.one field and that is retrieved like
MyThirdVariable = Fields(2)
' And so on to 15.
The reason for this is that I want to add this to a database but before its added to a database I need to take Fields(1) and I have a function that tries to open a database record that contains this data, if its finds its then all the database records within that are retrieved so that they will be matched with the record that had been read from the file. Here is how that is done.
I'm trying to read a text file. This text file has a multiply lines and what I want is that I want to separate each line or should I say I want to split each line (chop it) so that I will be able to assign these fields to variables using the array.
What I first did is to get the lines then for each line I need to break it. The split I'm using the "*" as a separator.
Each line will produce/has 13 fields so I want to get these as a variable. Here is just 1 line example of how each line is:
So that is just one line. There are quite a number of lines in a file with this format but has different data as to this but the format is the same. Ow also some lines ends with # and also there are some blank lines as well within the file.
This is what I have:
If My.Computer.FileSystem.FileExists("C:\test.txt") Then
Dim test As String = IO.File.ReadAllText("C:\test.txt")
Dim lines() As String = Split(test, vbCrLf)
For Each line As String In lines
' Now I have the collection of lines now
' What I want to achieve is to split each
' Line into array so that I can use some
' Thing like MyArray(0), MyArray(1) etc
' This is what I have but I don't want it
' Like this.
Dim words As String() = line.Split(New Char() {"*"})
Dim data As String …
I think you got it the other way around. Well just the clarify this. These VB5.0 programs I did they communicate both they will be on different PCs and the server is on the side where the data gets in(arrive) from another PC on a different network, so on that other PC that's generates these data its only generates the data in a special or should I say in my format and on this server that we are talking about here that's the part that will look for a special file and when found.
This file will be opened (what I'm doing here is do send the data to another computer before the data is sent out to the user) I don't want the user nor the data generator to use the same PC so I had to transfer the file to another computer and that's the computer that will also send the data to clients(users).
Coming to clients all other software can be installable but not on the Generator and center computer, only from this computer where its sends or communicates with users can be installable and on users the software can be installed.
As of comma in a message, that's not possible in this because as I've said the message is formatted bÚy this server that we are talking about here and I used the * to separate the records on each line and used # at the end of each line to indicate that its the end …
