Hi
Does anybody know why the cascading foreign key relationships to auto-incrementing primary keys in a .net DataSet behaves differently with MySQL vs. MS SQL?
I'll flesh it out a bit. I created a parent table with an AI PK, and a child table with an AI PK and a foreign key field to the parent PK. Then I created a cascading relationship between the two, and a single typed DataSet with the same foreign key relationship. The DataSet's auto-increment is set to -1 (the default) to allow to SQL engine to supply the correct incremental values on update. The adapter is configured to 'Refresh the data table' after inserts. On MSSQL, I created 1 parent row and 2 child rows, added them and updated the database and the primary keys change to the next incremental values from the db, and the foreign key relationship holds.
Now with MySQL, I selected the Refresh data table option, but after update the new PK values aren't retrieved, and stay at -1. The parent row is committed to the db, but the child row can't because it doesn't know what the new parent PK is. Strangely enough, when I go back to the dataset wizard the refresh option is unchecked. I know that creating and executing an insert MySqlCommand will return the new PK, but I really want to use the datasets.
Is this expected behavior and does anybody know a workaround? Tried with v5.2.7 and 6.3.5, VS2008 on Win7, and all sorts of different queries
Thanks, Dave