MYSQL da.update(dt) doesn't work after online changes

[mczupryniak]

Hi everyone

I have windows form application connected to mysql database. To menage this base I use mysql.net connector.

After I make some changes in datatable in my app, I use rowChanged handler to store changes in mysql.

Here is code I use in handler:

            cmdBuilder = New MySqlCommandBuilder(dataAdapter)
            dataAdapter.Update(datatable)

AND it works fine till there is no changes in mysql from other access way.

QUESTION IS:
How update changes from database to datatable AND update changes from datatable to database, without any stack, making Update command work?

Please help
Regards

[Begginnerdev]

I am assuming that you are using a gridview or some kind of data control.

Just create a DataSet/DataAdapter, and don't worry about storing values in row changed, you can simply do something like this:

        'Creating the objects
        Dim da As New OleDbDataAdapter(New OleDbCommand("SELECT * FROM tbl", New OleDbConnection("myConStringHere")))
        Dim ds As New DataSet

        'Filling the data adapter
        da.Fill(ds, "myTable")

        'Binding the datasource
        DataGridView1.DataSource = ds.Tables("myTable")
        'You can auto generate colums
        DataGridView1.AutoGenerateColumns = True


        'To update
        da.UpdateCommand = New OleDbCommandBuilder(da).GetUpdateCommand
        da.Update(ds)

        'This will update ANY changes, not just row by row.

[mczupryniak]

It is not exactly what I'm looking for...

From the begining...

I have a datatable in my dataset.

1. When I start my app, I load data from MYSQL DATABASE to my datatable:

       Public Sub
           Dim sqlConnection As New MySqlConnection("Server=" & server & ";User Id=" & user & "; Password=" & pass & "; Database=" & database & "; Pooling=false;Convert Zero Datetime=True")
           Dim sqlcommand As New MySqlCommand
   
           'query
           With sqlCommand
               .CommandText = "SELECT * FROM " & customertable
               .Connection = sqlConnection
               .CommandType = CommandType.Text
               .CommandTimeout = 5000
           End With
   
           'fill table
           Try
               dataBaseAdapter.SelectCommand = sqlCommand
               dataBaseAdapter.Fill(datatable)
           Catch ex As Exception
               Console.WriteLine(ex.Message)
           End Try
   
           'close connection
           If sqlConnection.State <> ConnectionState.Closed Then
               sqlConnection.Close()
           End If
   
           sqlcommand.Dispose()
           sqlConnection.Dispose()
   
      End Sub
   

2. Then I work on this datatable (change data inside rows).

3. When I change some data there is an update handler:

   AddHandler datatable.RowChanged, AddressOf updatedatatable
   

4. And sub to update changed content FROM my datatable to MYSQL DATABASE:

       Public Sub updatedatatable() 
   
           Dim sqlConnection As New MySqlConnection("Server=" & server & ";User Id=" & user & "; Password=" & pass & "; Database=" & database & "; Pooling=false;Convert Zero Datetime=True")
           Dim sqlcommand As New MySqlCommand
   
   
           Try
               cmdBuilder = New MySqlCommandBuilder(dataBaseAdapter)
               dataBaseAdapter.Update(datatable)
           Catch ex As Exception
               Console.WriteLine(ex.Message)
           End Try
   
           If sqlConnection.State <> ConnectionState.Closed Then
               sqlConnection.Close()
           End If
   
           cmdBuilder = Nothing
   
           sqlConnection.Dispose()
   
   
       End Sub
   

AND NOW:
There is a many way to edit MYSQL DATABASE. For example from website or other app's.

And when only I make changes... The update command works fine.

But when for example:
app's load to datatable content from MYSQL Database at 19:00,
then I make some changes from website in that MYSQL DATABASE at 19:02,
and at 19:04 I make changes in datatable and handler will start update sub there is an error:

A first chance exception of type 'System.Data.DBConcurrencyException' occurred in System.Data.dll
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

What I have to do now?
Please Help