DataAdapter.Update method

[Mark@SF]

As per Microsoft's documentation, there are several ways to call the DataAdapter.Update method by passing it one of the following: an array of DataRow objects, a DataSet object, or a DataTable object.

And furthermore, the AcceptChanges method for the DataRow, DataSet, or DataTable should also be implicitly called by the DataAdapter.Update method.

I have a MS Access database as my datasource. I use a DataAdapter and I've configured its DeleteCommand, InsertCommand, and UpdateCommand properties.

If I call the DataAdapter.Update method and pass it a DataTable, the AcceptChanges method of the DataTable is not implicitly called by the DataAdapter.Update method.

dtTemp = dsMain.Tables("Categories").GetChanges(DataRowState.Modified)
daCategories.Update(dtTemp)

If instead I call the DataAdapter.Update method and pass it an array of DataRow objects, then the AcceptChanges method for each changed DataRow is implicitly called by the DataAdapter.Update method.

rows = dsMain.Tables("Categories").Select(vbNullString, vbNullString, DataViewRowState.ModifiedCurrent)
daCategories.Update(rows)

I can see this behavior by checking the if the DataTable has changes before and after calling the DataAdapter.Update method.

Debug.Print(dsMain.Tables("Categories").GetChanges Is Nothing)

Since the Microsoft documentation claims otherwise, is there something that I'm missing?

[jmcilhinney]

Why are you calling GetChanges in the first place? This:

dtTemp = dsMain.Tables("Categories").GetChanges(DataRowState.Modified)

suggests that you want to isolate those rows with a RowState of Modified, i.e. existing rows that have been edited, from those with a RowState of Added, i.e. new rows, or Deleted, i.e. existing rows that have been deleted. Do you want to do that? If so, this line doesn't really make sense:

Debug.Print(dsMain.Tables("Categories").GetChanges Is Nothing)

That is checking whether the DataTable contains changes of ANY kind and not just edits. If you do care about changes of any kind, why are you calling GetChanges before Update in the first place?

That's by the by though. The real issue here is that GetChanges returns a completely new DataTable, populated with completely new DataRows. When you do this:

dtTemp = dsMain.Tables("Categories").GetChanges(DataRowState.Modified)
daCategories.Update(dtTemp)

The AcceptChanges method of dtTemp, and thus the AcceptChanges method of every DataRow in dtTemp, does get called. If you were to actually examine dtTemp afterwards then you'd see that. The thing is, that has exactly zero effect on the original DataTable, i.e. dsMain.Tables("Categories"), which is the whole point. Calling GetChanges allows you to mess around with specific changes from a DataTable without affecting the whole DataTable. The idea is that you do whatever you need to do on that DataTable and any others in whatever order you want, then you call AcceptChanges explicitly when you're done. For example, let's say that you have a parent DataTable and a child DataTable. You need to delete child records before deleting parent records but you need to insert parent records before inserting child records, so you can 't just do all of one DataTable and then all of the other. You would handle that situation something like this:

Dim childDeleteTable = childTable.GetChanges(DataRowState.Deleted)
Dim parentDeleteTable = parentTable.GetChanges(DataRowState.Deleted)
 
childAdapter.Update(childDeleteTable)
parentAdapter.Update(parentDeleteTable)
 
Dim parentUpdateTable = parentTable.GetChanges(DataRowState.Modified)
Dim childUpdateTable = childTable.GetChanges(DataRowState.Modified)
 
parentAdapter.Update(parentUpdateTable)
childAdapter.Update(childUpdateTable )
 
Dim parentInsertTable = parentTable.GetChanges(DataRowState.Added)
Dim childInsertTable = childTable.GetChanges(DataRowState.Added)
 
parentAdapter.Update(parentInsertTable)
childAdapter.Update(childInsertTable )
 
parentTable.AcceptChanges()
childTable.AcceptChanges()

[Mark@SF]

jmc -

You rock! My learning point for today...

The real issue here is that GetChanges returns a completely new DataTable, populated with completely new DataRows.

Makes sense, now that you've explained it. BTW, where in the Microsoft documentation is that nugget of knowledge?

Thanks for your help.

[jmcilhinney]

The documentation says that GetChanges gets a copy of the original DataTable and the authors probably just assumed that you would know that a DataRow is always associated with a specific DataTable. I'm fairly certain that you can't even remove a DataRow from a DataTable and then add it to another, let alone add it to another without removing it first. Given that a DataRow has a Table property that specifies the DataTable it belongs to, that's indicative of the fact that it can only belong to one DataTable.