-
Oct 6th, 2017, 08:39 AM
#1
Thread Starter
Lively Member
[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
-
Oct 6th, 2017, 12:03 PM
#2
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.
-
Oct 6th, 2017, 11:42 PM
#3
Re: How to update MySQL database with Datagridview?
Originally Posted by wes4dbt
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.
Originally Posted by wes4dbt
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.
-
Oct 6th, 2017, 11:50 PM
#4
Thread Starter
Lively Member
Re: How to update MySQL database with Datagridview?
Originally Posted by wes4dbt
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
-
Oct 7th, 2017, 12:04 AM
#5
Re: How to update MySQL database with Datagridview?
Originally Posted by DFlow
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.
Originally Posted by DFlow
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?
-
Oct 7th, 2017, 12:57 AM
#6
Thread Starter
Lively Member
Re: How to update MySQL database with Datagridview?
Originally Posted by jmcilhinney
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?
-
Oct 7th, 2017, 01:21 AM
#7
Re: How to update MySQL database with Datagridview?
Originally Posted by DFlow
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.
-
Oct 7th, 2017, 01:42 AM
#8
Thread Starter
Lively Member
Re: How to update MySQL database with Datagridview?
Ok then, If I retrieve the table again to the datatable right before updating the table?
-
Oct 7th, 2017, 01:55 AM
#9
Re: How to update MySQL database with Datagridview?
Originally Posted by jmcilhinney
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.
-
Oct 7th, 2017, 02:22 AM
#10
Thread Starter
Lively Member
Re: How to update MySQL database with Datagridview?
Originally Posted by jmcilhinney
I'm happy to be an adjunct to that but not a substitute for it.
Sorry, i didn't understand.
-
Oct 7th, 2017, 03:23 AM
#11
Addicted Member
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
-
Oct 7th, 2017, 03:30 AM
#12
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.
-
Oct 7th, 2017, 04:01 AM
#13
Thread Starter
Lively Member
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?
-
Oct 7th, 2017, 04:10 AM
#14
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|