locked
Binding MySQL Data With DataGridView RRS feed

  • Question

  • I have data being bound to a datagridview (DG_Bids) using the following code...

    Private Sub FillBids()
            If Not con.State = ConnectionState.Open Then con.Open()
            Try
                ds1 = New DataSet
                Dim SQL As String = "SELECT * From Bids Order By BidNumber Desc"
                cmd = New MySqlCommand(SQL, con)
                adp1 = New MySqlDataAdapter(cmd)
                adp1.Fill(ds1, "Bids")
                DG_Bids.DataSource = ds1
                DG_Bids.DataMember = "Bids"
         
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            con.Close()
        End Sub

    ..the data is displayed correctly, no issues. Now I need for that data to be updated to my MySQL data base after it is edited in the datgridview. To do so, I am using this code...

    Private Sub DG_Bids_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Bids.CellEndEdit
            con.Open()
    
            Dim cmdbuilder As New MySqlCommandBuilder(adp1)
            Dim i As Integer
            Try
                i = adp1.Update(ds1, "Bids")
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            con.Close()
        End Sub

    ...which has always worked for me using OleDB but does not seem to work using MySQL. I am positive that I am connected correctly because my data is displayed correctly and I also am positive that my edits are updated from the datagridview to the values because I have checked them when the CellEndEdit has been fired, but the actual update back to my databasae does not occur.

    What do I need to do?

    Thank you,


    Hal Roenick

    Friday, July 17, 2015 4:08 PM

Answers

  • Cor et al,

    Holy cow it worked! I actually have 4 grids on my dialog, I added a button called "BtnUpdate" behind one of the grids and added this code...

    Private Sub DG_Bids_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Bids.MouseLeave
            DG_Bids.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub
        Private Sub DG_Items_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Items.MouseLeave
            DG_Items.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub
        Private Sub DG_Tasks_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Tasks.MouseLeave
            DG_Tasks.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub
        Private Sub DG_TaskItems_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_TaskItems.MouseLeave
            DG_TaskItems.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub

    ...and there is no code in the click event...

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

    ...and it works! Because my code to update the database is in the Row_Validated event, the button click causes that to occur so I don't need the code to update each grid...

    Private Sub DG_Bids_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Bids.RowValidated
            UpdateTable(ds1, adp1, DG_Bids, "Bids")
        End Sub
    
        Private Sub DG_Items_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Items.RowValidated
            UpdateTable(ds2, adp2, DG_Items, "BidItems")
        End Sub
    
        Private Sub DG_Tasks_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Tasks.RowValidated
            UpdateTable(ds3, adp3, DG_Tasks, "BidItemTasks")
        End Sub
    
        Private Sub DG_TaskItems_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_TaskItems.RowValidated
            UpdateTable(ds4, adp4, DG_TaskItems, "MaterialsForTasks")
        End Sub

    ...ill mark this as answered, I hope others can find it!

    Thanks a bunch!


    Hal Roenick

    • Marked as answer by VCADDCAMM Thursday, July 30, 2015 11:19 AM
    Thursday, July 30, 2015 11:19 AM

All replies

  • You have to call EndEdit function of DataGridView before updating the database. This function will commit the changes made in the cell.

    sivanitha

    Friday, July 17, 2015 5:50 PM
  • Sivanitha,

    Thank you, but if you look closely above, my code to update the database is in the CellEndEdit event of the DataGridView (called DG_Bids). I have put (and deleted) test code in that event to check to make sure the "values" of the cell is updated to the Grid and they are. The problem is that they are not being updated to my database. As I mentioned, I have used this code using OleDB but the SQL functions do not seem to be working or the DataGridView is not updating the dataset correctly, I cannot think of a way to tell which one might be broken.

    Thanks again,

    Hal


    Hal Roenick

    Friday, July 17, 2015 7:31 PM
  • I still have no answer for this question but have some things to add...I have found that the same problem exists in OLE too. The code I posted works if the user edits a cell then moves to another cell that is not in that row (or record), if the user simply leaves the datagridview or hits "enter" or moves to a cell in the same row, the record never gets updated.

    The problem seems to be that a row change after editing is the only action that will update the dataset with the information from the datagidview. What if there is only 1 row in the datagridview? How can you update values if you have no other row to move into? There must be a way to update the dataset with the information from the datagridview whenever you need it to happen.

    Please help!


    Hal Roenick


    • Edited by VCADDCAMM Wednesday, July 29, 2015 11:41 AM Added Information
    Wednesday, July 29, 2015 11:38 AM
  • Wednesday, July 29, 2015 1:52 PM
  • Paul, Thank you for your suggestion, but I do not >>>AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter. AcceptChanges<<< as suggested by the article. In addition, I do not have a connection to the database established in my solution.

    I created a VERY simple application that reads a MySQL database and reads all of the table names and puts them into a menu list on a form. You can then select a table from the list and it is bound to a datagridview in form. At this point I can edit any cell in the data grid and if after I edit the field move to a different cell in a different row, the update occurs, but if I finish the edit any other way, the update does not occur.

    I could easily send this project to anyone willing to try it to see the problem, you would just need a MySQL database and set the following strings for the connection to be made.

    Dim db_server As String = "server"

    Dim db_name As String = "databasename"

    Dim db_username As String = "username"

    Dim db_userpassword As String = "password"


    Hal Roenick

    Wednesday, July 29, 2015 2:14 PM
  • I don't currently have MySQL installed on my machine so I cannot test the code. I've usually found that third-party providers tend to operate a little differently than Microsoft's so I would not base your code upon how it works with System.Data.OleDb. Here is an example from the below link:

    http://solibnis.blogspot.com/2013/02/connecting-mysql-table-to-datagridview.html

    Converted from C#:

    Private Sub dataGridView1_RowValidated(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
    
    Dim changes As DataTable = (CType(dataGridView1.DataSource, DataTable)).GetChanges()
    If changes IsNot Nothing Then
    	Dim mcb As New MySqlCommandBuilder(mySqlDataAdapter)
    	mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand()
    	mySqlDataAdapter.Update(changes)
    	CType(dataGridView1.DataSource, DataTable).AcceptChanges()
    End If
    
    End Sub


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 29, 2015 2:44 PM
  • I still have no answer for this question but have some things to add...I have found that the same problem exists in OLE too.

    Did you try what Sivan wrote. The data is only pushed to the datasource with a row commit of the user (a row change) or by an end edit. I see nowhere in your code that you did that.

    Do you really think that because of the fact there was another person who did it wrong like you, nobody is making programs like yours (but than much bigger)?

    Hundred of thousands have seen your problem and solved it.

    And use another event to handle the code. Because the edit mode does not stop if you are not stopping it programmatically.


    Success
    Cor





    Wednesday, July 29, 2015 2:44 PM
  • Try adding this code to commit the changes prior to updating the table:

    Sub DG_Bids_CurrentCellDirtyStateChanged( _
        ByVal sender As Object, ByVal e As EventArgs) _
        Handles DG_Bids.CurrentCellDirtyStateChanged
    
        If DG_Bids.IsCurrentCellDirty Then
            DG_Bids.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End If 
    End Sub 


    Programming is easy, understanding how is not.


    • Edited by PEng1 Wednesday, July 29, 2015 3:43 PM
    Wednesday, July 29, 2015 3:42 PM
  • Paul and Cor,

    So far, from what I have found, MySQL functions require a DataSet as opposed to a DataTable, so I hope that is OK. I changed Paul's suggested DataTable to a DataSet and everything seems to function, but the functionality is still the same. If after I make an edit, I click into another row, the update occurs, but if I click into another cell on the same row, the update does not occur. What if there is only 1 record in the datagridview? I have no way to change rows so I cannot update the edit to the database. Is there a way to force the row to validate?

    Cor, if you glance below, as you can see I am calling "EndEdit" on the Mouse Leave event in an attempt to make my changes stick, but that is not making the Row Validated event occur.

    Private Sub DataGridView1_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.MouseLeave
            DataGridView1.EndEdit()
        End Sub
    
    
        Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
            MyCon.Open()
            Dim changes As DataSet = (CType(DataGridView1.DataSource, DataSet)).GetChanges()
            Dim i As Integer
            If changes IsNot Nothing Then
                Dim mcb As New MySqlCommandBuilder(Myadp1)
                Myadp1.UpdateCommand = mcb.GetUpdateCommand()
                i = Myadp1.Update(changes, sMyData)
                MsgBox(CStr(i) & " Records Updated")
                CType(DataGridView1.DataSource, DataSet).AcceptChanges()
            End If
    
            MyCon.Close()
        End Sub


    Hal Roenick

    Wednesday, July 29, 2015 3:42 PM
  • PEng1,

    Thank you, I had tried that and added your code and I can see it being hit, but it does not seem to change the functionality of the datagridview not updating the database. It does not seem to cause a Row Validated to occur.

    Thanks again,


    Hal Roenick

    Wednesday, July 29, 2015 4:26 PM
  • Do you happen to see the MySQL database in your project (Solution Explorer)?

    I'm not sure whether you checked the #2 issue from the link I previously posted, but I'm beginning to wonder.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 29, 2015 4:39 PM
  • Paul,

    A couple of things...I changed to a more simple project so testing is easier. From my original Post what was DG_Bids is now DataGridView1.

    Another discovery...If I close the dialog by using the "X", the Row Validated event is thrown and the updates are made to the database. Also, your code helped because now, hitting "Enter" does casue the update to occur...THANK YOU!

    So now I know that whatever it is that makes the datagridview decide to update its "DataSource" with the edits from the Grid seems to be thrown in 2 events...1) When you change rows by clicking, when you press "Enter" and when you close the dialog where the datagridview is located.

    And no...In my test project, I do not have a direct connection to the database.

    I am going to see if I can send keys strokes of "Enter" to the datagridview if the user move the mouse out of it.


    Hal Roenick


    • Edited by VCADDCAMM Wednesday, July 29, 2015 7:30 PM Brain Fart
    Wednesday, July 29, 2015 4:42 PM
  • Paul,

    I think I can report that your answer is working correctly for the most part. Most events like the user moving from one datagrid to another seem to complete the update, but I have found that some events such as selecting a menu item does not cause the update to occur. This scares me a bit because it can give th euser a way around the update occurring, and if you have a suggestion for that, I would appreciate it.

    Thanks Again!


    Hal Roenick


    • Edited by VCADDCAMM Wednesday, July 29, 2015 7:29 PM Brain Fart
    Wednesday, July 29, 2015 7:15 PM
  • Have I missed something?  I do not see any post by a Ken?  

    It would seem that the simple solution to this is to call EndEdit on the data grid prior to doing any work on the data contained within, including re-binding the data grid to a different table.



    Programming is easy, understanding how is not.

    Wednesday, July 29, 2015 7:27 PM
  • If the code from the Event is working then you can move it to it's own Sub and then call it from both the DataGridView Event and your Menu item event. I just converted the code from the example but you should be able to move it to a separate Sub if it needs to be invoked from different places.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 29, 2015 8:26 PM
  • Have I missed something?  I do not see any post by a Ken?  



    Shhhh...that's my real name. ;-)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 29, 2015 8:28 PM
  • Paul,

    Yes, I did create a sub routine (see below) for the update that works well with all of my datagrids, but in the code below, the variable "changes" equal "Nothing" if I call it from something like a Menu Click or CellLeave even after calling EndEdit on the grid. As I stated before, many events like clicking in another grid, pressing "Enter" or changing rows in the grid makes "changes" in the code below equal to the edits in the grid, but it does not happen with all events!

    I am still looking for the code that always sets the dataset bound with a datagridview equal to the edits in the datagridview.

    Public Sub UpdateTable(ByRef ds1 As DataSet, ByRef adp1 As MySqlDataAdapter, ByRef MyGrid As DataGridView, ByVal sTable As String)
            con.Close()
            con.Open()
            Dim i As Integer
            
            Try
                Dim changes As DataSet = (CType(MyGrid.DataSource, DataSet)).GetChanges()
                If changes IsNot Nothing Then
                    Dim mcb As New MySqlCommandBuilder(adp1)
                    adp1.UpdateCommand = mcb.GetUpdateCommand()
                    i = adp1.Update(changes, sTable)
                    MsgBox(CStr(i) & " Records Updated")
                    CType(MyGrid.DataSource, DataSet).AcceptChanges()
                    MyGrid.Refresh()
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
            con.Close()
        End Sub


    Hal Roenick

    Wednesday, July 29, 2015 8:55 PM
  • Hal,

    You simply does not look at replies which handles your problem. Why do you ask it. Has somebody ordered you to do it and then you tell the answer was not there?

    But be happy nobody here cares if you can tell the answer was not there. But for that there was no reason to state this question in this forum. Simply had done nothing because you refuse to try the real answers. 

    The first reply you got you still not have done, something which you can find endless times on Internet as the answer for your problem.

    You are not the first one asking this you know. But others took the solution and not what did sound them the best. 


    Success
    Cor



    Wednesday, July 29, 2015 10:32 PM
  • Cor,

    I am sorry if I am upsetting you somehow. I have invoked the EndEdit in a number of places and have mentioned such. For example...

    Private Sub DG_Bids_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Bids.MouseLeave
            DG_Bids.EndEdit()
        End Sub

    ...but it does not solve my issue. I have tried it in all places I can think of and it does not do what I need.

    Thank you for your consideration.


    Hal Roenick

    Wednesday, July 29, 2015 11:01 PM
  • Cor,

    I am sorry if I am upsetting you somehow. I have invoked the EndEdit in a number of places and have mentioned such. For example...

    Private Sub DG_Bids_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Bids.MouseLeave
            DG_Bids.EndEdit()
        End Sub

    ...but it does not solve my issue. I have tried it in all places I can think of and it does not do what I need.

    Thank you for your consideration.


    Hal Roenick

    Hal,

    Put a button on your form and use a click. The event you are using now is meant that the mouse should go from the datagridview to another area. But it is not updating anything.

    Therefore put under that click event the code of the update. Including that endedit. 

    The event which you currently use to update is the strangest one for that I ever saw.

    If it is then still not working create a bindingsouce between it. That was once created to help with this problem but more meant for framework 2.0.


    Success
    Cor


    Thursday, July 30, 2015 8:18 AM
  • Cor,

    I agree that my example is odd, but as programmers, are we not supposed to attempt to anticipate anything a user might do and make sure the program works correctly?

    My example is for real...I am writing a program that creates Bid proposals for my company. If the user changes a cell which affects the price then needs to "print" that proposal, he may go directly from changing a number to the menu to print the document which is created by use of the database.  If the grid looks correct, but the data is not updated, the proposal could be wrong and my rear end in the hot seat! So, to make sure the database is correct, when the users mouse leaves the grid, I want to update the database.

    Your suggestion of a binding source is interesting. I have read about them but was unable to make it work using MySQL. Could you explain?

    If I may rant for a moment...Personally, I cannot believe anyone has to go through this to solve a very simple issue...something somewhere deep in the code of a datagridview there is something that updates a grids data source to equal the values in its cells, I don't understand why that trigger is such a secret and so hard to find. To me it simple....a "TriggerBinding" or "UpdateBoundDataset" method would work for me! I would even create it if I knew how, but that is why I am here, to learn. 

    Hey, I just thought of something...if you say I could create an "update" button, does that mean a "button click" is one of the actions (unlike EndEdit and a MouseLeave event) which will cause the update I am looking for? I could then have a hidden button and make the click public and call that on my mouseleave? Might that work?

    Thanks again.  


    Hal Roenick

    Thursday, July 30, 2015 10:58 AM
  • Cor et al,

    Holy cow it worked! I actually have 4 grids on my dialog, I added a button called "BtnUpdate" behind one of the grids and added this code...

    Private Sub DG_Bids_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Bids.MouseLeave
            DG_Bids.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub
        Private Sub DG_Items_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Items.MouseLeave
            DG_Items.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub
        Private Sub DG_Tasks_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_Tasks.MouseLeave
            DG_Tasks.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub
        Private Sub DG_TaskItems_MouseLeave(ByVal sender As Object, ByVal e As System.EventArgs) Handles DG_TaskItems.MouseLeave
            DG_TaskItems.EndEdit()
            Me.BtnUpdate.PerformClick()
        End Sub

    ...and there is no code in the click event...

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

    ...and it works! Because my code to update the database is in the Row_Validated event, the button click causes that to occur so I don't need the code to update each grid...

    Private Sub DG_Bids_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Bids.RowValidated
            UpdateTable(ds1, adp1, DG_Bids, "Bids")
        End Sub
    
        Private Sub DG_Items_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Items.RowValidated
            UpdateTable(ds2, adp2, DG_Items, "BidItems")
        End Sub
    
        Private Sub DG_Tasks_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Tasks.RowValidated
            UpdateTable(ds3, adp3, DG_Tasks, "BidItemTasks")
        End Sub
    
        Private Sub DG_TaskItems_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_TaskItems.RowValidated
            UpdateTable(ds4, adp4, DG_TaskItems, "MaterialsForTasks")
        End Sub

    ...ill mark this as answered, I hope others can find it!

    Thanks a bunch!


    Hal Roenick

    • Marked as answer by VCADDCAMM Thursday, July 30, 2015 11:19 AM
    Thursday, July 30, 2015 11:19 AM