Hi Everyone,

I had post the similar post on the visual basic, i decide to post here too as i am worry it doesn't work because of My SQL database.

I had write a simple program using visual basic 2008 express edition for displaying the voting. I am using mySQL as my database, the program have to retrieve data by a SQL statement from my database. Whenever i click on start button, it should be refresh and retrieve the lastest data in mySQL database.

My problem is that when i load the program, so called a "formload", and click on the start button, it will display the correct data from database, but when i click the button second time, it does not refresh and display the latest data.

This program is done by me who is a newbie in visual basic, this is the only problem i met, i have to settle it in 1 weeks time. But until now i still cant find the solution, anyone please help me on that?

My code for my program start button as below

Private Sub btn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click
End
End Sub

Private Sub btn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnstart.Click
Dim MysqlConn As MySqlConnection

MysqlConn = New MySqlConnection()


' Define the SQL to grab data from table.
SQL = "SELECT count('content') FROM loglog where content = 'M1'"



'Connection String
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=root;" _
& "password=;" _
& "database=vgsmdb"

' Try, Catch, Finally
Try
MysqlConn.Open()

myCommand.Connection = MysqlConn
myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)

DataGridView1.DataSource = myData


Catch myerror As MySqlException
MessageBox.Show("Cannot connect to database: " & myerror.Message)
Finally
MysqlConn.Close()
MysqlConn.Dispose()


End Try

' Define the SQL to grab data from table.
SQL2 = "SELECT count('content') FROM loglog where content = 'M2'"

'Connection String
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=root;" _
& "password=;" _
& "database=vgsmdb"

' Try, Catch, Finally
Try
MysqlConn.Open()

myCommand.Connection = MysqlConn
myCommand.CommandText = SQL2

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData2)

DataGridView2.DataSource = myData2




Catch myerror As MySqlException
MessageBox.Show("Cannot connect to database: " & myerror.Message)
Finally
MysqlConn.Close()
MysqlConn.Dispose()

End Try

' Define the SQL to grab data from table.
SQL3 = "SELECT count('content') FROM loglog where content = 'M3'"

'Connection String
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=root;" _
& "password=;" _
& "database=vgsmdb"

' Try, Catch, Finally
Try
MysqlConn.Open()

myCommand.Connection = MysqlConn
myCommand.CommandText = SQL3

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData3)

DataGridView3.DataSource = myData3



Catch myerror As MySqlException
MessageBox.Show("Cannot connect to database: " & myerror.Message)
Finally
MysqlConn.Close()
MysqlConn.Dispose()

End Try

' Define the SQL to grab data from table.
SQL4 = "SELECT count('content') FROM loglog where content = 'F1'"

'Connection String
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=root;" _
& "password=;" _
& "database=vgsmdb"

' Try, Catch, Finally
Try
MysqlConn.Open()

myCommand.Connection = MysqlConn
myCommand.CommandText = SQL4

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData4)

DataGridView4.DataSource = myData4


Catch myerror As MySqlException
MessageBox.Show("Cannot connect to database: " & myerror.Message)
Finally
MysqlConn.Close()
MysqlConn.Dispose()


End Try

' Define the SQL to grab data from table.
SQL5 = "SELECT count('content') FROM loglog where content = 'F2'"

'Connection String
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=root;" _
& "password=;" _
& "database=vgsmdb"

' Try, Catch, Finally
Try
MysqlConn.Open()

myCommand.Connection = MysqlConn
myCommand.CommandText = SQL5

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData5)

DataGridView5.DataSource = myData5




Catch myerror As MySqlException
MessageBox.Show("Cannot connect to database: " & myerror.Message)
Finally
MysqlConn.Close()
MysqlConn.Dispose()

End Try

' Define the SQL to grab data from table.
SQL6 = "SELECT count('content') FROM loglog where content = 'F3'"

'Connection String
MysqlConn.ConnectionString = "server=localhost;" _
& "user id=root;" _
& "password=;" _
& "database=vgsmdb"

' Try, Catch, Finally
Try
MysqlConn.Open()

myCommand.Connection = MysqlConn
myCommand.CommandText = SQL6

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData6)

DataGridView6.DataSource = myData6


Catch myerror As MySqlException
MessageBox.Show("Cannot connect to database: " & myerror.Message)
Finally
MysqlConn.Close()
MysqlConn.Dispose()

End Try

Btnsave.Enabled = False
btnedit.Enabled = True

End Sub


I also attach a copy of my GUI

Your helps is highly appreciated and thanks!

I had a similar problem to yours, how did you manage to get it sorted out????

Hi,

I have looked through your code and may have found something:

If you look at the code below:

The code has good exception handling and will close the connections in the event of an exception being raised. If no exception exists then the connection remains open. Yet you attempt to reconfigure the connection and open again. Either keep the connection open and reuse the MysqlConn or close and dispose after each try/exception.

Hi Boating, i am still unable to sort it out.. If you had sort out may you please post a reply on how you do it? 100 of thanks!!

it-iss.com, what you meant is to removed all the exception and connection dispose and close? So that the connection will be open till the end of line?

I had tried that but it's still giving me the same problem..

I have some experience of developing in VB but this was quite a while ago.

What I am saying is that you either close each connection after you use them (which in this case would be inefficient) or keep the connection open and simply keep reusing this and at the very end of the script close the connection to the database. This is how I develop in PHP and I am sure that this is also permitted in VB.

The code from what I can read is opening a connection each time without previously closing the first connection.

What I suggest you do is to comment out from "SQL2 = ..." down to the last End Try statement. Include a Close and Dispose at the end before the last statements:

Btnsave.Enabled = False
btnedit.Enabled = True

and test this out.

If this works it means one of two things: a) the close and dispose is needed at the end of the procedure or b) that you will need to use unique names for the different elements that you are using i.e. myCommand.

Yes I did manage to sort it out! In the ined I had to hire someone through oDesk to do it for me! Sometimes its better to delegate these task rather than banging your head for hours

Hi Boating.. do you mind sharing with me how does it been solved? my email is it_ritz@hotmail.com

Thanks!

Hi,

I heard that i can do some configuration from mySQL database by setting some interval for refresh. I had research online the whole day but i could not find any information for this..

As mentioned here: voting display system cannot refresh

I didn't see anything in there that would cause an insert or update, therefore the recordset would be identical unless some external DB call was made. Obviously you can't display changes that never happened...


ProTip: If you don't answer the follow up questions after posting a thread, you are far less likely to receive accurate assistance with your issue. We can only do so much with limited information.

I had a similar problem ''SweeKee''

Hi Somsak1.. what application you are using for the developing of this system? Are you using VBA for that? BTW, i am still unable to solve the problem.. I really cant figure out the problem..

you cannot get MySQL to update in such a manner, however you can get a VB/VBA program to do this by user a form timer.

as I read it
your form is not refreshing with new data
so its going to be either
you are not requesting the data
you are getting the data but doing nothing with it
you are trying to uopdate the form but because of the design its not doign that.

so that could be somethign to do with your recordset type.
it could be something as simple as issuing a me.recordset.refresh command


persoanlly Im not going to wade through a whole pile of VB to find what the culprit is. in my books you need to do more to identify why what you expect to happen doesn't
thats going to involve putting break/watchpoints on your code then stepping through each but to find out if what you expect to happen does. having done that (and hopefully indentified where the problem lies) by all mean come back