Ok, I finally was able to get it to work after some more research. I will summarize my solution here in case someone needs it. And I still would like some help on making this solution cleaner.

a- add a partial class of the table adapter to help expose the data adapter (code generator makes it private)

namespace InspectionTableAdapters { // name space comes as is from the generated file

public partial class InspectionTableAdapter : Component { // same class header as from the generated file.

public OleDbDataAdapter Adapter2 {

get { return Adapter; } // not _adapter since Adapter has some logic

I was able to expose the data adapter hidden by the table adapter, then reference the RowUpdated adapter event in the code that handles the loading of data that's built in a disconnected state (batch loaded). Here's a translation to VB.NET with some additional notes:

Let's say your dataset is named "MyDataset.xsd" and the table within the dataset you are loading is "MyTable". The data adapter for the table is wrapped and hidden by a table adapter called "MyTableTableAdapter" in the "MyDataSetTableAdapters" namespace in the "MyDataset.Designer.vb" (this file is auto-generated...changes will be wiped out so don't change it).

Add the partial public class to file "MyDataset.vb" and expose the data adapter as "MyAdapter":

Namespace MyDataSetTableAdapters
Partial Public Class MyTableTableAdapter
Public ReadOnly Property MyAdapter() As System.Data.SqlClient.SqlDataAdapter
Get
If (Me._adapter Is Nothing) Then
Me.InitAdapter()
End If
Return Me._adapter
End Get
End Property
End Class
End Namespace

In the code that calls the table adapter update function (the code that manages the batch load), add the RowUpdated event. This assumes you've added a reference to the table adapter as "MyTableTableAdapter1":

AddHandler Me.MyTableTableAdapter1.MyAdapter.RowUpdated, AddressOf RowUpdated

In the same file, add the RowUpdated sub:

Private Shared Sub RowUpdated(ByVal sender As Object, ByVal e As SqlClient.SqlRowUpdatedEventArgs)
If e.StatementType = StatementType.Insert Then e.Status = UpdateStatus.SkipCurrentRow
End Sub

I was having exactly the same problems as described in the first post. Thanks for the help. It's now sorted out.

As a matter of interest, the concurrency error (for me, at least) was as a result of having deletes cascaded in SQL Server as well as on the dataset i.e. the constraint is declared with 'ON DELETE CASCADE' in the table definition. The concurrency problem is eliminated when deletes are handled in the correct order (bottom-up).

Cheers!

I have implemented what you suggest in your very useful post but I am experiencing a bug that I am having problems understanding and satisfactorily resolving.

It goes something like this:

I have a parent table and a child table with a foreign key relationship between them. They are in a SQL Server 2K database and are defined also in a strongly typed dataset. Cascading is set to happen in the dataset and the database.

I update the parent table and then I attempt to update the child table (in fact it is the added rows part that is attempted). On attempting this I get the following error: "INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Child_Parent'. The conflict occurred in database 'Tests', table 'Parent', column 'ParentID'.\r\nThe statement has been terminated."

Now when investigating this in the Immediate Window I found that if I examined the contents of the row which the command was attempting to insert the new (positive) ID assigned by the database was in place correctly in both the Parent table and the Child table. This confused me greatly as I could see no reason given this why their might be a conflict. I ran a Profiler trace using SQL Profiler and saw that the Update command was passing the original (negative) ID for the Parent row when trying to insert the row for the child.

I am having real problems trying to reconcile why the dataset should show that the fkID is the new fkID whilst the TableAdapter.Update command should be using the old fkID.

I didn't leave my investigating there however but looked at the row which the ChildTable.Select("","", DataViewRowState.Added)) was referencing (obviously I thought it should be the same row, but just out of interest. What I found when I tried "((DataRow)(ChildTable.Select("","",DataViewRowState.Added))[0])" in the Immediate Window was that the row contained the error I was experiencing. I used the DataRow.ClearErrors() method to clear the error and the tried the DataRow.EndEdit() method. When I then retried the line of code (pressed F5) was that it inserted fine.

I don't want to have to iterate through each row in the child to call the EndEdit() method and I'm sure I'm missing what is probably a simple trick. I hope that you (or someone) can help me.

Many Thanks,

Neil.

I too am in this "hell" and would appreciate assistance.

tblInvTrans (child)

tblPurchaseOrders(parent)

FK Insert & Delete set to cascade.

I current throw a foreign key constraint error as follows:

_________________________________________

System.Data.SqlClient.SqlException was caught
Class=16
ErrorCode=-2146232060
LineNumber=1
Message="The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblInvTrans_tblPurchaseOrders". The conflict occurred in database "RegalInventory", table "dbo.tblPurchaseOrders", column 'PurchaseOrderID'.
The statement has been terminated."
Number=547
Procedure=""
Server="regdc1"
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)
at Regal_Raw_Inventory.PODataSetTableAdapters.tblInvTransTableAdapter.Update(DataRow[] dataRows) in C:\Documents and Settings\mtompkins.PURCHASE\Desktop\Regal Visual Basic\Regal Raw Inventory\Regal Raw Inventory\PODataSet.Designer.vb:line 3171
at Regal_Raw_Inventory.Form1.PoTabSave() in C:\Documents and Settings\mtompkins.PURCHASE\Desktop\Regal Visual Basic\Regal Raw Inventory\Regal Raw Inventory\Form1.vb:line 155
______________________________________________

The code for the update looks as follows -

If tried many iterations to work through this and have left them commented in the code - all to no avail.

[code]

Private Sub PoTabSave()

Me.Validate()

'Not calling EndEdit will not capture changes that have been made

Me.TblInvTransBindingSource.EndEdit()

Me.TblPurchaseOrdersBindingSource.EndEdit()

'Dim deletedInvTrans As PODataSet.tblInvTransDataTable = _ 'CType( _

'PODataSet.tblInvTrans.GetChanges(Data.DataRowState.Deleted) ', _

''PODataSet.tblInvTransDataTable)

Dim deletedInvTrans As PODataSet.tblInvTransDataTable = _

Dim delPO As PODataSet.tblPurchaseOrdersDataTable = _

Dim newInvTrans As PODataSet.tblInvTransDataTable = _

Dim newPO As PODataSet.tblPurchaseOrdersDataTable = _

Dim modifiedInvTrans As PODataSet.tblInvTransDataTable = _

Dim modPO As PODataSet.tblPurchaseOrdersDataTable = _

' Using scope As Transactions.TransactionScope = _

' New Transactions.TransactionScope()

Try

' Remove all deleted orders from the tblInvTrans table

If deletedInvTrans IsNot Nothing Then

'Me.TblInvTransTableAdapter.Update(deletedInvTrans)

Me.TblInvTransTableAdapter.Update(deletedInvTrans.Select("", "", DataViewRowState.Deleted))

End If

'Update the PO table

'Me.TblPurchaseOrdersTableAdapter.Update(PODataSet.tblPurchaseOrders)

'Should be able to do this by updating tblPO but will break it down

If delPO IsNot Nothing Then

Me.TblPurchaseOrdersTableAdapter.Update(delPO.Select("", "", DataViewRowState.Deleted))

End If

If newPO IsNot Nothing Then

Me.TblPurchaseOrdersTableAdapter.Update(newPO.Select("", "", DataViewRowState.Added))

End If

If modPO IsNot Nothing Then

Me.TblPurchaseOrdersTableAdapter.Update(modPO.Select("", "", DataViewRowState.ModifiedCurrent))

End If

'Add new orders to the InvTrans table

If newInvTrans IsNot Nothing Then

' Me.TblInvTransTableAdapter.Update(newInvTrans)

Me.TblInvTransTableAdapter.Update(newInvTrans.Select("", "", DataViewRowState.Added))

End If

'Update all modified InvTrans

If modifiedInvTrans IsNot Nothing Then

'Me.TblInvTransTableAdapter.Update(modifiedInvTrans)

Me.TblInvTransTableAdapter.Update(modifiedInvTrans.Select("", "", DataViewRowState.ModifiedCurrent))

End If

Me.PODataSet.AcceptChanges()

Catch ex As Exception

MsgBox("Update To Remote Server Failed" & vbCrLf & ex.ToString())

Finally

If deletedInvTrans IsNot Nothing Then

End If

If delPO IsNot Nothing Then

End If

If newInvTrans IsNot Nothing Then

End If

If newPO IsNot Nothing Then

End If

If modifiedInvTrans IsNot Nothing Then

End If

If modPO IsNot Nothing Then

End If

End Try

'scope.Complete()

'End Using

End Sub

[/code]