locked
datasets and autonumbers RRS feed

  • Question

  • I'm new to using datasets with servers, and have a question about their capabilities regarding child tables.  Say I have a dataset containing a parent table and child table (the child table has a column for referencing the parent ID #'s), and I want to insert this dataset into a SQL Server db.   When uploading, the parent entries will be getting to an autonumber ID assigned by the sql server database.  Assuming I have the intertable relations correctly set up in both my datasets and database, is it possible to upload both tables in one batch, such that the uploaded child entries will automatically properly reference the parent autonumber ID's?  Since both sides understand the relationships, I don't see that any extra coding will be necessary.

    (The other solution I guess would be to create a insert parameter so that the dataset gets back the autonumber value when I insert my parent table, and then the ID # gets propagated to the child datatable, assuming the references are set up.  I'm just wondering if I have to do this or if datasets can figure this out without any intervention.)
    Thursday, July 2, 2009 10:09 PM

Answers

    • Marked as answer by Chris Robinson- MSFT Thursday, July 2, 2009 11:27 PM
    • Unmarked as answer by Tekito Monday, July 6, 2009 9:27 PM
    • Marked as answer by Tekito Wednesday, July 8, 2009 1:18 PM
    Thursday, July 2, 2009 11:05 PM
  • Okay, now I tried the table adapter 'Update' instead of the 'InsertQuery' method above, and it does appear to be propogating back.  This is what I meant by there seemed to be more than one method option.  I don't know why the InsertQuery method explained in my last two posts would fail though.  So I'm still somewhat confused, and still going to be playing with this some more.  If there are no more surprises then maybe the issue is solved.

    • Proposed as answer by Chris Robinson- MSFT Tuesday, July 7, 2009 8:44 PM
    • Marked as answer by Tekito Wednesday, July 8, 2009 1:18 PM
    Tuesday, July 7, 2009 2:47 PM

All replies

    • Marked as answer by Chris Robinson- MSFT Thursday, July 2, 2009 11:27 PM
    • Unmarked as answer by Tekito Monday, July 6, 2009 9:27 PM
    • Marked as answer by Tekito Wednesday, July 8, 2009 1:18 PM
    Thursday, July 2, 2009 11:05 PM
  • BinaryCoder,
    I looked at your answer and attempted to replicate it, but it does not seem to be getting back the autonumber ID from the server.  Even the ID in the parent datatable remains unchanged (negative) so this not a child-cascading issue.  Completely analogous to the insert query you posted, this was my wizard-generated insert query:

    INSERT INTO [dbo].[Fields] ([JobCode]) VALUES (@JobCode);
    SELECT JobCode, FieldID FROM Fields WHERE (FieldID = SCOPE_IDENTITY())

    FieldID is the autonumber and remains unchanged in the datatable after a successful upload.  I don't understand why as this was the autogenerated code, and it clearly is being selected.
    Monday, July 6, 2009 9:34 PM
  • Hmmm... don't know.  This technique usually works.  What does your update look like (are you using TableAdapters, SqlDataAdapter)?  Does your DataSet have FieldID set as the primary key in the DataSet?
    Tuesday, July 7, 2009 2:48 AM
  • FieldID is set as the single primary key of the specified datatable (I don't know what you mean by "primary key of the DateSet").  I am using the generated table adapters that are created along with the dataset. 

    As to what command to use to update, I think there might be more than one option, and maybe that's the issue.  But to generate my query, in dataset designer I choose to "Add query" for the table adapter, select "Use SQL statements", choose "INSERT" type, etc, and the generated query looks like what was posted above.  The final query is given a default name such as "InsertQuery".  Codewise, it is being called as below:

      Using da As New dsTBSQLTableAdapters.FieldsTableAdapter
                    For Each rw As FieldsRow In _DS.Fields.Rows
                        da.InsertQuery(rw.JobCode, rw.FieldCode)
                    Next
      End Using

    Fields is the name of the datatable, _DS is the instance of the dataset, and the insert query takes the columns as parameters (it does NOT ask for the FieldID column).  This does successfully update to the SQL Server, but the autonumbered id is not propogated back to the datatable.

    Now, once I did get the ID to propogate back to the datatable with this modified query:

    INSERT INTO [dbo].[Fields] ([JobCode], [FieldCode]) VALUES (@JobCode, @FieldCode);
    SELECT @FieldID=SCOPE_IDENTITY()

    This uses another paramater called @FieldID, that has to be first appropriately set up as an output parameter (and the insertquery method then requires rw.FieldID as a BYREF parameter).  Even though this appears to work, I would much rather use a method that is close to being autogenerated by the dataset designer.  I don't want to have to manually create update, insert, etc queries (including having to add parameters) for every single table if the designer wizard is smart enough to make a workable query for me - it would potentially save me a lot maintenance time.  So I am interested in getting your solution to work.

     

     

    • Edited by Tekito Tuesday, July 7, 2009 1:43 PM
    Tuesday, July 7, 2009 1:41 PM
  • Okay, now I tried the table adapter 'Update' instead of the 'InsertQuery' method above, and it does appear to be propogating back.  This is what I meant by there seemed to be more than one method option.  I don't know why the InsertQuery method explained in my last two posts would fail though.  So I'm still somewhat confused, and still going to be playing with this some more.  If there are no more surprises then maybe the issue is solved.

    • Proposed as answer by Chris Robinson- MSFT Tuesday, July 7, 2009 8:44 PM
    • Marked as answer by Tekito Wednesday, July 8, 2009 1:18 PM
    Tuesday, July 7, 2009 2:47 PM