Results 1 to 14 of 14

Thread: [RESOLVED] How to update MySQL database with Datagridview?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2015
    Posts
    93

    Resolved [RESOLVED] How to update MySQL database with Datagridview?

    Hello guys, I have MySQL database and can retrieve and connect to the database and update with query but cannot update the database directly from the datagridview.
    getting the table to datagridview....
    Code:
    Try
                Connection.Open()
                Database_Command = New MySqlCommand
                Database_Command.CommandText = "SELECT * FROM `" & Table_Name & "`"
                Database_Adapter = New MySqlDataAdapter(Database_Command.CommandText, Connection)
                Users_Table_Command_Builder = New MySqlCommandBuilder(Database_Adapter)
                Users_Table_Table = New DataTable
                Users_Table_Table.Locale = System.Globalization.CultureInfo.InvariantCulture
                Database_Adapter.Fill(Users_Table_Table)
                Users_Table_Binding_Source = New BindingSource
                Users_Table_Binding_Source.SuspendBinding()
                Users_Table_Binding_Source.DataSource = Users_Table_Table
                Table.DataSource = Users_Table_Table
                Users_Table_Binding_Source.ResumeBinding()
                Connection.Close()
            Catch ex As Exception
            End Try
    update code LoL
    Code:
    Try
                Database_Adapter.Update(Users_Table_Table)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
    And the update command is giving me this error
    Name:  Untitled.png
Views: 1726
Size:  18.9 KB

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: How to update MySQL database with Datagridview?

    A couple of things,

    A DB concurrency error usually indicates that the data in the database table that you are trying to update with the dataadapter.update method has been modified by some other part of your program or some other process since the last time you called the dataadapter.Fill method.

    Can't really see what's going on with your DataGridView. Remember, if your using a bindinsource for the DGV datasource, you have to call the bindingsource.EndEdit method before calling the dataadapter.Update method.
    Last edited by wes4dbt; Oct 6th, 2017 at 08:20 PM.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to update MySQL database with Datagridview?

    Quote Originally Posted by wes4dbt View Post
    A DB concurrency error usually indicates that the data in the database table that you are trying to update with the dataadapter.update method has been modified by some other part of your program or some other process since the last time you called the dataadapter.Fill method.
    Just to clarify, a concurrency error is supposed to let you know that another user has modified the data that you're trying to save since you retrieved it. It does that because simply saving your data will overwrite their changes, which would be bad.

    If that's not what's happening then you have stuffed up your code somewhere such that the data you're saving has an original version that is different from what's currently in the database. That may happen because your app has made modifications to the database, as wes4dbt suggests, but it usually means that you have messed around inappropriately with the data. For instance, if you make changes, call AcceptChanges, then make more changes, then save. AcceptChanges will modify the original version of the data in the DataTable so it doesn't match what's in the database when you save. That's not the only way but it's a common one. You probably need to show us any code that accesses the DataTable.

    Quote Originally Posted by wes4dbt View Post
    Can't really see what's going on with your DataGridView. Remember, if your using a bindinsource for the DGV datasource, you have to call the bindingsource.EndEdit method before calling the dataadapter.Update method.
    That's not strictly true, although it's certainly recommended, because it ensures that any pending edit is committed to the DataTable. That certainly wouldn't cause a concurrency violation.

    By the way, an empty Catch block is pretty much always a bad thing. At the very least you should have a Debug.WriteLine call in there to show you the error message while debugging. Unless you know every exception that could be thrown there and know for a fact that you want to simply carry on silently if one is, you should not have an empty Catch block.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Aug 2015
    Posts
    93

    Re: How to update MySQL database with Datagridview?

    Quote Originally Posted by wes4dbt View Post
    A couple of things,

    A DB concurrency error usually indicates that the data in the database table that you are trying to update with the dataadapter.update method has been modified by some other part of your program or some other process since the last time you called the dataadapter.Fill method.

    Can't really see what's going on with your DataGridView. Remember, if your using a bindinsource for the DGV datasource, you have to call the bindingsource.EndEdit method before calling the dataadapter.Update method.
    Wow The bindingsource.endedit actually worked for tables with Primary Key. But the tables without primary key are giving this error

    Name:  Untitled 2.png
Views: 1525
Size:  16.6 KB

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to update MySQL database with Datagridview?

    Quote Originally Posted by DFlow View Post
    Wow The bindingsource.endedit actually worked for tables with Primary Key.
    I'm surprised at that. I'm not sure why it would but it's good that you've fixed that issue.
    Quote Originally Posted by DFlow View Post
    But the tables without primary key are giving this error
    Why do you have tables without primary key's in the first place? Regardless, you can't use a command builder to update or delete records without a primary because how could it possibly identify which record to update or delete? That's exactly what the PK is for, i.e. to uniquely identify a record. You can't even write your own SQL unless you have some other way to uniquely identify a record and, if you do have such a way, why haven't you made the columns involved the PK?

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Aug 2015
    Posts
    93

    Re: How to update MySQL database with Datagridview?

    Quote Originally Posted by jmcilhinney View Post
    Just to clarify, a concurrency error is supposed to let you know that another user has modified the data that you're trying to save since you retrieved it. It does that because simply saving your data will overwrite their changes, which would be bad.

    If that's not what's happening then you have stuffed up your code somewhere such that the data you're saving has an original version that is different from what's currently in the database. That may happen because your app has made modifications to the database, as wes4dbt suggests, but it usually means that you have messed around inappropriately with the data. For instance, if you make changes, call AcceptChanges, then make more changes, then save. AcceptChanges will modify the original version of the data in the DataTable so it doesn't match what's in the database when you save. That's not the only way but it's a common one. You probably need to show us any code that accesses the DataTable.



    That's not strictly true, although it's certainly recommended, because it ensures that any pending edit is committed to the DataTable. That certainly wouldn't cause a concurrency violation.

    By the way, an empty Catch block is pretty much always a bad thing. At the very least you should have a Debug.WriteLine call in there to show you the error message while debugging. Unless you know every exception that could be thrown there and know for a fact that you want to simply carry on silently if one is, you should not have an empty Catch block.
    The Code worked perfectly after I made all the tables to have primary key. But was wandering, I can't update the table IF other user is retrieving data, inserting row or updating table?

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to update MySQL database with Datagridview?

    Quote Originally Posted by DFlow View Post
    But was wandering, I can't update the table IF other user is retrieving data, inserting row or updating table?
    Yes you can, but the concurrency exception is there to prevent you over-writing other users' changes in the process. You should do some reading on optimistic concurrency and how it's implemented in ADO.NET. You need to decide what to do when a concurrency exception is thrown but, whatever that is, you're going to need to retrieve the data from the database as it is now. You probably want to then merge the current data with the user's changes and then save the result.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Aug 2015
    Posts
    93

    Re: How to update MySQL database with Datagridview?

    Ok then, If I retrieve the table again to the datatable right before updating the table?

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to update MySQL database with Datagridview?

    Quote Originally Posted by jmcilhinney View Post
    You should do some reading on optimistic concurrency and how it's implemented in ADO.NET.
    I'm happy to be an adjunct to that but not a substitute for it.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Aug 2015
    Posts
    93

    Re: How to update MySQL database with Datagridview?

    Quote Originally Posted by jmcilhinney View Post
    I'm happy to be an adjunct to that but not a substitute for it.
    Sorry, i didn't understand.

  11. #11
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: How to update MySQL database with Datagridview?

    The tables without a primary key don't create a update/delete rule when you use the importing of a database with importing a Datasource wizard of visual studio
    you can switch of concurencey in the dataset window rightclick on the tableadapter and then configure/advanced options and uncheck use optimistic concurrency

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to update MySQL database with Datagridview?

    I told you that you should do some research. You didn't. I'm not going to just tell you what to do because then you'll just blindly follow the instructions to solve this one specific issue but not understand the subject in general and then just have to ask more questions later to solve other related issues. If you learn about the subject in general now then you will likely be able to solve this issue and any related issues that arise in the future. I expect you to make an effort to find the relevant information for yourself rather than just wait for me to give it to you. If you do that and there's something specific that you don't understand or doesn't work then I'm happy to help with that if I can but I'm not here so that you don't have to bother using the copious amounts of information that are already out there.

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Aug 2015
    Posts
    93

    Re: How to update MySQL database with Datagridview?

    Yes I understand and will do my researches, but really don't know where to look. If you can point me to a book or something like that?

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to update MySQL database with Datagridview?

    I said:
    You should do some reading on optimistic concurrency and how it's implemented in ADO.NET.
    If you don't know how to pick the keywords out of that and search for them on the web then you have greater issues.

Posting Permissions

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



Click Here to Expand Forum to Full Width