AcceptChangesDuringUpdate Too Literal?

  • Thread starter Thread starter jlspublic
  • Start date Start date
J

jlspublic

I am attempting to update two database tables from a single DataTable
using DataAdapters. I set the AcceptChangesDuringUpdate property to
"False" on the DataAdapter used to update the first table but it
doesn't work quite the way I'd hoped. As expected, any rows which are
updated by the DataAdapter keep their original row state. However, to
my surprise, any rows the DataAdapter does NOT update appear to have
AcceptChanges called on them. If there are only changes in the
DataTable that would affect the second database table, they never get
applied because the rows have the "wrong" row state when the second
DataAdapter gets a shot at them. Has anyone else run into this? Is
there some way around it? In case it helps, the update code looks
something like this...

Dim ADataAdapter As New SqlDataAdapter(SourceSQL, _Connection)
Dim ASQLCommandBuilder As New SqlCommandBuilder(ADataAdapter)
ASQLCommandBuilder.QuotePrefix = SQLFormatter.QuotePrefix
ASQLCommandBuilder.QuoteSuffix = SQLFormatter.QuoteSuffix
ADataAdapter.AcceptChangesDuringUpdate = False
ChangedRowCount = ADataTable.Select("", "",
DataViewRowState.ModifiedCurrent).Length
Update = ADataAdapter.Update(ADataTable)

If (ChangedRowCount <> ADataTable.Select("", "",
DataViewRowState.ModifiedCurrent).Length) Then
Throw New ApplicationException("Multi-table update failed.")
End If
 
Following up with the resolution to my own post in case anyone else
encounters this problem...

Setting SetAllValues to true on the SQLCommandBuilder forces the
DataAdapter to execute updates for all modified records, even if they
are not modified in fields included in the current update. This makes
AcceptChangesDuringUpdate work the way I expected (at the expense of a
few uneccessary updates on the DB).

The modified code is as follows:

Dim ADataAdapter As New SqlDataAdapter(SourceSQL, _Connection)
Dim ASQLCommandBuilder As New SqlCommandBuilder(ADataAdapter)
ASQLCommandBuilder.QuotePrefix = SQLFormatter.QuotePrefix
ASQLCommandBuilder.QuoteSuffix = SQLFormatter.QuoteSuffix
ADataAdapter.AcceptChangesDuringUpdate = False
ASQLCommandBuilder.SetAllValues = True
Update = ADataAdapter.Update(ADataTable)

-JLS
 
Back
Top