DataAdapter updates DB at design time but not at runtime (Dataset binded with Visual Studio Designer via ODBC to a MYSQL DB)

Question

I've a Datagridview in a Windows Form, which BindingContext manages a DataSet via the DataSource Configuraton Wizard (which, automatically, created the necesary Databinding and TableAdapters). The connection type is a ODBC to a remote MySQL server. If I do the connection test, it works fine as well. The DataSet contains two tables related with a master's PrimaryKey and a child's ForeingKey (which reflects the source DataTables' constraints).

The Form has associated the DataSet's, myMasterTable's and myChildTable's corresponding Databind and DataAdapter components, as well as the relational ones (fk's)(with a TableAdapterManager for this last one). If I run my project, the Forms' controls reflects correctly myMasterTable's data, and the DataGridView shows its related data associated with the current Master's ID, thanks to the fksDataBind. By the other hand, if I edit the DataGrid's cells, the changes remain on screen as far a I don't close the Form.

I've created the "Insert, Update and Delete" statements through the DataBase Designer, so the TableAdapters can make the proper updates to the DataSet and Source Tables. If I run those queries at the DataSet Designer, they actually retrieve and modify the Source DataBase, but it does not work at runtime through my Form and its DataGridView.

So, my guess is that I'm lacking a concept (or more), in the relationship between the DataSet, the databindings, the DataAdapters and the Form/DataGridView.

The code I'm using goes around the DataGridView's CellEndEdit event, as follows (VB.NET):

Private Sub myDataGrid_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles myDataGrid.CellEndEdit
Dim auxColumnName As String = Me.myDataGrid.Columns(e.ColumnIndex).Name
Dim auxCellValue = Me.myDataGrid.Rows(e.RowIndex).Cells(auxColumnName).Value

Try
  Select Case auxColumnName
         Case "myColumnName2"

         Case "myColumnName3"

         Case Else

         End Select
         Me.myChildTableBind.EndEdit()
         Me.myDataGrid.NotifyCurrentCellDirty(True)
         Me.myDataGrid.EndEdit()
         Me.myDataGrid.NotifyCurrentCellDirty(False)
         Me.myChildTableAd.Update(Me.myDataSet.myChildTable)
         Me.TableAdapterManager.UpdateAll(Me.myDataSet)
         Me.myDataSet.AcceptChanges()
    Catch ex As Exception
         MessageBox.Show(ex.Message)
    End Try
End Sub

If I debug the DataGrid's user interaction, the event triggers without any problems. This event does not return any errors so far.

As you can see, I've included all the commands I've found looking around the web and forums, and nothing of this works. I would really appreciate any help in the matter.

Answer 1

I get the feeling that it's as simple as you're looking in the wrong database. Probably you're using Access or file mode SQLServer, you have a db in your project folder that is copied to the bin folder when you run the app (press play in VS). The app modifies the db in the bin folder. VS copies the db from the project folder to the bin folder every time it builds, so basically your app is starting over with a fresh "blank" db every time you run it

As an aside, I wouldn't do what you're doing there and update in a DataGrid cellendedit event - it's probably going to end up quite disruptive for the ui and overly resource intensive. Do all that EndEdit and Update stuff in a save button

Here's a quick "what is the relationship between it all"

There is a database, with tables

There is a DataSet, which is a collection of DataTable, themselves a collection of DataRow and DataColumn.

Datasets usually are a corollary of a (part of a) database. DataTables usually mimic a database table. They don't have to - a datatable can contain mor or fewer columns and of differing types than the table. The DataSet can contain more or fewer datatables than the db has tables

A tableadapter is a device that moves data from db table to datatable and back. It can have multiple queries of any kind that pull and shape different data. Calling table adapter update uses a datarow's RowState to determine whether an INSERT UPDATE or DELETE should be run.

A tableadapter manager knows about all tableadapters and the order that updates should be called in to ensure that parent rows are created first and child rows are deleted first

Try not to make tableadapter that is based on SELECT * FROM table - there is seldom any point in downloading an entire table out of a db into a client. Make queries that select by ID; it's handy for loading related data. Make other queries that eg select * from person where name = @name and call then FillByName/GetDataByName or something similarly relevant.