none
Insert new master/details rows

    Вопрос

  • Gentle reader,
    here is my today's trouble:

    I have two tables in a dataset
    Customers (int ID primary key, ...)
    Orders (int ID primary key, int IDCust foreign key, ...)

    the SQL Server fields for primary keys are set as autoincrement fields.

    If I want to insert a new row in the Customers table, everything works fine. In this case, setting the ID field is useless, because it is changed by the server when the CustomerTableAdapter.Update method is called.

    But,
    if I want to add a new row in the Orders table, I can omit the ID field as before, but which IDCust do I have to use??????

    Here is my question:
    do I have to make it a two step process? First updating the Customer row, then getting the Customer ID, then create the Orders row, then again updating the Order row.
    If this were the case, it would be a big limit for my work: I cannot restore the previous situation if the user decide to abort the changes instead of committing, like all other ONE TABLE CHANGES I experienced before.
    I mean: I would like to make all the changes to the dataset and then decide to commit or abort in one step, but if I need to update in order to build data in relation-connected tables, this is not possible.

    Is there a way to get the next autoincrement number by the server and book it somehow?

    I'm a newbie here, and would like to understand the common how to in this situation.

    Help me if you can.
    Thanks.
    ------- Life is what happens while doing other projects -------
    4 июня 2009 г. 17:39

Ответы

  • The DataSet is able to take care of this for you.  It's just a little non-obvious.

    First, for your autoincrement fields, in the DataSet designer I would highly recommend that you set the AutoIncrementSeed and AutoIncrementStep both to -1 (negative 1).  This will keep it clear which IDs are temporary and which ones are actually in the database.  You might be doing this already.

    Make sure that you have defined both a DataRelation with ForeignKeyConstraint line between parent Customers (IDCust) and Orders (IDCust) in the designer.  Set the UpdateRule on this object to Rule.Cascade.  This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

    In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

    For example, INSERT is actually an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

    INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);
    SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())

    http://msdn.microsoft.com/en-us/library/2hh60x2k.aspx (Foreign Key Constraint Dialog Box)
    http://msdn.microsoft.com/en-us/library/cs5ze1dx.aspx (Rule Enumeration)

    > I would like to make all the changes to the dataset and then decide to commit or abort in one step

    This always requires you to perform the commands within a transaction, such as an SqlTransaction or a TransactionScope.  Otherwise the granularity will be one row at a time.  This is how SQL Server works.

    5 июня 2009 г. 21:14

Все ответы

  • The DataSet is able to take care of this for you.  It's just a little non-obvious.

    First, for your autoincrement fields, in the DataSet designer I would highly recommend that you set the AutoIncrementSeed and AutoIncrementStep both to -1 (negative 1).  This will keep it clear which IDs are temporary and which ones are actually in the database.  You might be doing this already.

    Make sure that you have defined both a DataRelation with ForeignKeyConstraint line between parent Customers (IDCust) and Orders (IDCust) in the designer.  Set the UpdateRule on this object to Rule.Cascade.  This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

    In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

    For example, INSERT is actually an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

    INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);
    SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())

    http://msdn.microsoft.com/en-us/library/2hh60x2k.aspx (Foreign Key Constraint Dialog Box)
    http://msdn.microsoft.com/en-us/library/cs5ze1dx.aspx (Rule Enumeration)

    > I would like to make all the changes to the dataset and then decide to commit or abort in one step

    This always requires you to perform the commands within a transaction, such as an SqlTransaction or a TransactionScope.  Otherwise the granularity will be one row at a time.  This is how SQL Server works.

    5 июня 2009 г. 21:14
  • OK. This method you proposed is straight.
    But,
    is it possible to work without SQL code , without connecting to the underlying database ?
    Can I achieve my goal by using instead ADO.NET objects like DataRow, DataTable and DataSet?

    Is it possible to insert two brand new rows in two tables, connected by a DataRelation, and after that, have them correctly connected (primary key vs. foreign key) when the update method is called? This would be more elegant, IMHO.

    For instance, I noticed the method SetParentRow....... may it be useful?

    Thanks.
    ------- Life is what happens while doing other projects -------
    6 июня 2009 г. 12:20
  • I'm confused.  You naturally need to a connection to the database as you are updating data in the database.

    > Can I achieve my goal by using instead ADO.NET objects like DataRow, DataTable and DataSet?

    In what way has my response gone beyond these things?  (Other than admittedly the SqlTransaction/TransactionScope.  But these are necessary if you want to do all of the updates as one unit.)

    6 июня 2009 г. 16:24
  • I go and explain:
    your response has gone beyond my limits when you say:

    In order for this to work correctly, your INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate.

    For example, INSERT is actually an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

    INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);
    SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())


    I just want to use ADO.NET object, not any SQL command or stored procedure or istant connection to the db.
    I want that all changes are only in the local datatables and if I want (only if) they can be committed.

    I'm working on it.

    See you.
    ------- Life is what happens while doing other projects -------
    6 июня 2009 г. 16:35
  • For people that are using oracle the following thread has another solution to autoincrementing columns as well.

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fbfdc09b-41bf-4290-9dea-6a9a85d5becf

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    18 августа 2009 г. 16:59
  • Hi Cousin,

    What you are asking will have one problem like data inconsistency in Customer & Order Data (Only problem is in Getting Correct ID of each tables).

    I mean when u work with dataset , you can add record in datatable (customer) but what about CustomerID that u will not have correct id when u work in disconnected mode(dataset). Now if u do not have CustomerID , you can not assign it to Order table.

    so if you want to have customer id for your order table (foreign key) than you have to insert record in customer table (i.e database interaction needed). once you have customer id , you will be able to add order table with CustomerID.

    Hope this will help !!!

    20 августа 2010 г. 6:00
  • How can i achieve this in access also?

    I have a peopleTable (PK is peopleID) and i have a stakesTables (PK is stakeID) then i have a judgestable which only contains the peopleID, StakeID.

    I can update with no problem. But if i .Addnew to the judges table i need to the the peopleID. I tested using

    intretval = MainDbDataSet.Tables(

     

    "lastID").Rows(0)("ID") + 1

    just to get the max value and add 1 but this does not work if the last record has been deleted. (ie if the last peopleid was 7 and it was deleted, then the next one will be 8 and the formula above returns 7)

    and it does not work if the table is empty for the same reason as above.

    Below is the code for the savenew button... PLEASE HELP!!

    Private Sub btnSaveNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveNew.Click
        Try
          Dim intretval As Int32
    
          Me.Validate()
          Me.PeopleBindingSource.EndEdit()
          Me.PeopleTableAdapter.Update(MainDbDataSet.people)
    
          intretval = MainDbDataSet.Tables("lastID").Rows(0)("ID") + 1
    
          For Each TRow As DataGridViewRow In JudgesDataGridView.Rows
            Dim TCell As DataGridViewCell = TRow.Cells(1)
            'Zero for Field1 or index=1 for Field2 data.
            'write data
            TCell.Value = intretval
          Next
    
          Me.JudgesBindingSource.EndEdit()
          Me.JudgesTableAdapter.Update(MainDbDataSet.judges)
    
          MainDbDataSet.AcceptChanges()
    
          MsgBox("Save successful.")
          'refresh data
          Me.PeopleTableAdapter.Fill(Me.MainDbDataSet.people)
          'set buttons
          btnSaveChanges.Visible = True
          btnSaveNew.Visible = False
          btnCancel.Visible = True
          btnAdd.Visible = True
          btnAdd.Enabled = True
          btndelete.Visible = True
          btndelete.Enabled = True
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
    13 октября 2010 г. 15:04
  • Thanks it helped me to clear some basic consepts about dataset. But it is not working in MS-Access, Any Alternative?
    Muhammad Zeeshan, Chief Executive Officer Sprite Solution Provider +923214852152
    8 января 2011 г. 13:25
  • Its a bussiness  level Problem.In your case you have to add customer first.Then you have to update order.

     

    Example:-

    Whenever you are shoping in any online shoping store like ebay and Amazon they wont alloy you to buy till you register.Its a good practise as companies are keeping track of their customer,they have their information in case any fraud happen they can track him.There are other benefit too once user is signed in you can send  latest product detail.Then Signing user can book order so in your case you will have customer id.

     

    But in your scenario bussiness logic will be wrong if you are putting dummy value in customer id as this field has to be taken from customer table.But if you want to do it anyhow.

    then write a single store procedure which can insert in two table.Its mentioned below.

     

     

    BEGIN TRANSACTION;
    DECLARE @CustId int;
    INSERT INTO Customer (Column1 ...) VALUES (....);
    SELECT @CustId  = scope_identity();
    INSERT INTO Orders  (
    Column1 ...VALUES (@CustId);
    COMMIT;
    Happy coding hope my explanation might have helped you

     

    • Предложено в качестве ответа rahuliet2008 6 декабря 2011 г. 9:54
    6 декабря 2011 г. 9:53
  • I am also looking for the same.
    15 февраля 2012 г. 8:04
  • Dear Rahuliet2008,

    if customer have multiple orders same time let say. 3 orders under 1 customer. how we can pass the 3 orders values.

    I hope you understand my question.

    Regards,

    7 апреля 2012 г. 12:10
  •  >> I just want to use ADO.NET object, not any SQL command or stored procedure or istant connection to the db. <<

    The sample SQL statement that was provided by BC is similar to one constructed automatically by the wizards and placed in the .InsertCommand property of the Table Adaptor object.  So ... no need to learn SQL, but I highly encourage you to learn the basics so you know what the wizards are doing on your behalf.


    Brent Spaulding | Access MVP

    19 ноября 2012 г. 16:41
  • >> Thanks it helped me to clear some basic consepts about dataset. But it is not working in MS-Access, Any Alternative? <<

    Access is a different animal that SQL Server or other server based systems.  One such difference is that you can not send two statements in one command to the Access (actually ACE or JET) database engine.

    With an ACE/Jet back end, IIRC, you will need to create a RowUpdated event handler on the DataAdapter which handles the inserts into the parent table. 

    Set up the event handler with something like this after the dataset is created ...

    parentTableDataAdapter.RowUpdated += new OleDbRowUpdatedEventHandler(parentTableDataAdapter_OnRowUpdate);

    Then your event code would be something like:

    static void parentTableDataAdapter_OnRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
    {
        if ((int)e.Row["yourAutoIncrementColumn"] > 0)
        {
            return;
        }
    
        OleDbCommand oleCmd = new OleDbCommand("SELECT @@IDENTITY", e.Command.Connection);
    
        e.Row["yourAutoIncrementColumn"] = oleCmd.ExecuteScalar();
        e.Row.AcceptChanges();
    }


    {please note: this is AIR CODE !!!!}

    Hope that helps!


    Brent Spaulding | Access MVP

    Edits:

    LOL!!! ... I just noticed the age of this thread! ... I clicked on it from the side bar of 'similar threads' when I was working on another, so ... there you have it. :)

    19 ноября 2012 г. 18:54