| re: Foreign Key self-join on a DataTable
it looks like there are two problems
AutoIncrement columns aren't automatically updated by the database (as far
as I'm aware). To solve this problem, you should include a SELECT statement
in your INSERT & UPDATE commands to get the updated row.
The foreign key should work fine. When adding a new row to the DataTable,
make sure you call the SetParentRow method and pass the parent row You
shouldn't need to set the accept/reject rule. The fk will manage that for you.
HTH
"Ev" wrote:
[color=blue]
> I have a database table in SQL Server that has a self join. In C# I have a
> DataTable with a self-join. I have defined a foreign key constraint on the
> DataTable for the self join. The AcceptRejectRule is set to Cascade
>
> Table Structure:
> OrderID (Identity field)
> OrderDesc
> OrderParent (this is the self-join - it is a foreign key to OrderID)
>
> In my code I add several rows to the table, createing a hierarchy of orders
> e.g.
> 1000 (OrderParent = NULL
> - 1001 (OrderParent = 1000)
> - 1002 (OrderParent = 1000)
>
> The OrderID field of the DataTable is set as an AutoIncrement column with
> the seed starting at -1. When I insert the records in the database, it
> should, in theory, insert Order 1000, get the idenity of the new row, update
> the identity in the row, and then update the OrderParent in all child rows.
>
> When I call the DataAdapter Update() method, only the top level rows in the
> hierarchy (i.e. Order 1000) are inserted into the table. None of the other
> rows are inserted (i.e Order 1001, 1002 aren't in the db). It appears that
> the Update() doesn't even try to add those rows. After the Update() method I
> checked the rows in the table and they all still have their initial OrderID
> and OrderParent values so it doesn't look like the field is being updated
> with the idenity value from SQL Server.
>
> If don't set the AcceptRejectRule property, it will attempt to insert the
> child rows but since the OrderParent hasn't been updated there is a foreign
> key violation in SQL Server.
>
> Any help would be appreciated
>
>[/color]
| 
