none
Strongly typed dataset: inserting many-to-many relations RRS feed

  • 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">
    <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.

     

     

    Monday, November 30, 2009 10:37 AM

Answers

  • Hello Balint,

     

    We need to listen to pug.RowUpdated event! In the event handle, we can retreived the auto increased identity from the server by executing,
    "select @@identity"

    And then we need to set the retrieved id to e.Row["id"] and call AcceptChanges() function. After all these actions, both of your ProgramUserGroup and LinkFeatureWithProgramUserGroup table can have the right auto number id.

    See the example from this codeproject article,

    Inserting related data using DataAdpater and DataSet,
    http://www.codeproject.com/KB/database/relationaladonet.aspx

    Please give it a try and let us know if there is any futuer help we can provdie. Have a nice day sir!

     

     

    Ji Zhou

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by kbalint Wednesday, December 2, 2009 11:56 AM
    Tuesday, December 1, 2009 7:32 AM
    Moderator

All replies

  • Hello Balint,

     

    We need to listen to pug.RowUpdated event! In the event handle, we can retreived the auto increased identity from the server by executing,
    "select @@identity"

    And then we need to set the retrieved id to e.Row["id"] and call AcceptChanges() function. After all these actions, both of your ProgramUserGroup and LinkFeatureWithProgramUserGroup table can have the right auto number id.

    See the example from this codeproject article,

    Inserting related data using DataAdpater and DataSet,
    http://www.codeproject.com/KB/database/relationaladonet.aspx

    Please give it a try and let us know if there is any futuer help we can provdie. Have a nice day sir!

     

     

    Ji Zhou

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by kbalint Wednesday, December 2, 2009 11:56 AM
    Tuesday, December 1, 2009 7:32 AM
    Moderator
  • Hello, and thank you for your answer!

    I wrote some workaround with LINQ and iterated through the records.
    Wednesday, December 2, 2009 11:56 AM