DataAdapter.AcceptChangesDuringUpdate Property

Gets or sets whether AcceptChanges is called during a Update.

Namespace: System.Data.Common
Assembly: System.Data (in

Public Property AcceptChangesDuringUpdate As Boolean
Dim instance As DataAdapter
Dim value As Boolean

value = instance.AcceptChangesDuringUpdate

instance.AcceptChangesDuringUpdate = value
/** @property */
public boolean get_AcceptChangesDuringUpdate ()

/** @property */
public void set_AcceptChangesDuringUpdate (boolean value)

public function get AcceptChangesDuringUpdate () : boolean

public function set AcceptChangesDuringUpdate (value : boolean)

Not applicable.

Property Value

true if AcceptChanges is called during an Update; otherwise false. The default is true.

During a call to the Update method of a DataAdapter, the database can send data back to your ADO.NET application as output parameters or as the first returned record of a result set. ADO.NET can retrieve these values and update the corresponding columns in the DataRow being updated. By default, ADO.NET calls the AcceptChanges method of the DataRow after the update. However, if you want to merge the updated row back into another DataTable, you may want to preserver the original value of a primary key column. For example, a primary key column corresponding to an automatically incrementing column in the database, such as an identity column, can contain new values that are assigned by the database that do not match the original values assigned in the DataRow. By default, AcceptChanges is called implicitly after an update, and the original values in the row, which may have been AutoIncrement values assigned by ADO.NET, are lost. You can preserve the original values in the DataRow by preventing ADO.NET from calling AcceptChanges after it performs an update on a row, by setting the AcceptChangesDuringUpdate property to false, which preserves the original values.


Setting the AcceptChangesDuringUpdate property to false applies to all data modifications, not only inserts. If you want to edit or delete rows in the same update, and if you want to suppress the call to AcceptChanges only for inserts, then instead of setting AccceptChangesDuringUpdate to false, use an event handler for the RowUpdated event of the DataAdapter. In the event handler you can check the StatementType to determine if the data modification is an insert, and if true, set the Status property of the RowUpdatedEventArgs to SkipCurrentRow. For more information and an example, see Retrieving Identity or Autonumber Values.

This example demonstrates extracting changed rows from a DataTable and using a SqlDataAdapter to update the data source and retrieve a new identity column value. By setting the AcceptChangesDuringUpdate property of the SqlDataAdapter to false to preserve the original auto increment value, the new data can then be merged into the original DataTable, even if the new identity value does not match the original auto increment value in the DataTable.

Private Sub MergeIdentityColumns(ByVal connectionString As String)

    Using connection As SqlConnection = New SqlConnection( _

        ' Create the DataAdapter
        Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
          "SELECT ShipperID, CompanyName FROM dbo.Shippers", connection)

        ' Add the InsertCommand to retrieve new identity value.
        adapter.InsertCommand = New SqlCommand( _
            "INSERT INTO dbo.Shippers (CompanyName) " & _
            "VALUES (@CompanyName); " & _
            "SELECT ShipperID, CompanyName FROM dbo.Shippers " & _
            "WHERE ShipperID = SCOPE_IDENTITY();", _

        ' Set AcceptChangesDuringUpdate to false.
        adapter.AcceptChangesDuringUpdate = False

        ' Add the parameter for the inserted value.
        adapter.InsertCommand.Parameters.Add( _
           New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, _
        adapter.InsertCommand.UpdatedRowSource = _

        ' MissingSchemaAction adds any missing schema to 
        ' the DataTable, including auto increment columns
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        ' Fill a DataTable.
        Dim shipper As New DataTable

        ' Add a new shipper row. 
        Dim newRow As DataRow = shipper.NewRow()
        newRow("CompanyName") = "New Shipper"

        ' Add changed rows to a new DataTable. This
        ' DataTable will be used to update the data source.
        Dim dataChanges As DataTable = shipper.GetChanges()

        ' Update the data source with the modified records.

        Console.WriteLine("Rows before merge.")
        Dim rowBefore As DataRow
        For Each rowBefore In shipper.Rows
            Console.WriteLine("{0}: {1}", rowBefore(0), rowBefore(1))

        ' Merge the two DataTables to get new values.

        ' Commit the changes.

        Console.WriteLine("Rows after merge.")
        Dim rowAfter As DataRow
        For Each rowAfter In shipper.Rows
            Console.WriteLine("{0}: {1}", rowAfter(0), rowAfter(1))
    End Using
End Sub

Windows 98, Windows Server 2000 SP4, Windows CE, Windows Millennium Edition, Windows Mobile for Pocket PC, Windows Mobile for Smartphone, Windows Server 2003, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP SP2, Windows XP Starter Edition

The Microsoft .NET Framework 3.0 is supported on Windows Vista, Microsoft Windows XP SP2, and Windows Server 2003 SP1.

.NET Framework

Supported in: 3.0, 2.0

.NET Compact Framework

Supported in: 2.0

XNA Framework

Supported in: 1.0

Community Additions