Strongly typed dataset: inserting many-to-many relations

Question

Hi, I have a many-to-many relation in 3 tables: ProgramUserGroup and Feature are the two main tables, and the link between them is LinkFeatureWithProgramUserGroup, where I have Foreign key relations to the two parent tables.

I have a dataset with inserts: I want to add a new row to ProgramUserGroup, and a related (existing) Feature to the LinkFeatureWithProgramUserGroup table. When Inserting new rows, I'm setting the default id to -1:

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">   <DataSetUserGroup xmlns="http://tempuri.org/DataSetUserGroup.xsd"&gt;
<ProgramUserGroup diffgr:id="ProgramUserGroup1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
  <id>-1</id>
  <Name>99999999999</Name>
  <Active>false</Active>
</ProgramUserGroup>
<LinkFeatureWithProgramUserGroup diffgr:id="LinkFeatureWithProgramUserGroup1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
  <id>-1</id>
  <Feature_id>7</Feature_id>
  <ProgramUserGroup_id>-1</ProgramUserGroup_id>
</LinkFeatureWithProgramUserGroup>  </DataSetUserGroup> </diffgr:diffgram>

while I'm updating the tables, I get an error:

"The INSERT statement conflicted with the FOREIGN KEY constraint "FK-LinkFeatu-Progr-7DCDAAA2". The conflict occurred in database "x", table "dbo.ProgramUserGroup", column 'id'."

The code for the update is the following:

 DataSetUserGroupTableAdapters.LinkFeatureWithProgramUserGroupTableAdapter lfa = new LinkFeatureWithProgramUserGroupTableAdapter();
 DataSetUserGroupTableAdapters.ProgramUserGroupTableAdapter pug = new ProgramUserGroupTableAdapter();

 pug.Update(dsUserGroup.ProgramUserGroup);
 lfa.Update(dsUserGroup.LinkFeatureWithProgramUserGroup);

if I check the ProgramUserGroup table's new row's ID, it has been updated from -1 to @@identity (like 1099), so it's okay - it inserts the new row.

But In the LinkFeatureWithProgramUserGroup table, the related ProgramUserGroup.ID value is still -1, it was not updated anyhow.

How could I force the update of the link table's keys as well? I've tried

 pug.Update(dsUserGroup.ProgramUserGroup);
 dsUserGroup.Merge(dsUserGroup.ProgramUserGroup);
 lfa.Update(dsUserGroup.LinkFeatureWithProgramUserGroup);

But didn't solve the problem :(

Thanks,

b.

Answer 1

Yes, there's a work-around for this.

You need to tell your parent table's table-adapter to refresh the data-table after update operation. This is how you can do that.

1. Open the properties of ProgramUserGroupTableAdapter -> Default Select Query -> Advnaced options. and Check the option of Refresh the data table. Save the adapter now. Now when you call update on table-adapter, the data-table will be updated [refreshed] after the update operation and will reflect the latest values from database table. if the primary-key or any coloumn is set to auto-increment, the data-table will have those latest value post recent update.

2. Now you can Call the update as pug.Update(dsUserGroup.ProgramUserGroup);

3. Read latest values from the ProgramUserGroup coloumns and assign respective values into the child table before update. This will work exactly the way you want.