mysql updated in datagridview vb.net 2005

[Richard Hayes]

I just started with mysql last friday. So I do not even know enough to be dangerous yet, so please go easy on me.

I was able to make a database with Msql ver 5.. I named the database James and made a table called carol. My first field was my primay int field. The other field var char field. I was able to use SQLyog to view my work. I was so proud of myself until today.

I stumbled onto some code example that pulled a MS Sql data into a windows form housed in a datagridview. You can make changes and it updates the data base. So I had the bright idea to copy the code (which is vb .net 2005) to my own windows app (made with vb .net 2005).

I set about making the form which had the datagridview onit and a refresh button. Just like the one I saw online. When I built the application the datagridview populated with my test data from my James database no problem. I was in heaven until I made a change in the datagridview and to my great dismay My database was not updataed like the example I tried to modify.

I have looked at many other example and I am too dumb to figure out from them what I have done wrong. I do not have to have this for many project or anything like that its just I am going to loose a lot of sleep till I figure it out.

The example app had the data base as part of the solution instead of bieng an external databas like mine-- my guess is that may be the problem or I need more code for the datagridview to work with Mysql.

here is the original code I looked at:

Public Class Form1 
    Dim UpdatePending As Boolean = False

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
        ' standard initial load of the dataset from the source database 
        Me.ExampleTableAdapter.Fill(Me.ExampleDataSet.Example) 
    End Sub 

 Private Sub ExampleBindingSource_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles ExampleBindingSource.ListChanged 
        ' Whenever there is an update, note that a change is pending. 
        ' 
        ' ListChanged does not fire when moving within a row, so this will not 
        ' mark updates until done with the row.  (Here "done" could mean moving 
        ' to another row or closing the form.) 
        If Me.ExampleDataSet.HasChanges Then 
            Me.UpdatePending = True 
        End If 
    End Sub 

 Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated 
        ' The RowValidated event occurs after BindingSource_*Changed operations, which 
        ' makes it a good place to update our source database.   
        ' However, this event fires at a number of times when we don't have pending updates. 
        ' That's why we need the UpdatePending indicator to tell us whether to do anything. 

        ' If we have an update pending, copy it to the source database 
        If UpdatePending Then 
            Me.ExampleTableAdapter.Update(Me.ExampleDataSet.Example) 
            Me.UpdatePending = False 
        End If 
    End Sub 

    Private Sub ReFillButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReFillButton.Click 
        ' for demonstration / testing 
        Me.ExampleTableAdapter.Fill(Me.ExampleDataSet.Example) 
    End Sub 

End Class

Now here is my code:

Public Class Form1 


    Dim UpdatePending As Boolean = False 

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
        'TODO: This line of code loads data into the 'DataSet1.carol' table. You can move, or remove it, as needed. 
        Me.CarolTableAdapter.Fill(Me.JamesDataSet1.carol) 
        'TODO: This line of code loads data into the 'JamesDataSet.carol' table. You can move, or remove it, as needed. 
        Me.CarolTableAdapter.Fill(Me.JamesDataSet1.carol) 

    End Sub 

    Private Sub CarolBindingSource_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) 
        If Me.JamesDataSet1.HasChanges Then 
            Me.UpdatePending = True 
        End If 
    End Sub 
    Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) 
        If UpdatePending Then 
            Me.CarolTableAdapter.Update(Me.JamesDataSet1.carol) 
            Me.UpdatePending = False 
        End If 
    End Sub 

    Private Sub ReFillButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReFillButton.Click 
        Me.CarolTableAdapter.Fill(Me.JamesDataSet1.carol) 
    End Sub 
End Class

The best that I can tell I have every thing the same except the names for the binding and source..


I hope I have provided enough information

Thank you

 

[Arg81]

1) Remove one of the fills from your Form1_Load, you are filling the same dataset twice.

2) I think your problem is with the CarolBindingSource_ListChanged and _RowValidated Code.

How I would do it is to have another button called btnUpdate. Put it's code so that;

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click 
       
      If me.JamesDataSet1 HasChanges Then

              Me.CarolTableAdapter.Update(Me.JamesDataSet1.carol) 
    
       End If
End Sub

...remove the _ListChanged and _RowValidated code, make some changes to the data in the grid, click the update button and see whether that makes the changes to your database.

HTH