Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Cool Unanswered: voting display system cannot refresh

    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!
    Attached Thumbnails Attached Thumbnails Untitled-1.jpg  

  2. #2
    Join Date
    Sep 2010
    Posts
    3

    I also had that problem

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

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

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

    If you look at the code below:


    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
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Aug 2010
    Posts
    6

    Not Yet Sorted out

    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..

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Sep 2010
    Posts
    3

    Sorted the hard way

    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

  7. #7
    Join Date
    Aug 2010
    Posts
    6
    Hi Boating.. do you mind sharing with me how does it been solved? my email is it_ritz@hotmail.com

    Thanks!

  8. #8
    Join Date
    Aug 2010
    Posts
    6
    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..

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    As mentioned here: http://www.dbforums.com/visual-basic...t-refresh.html

    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.
    Last edited by Teddy; 09-29-10 at 11:53.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Oct 2010
    Location
    usa
    Posts
    1
    I had a similar problem ''SweeKee''

  11. #11
    Join Date
    Aug 2010
    Posts
    6
    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..

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by SweeKee View Post
    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..
    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
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •