none
How to pass an Identity from one INSERT to the next? RRS feed

  • Question

  • My problem,

    I have a generated typed dataset. There are 2 tables (for simplicity sake) in this typed dataset

    Meeting & MeetingCategory

    In the Meeting table, which contains general information about a meeting, the MeetingID column is an Identity field. I want to pass it to the MeetingCategory TableAdapter the MeetingID that was created when the meeting was inserted into the database, so the category information for the new meeting is inserted with the appropriate MeetingID. I can do it the manual way by altering the Meeting's INSERT SQL Command so the SCOPE_IDENTITY() is returned.  And then use this in a manual call to INSERT on the MeetingCategory table adapter. Is there a way to get this done automatically using the dataset designer and just calling update or do I have no choice and have to call it manually.

    Thanks
    Wednesday, March 26, 2008 6:44 PM

Answers

  • Your InsertCommand in the Meeting table TableAdapter should look something like:

     

    INSERT INTO Meeting ... ;

    SELECT MeetingId,... FROM Meeting WHERE (MeetingId = SCOPE_IDENTITY())

     

    This is similar to the code that will be generated if you drag and drop a table with an Identity column to your DataSet.

     

    If you then have a Relation in your DataSet which Cascades updates, the MeetingCategory table will be automatically updated, there's nothing for you to do.

     

    Wednesday, March 26, 2008 7:57 PM