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

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.

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

This article has been dead for over six months. Start a new discussion instead.