none
Access and Parent-Child Relationship RRS feed

  • Question

  • I've got an Access 97 database that has a main parent table and a number of child tables.  I'm writing an C# front-end for this application and I'm having problems with one specific case.  

    The user has just created a new parent record, and also want's to create a record in one or more of the child tables, *before* the parent has been saved.  I just can't get this to work at all, it looks ok on the form, but doesn't get saved to the database, the parent does, but no child records (not even orphaned rows with -1 id's)

    If the parent already exists then its fine.

    I'm using DataSets, and set the relationship to be 'Both Relation and FK', Cascade for Update/Delete/Accept.  The database also has a cascading one-to-many relationship defined.

    I created a stipped down test form just using datagrids, to try and get to the bottom of the issue.  

    The load method is:

    private void TESTFORM_Load(object sender, EventArgs e)
    {
        //fill parents then children.
       this.changeLogTableAdapter.Fill(this.dTCDataSet.ChangeLog);
       this.changeFilesTableAdapter.Fill(this.dTCDataSet.ChangeFiles);
       this.changeNotesTableAdapter.Fill(this.dTCDataSet.ChangeNotes);
    }

    And the save button is:

    private void changeLogBindingNavigatorSaveItem_Click(object sender, EventArgs e)
    {
        this.Validate();
                
        //children
        this.changeFilesBindingSource.EndEdit();
        this.changeNotesBindingSource.EndEdit();
        //...then parent
        this.changeLogBindingSource.EndEdit();
        this.tableAdapterManager.UpdateAll(this.dTCDataSet);
    }
    The TableAdapterManager is set to UpdateInsertDelete.

    What am I doing wrong? Or is it a limitation of the Access 97 database? 

    Tuesday, May 22, 2012 1:58 PM

Answers

  • hi,

    well, that would explain it.

    Apparently, this doesn' t work for ms access; this thread has an answer from the ms ado.net program manager, so he should know:

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/791f1532-d161-4ba5-a6e4-17467aacc35e

    His solution seems acceptable though.

    I think in this blog, the same sort of solution is used:

    http://blogs.msdn.com/b/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx?PageIndex=4

    So we've both learned something: autonumbers with access don't get updated with tableadapters out of the box.


    Regards, Nico

    • Marked as answer by cjb110 Thursday, May 24, 2012 9:30 AM
    Thursday, May 24, 2012 8:30 AM
  • Thanks Nico, I'll try the solution to see if that works!

    I've added a comment to the Hierarchical Update MSDN page for any other poor soul that comes across this!

    Ok, following the link in Nico's post and converting it to C#, you end up with the following that does seem to work!

    In the DataSet 'partial' code file that gets generated for you:

    using System.Data.OleDb; using System.Data; public static class AccessIDHelper { public static void SetPrimaryKey(OleDbTransaction trans, OleDbRowUpdatedEventArgs e) { if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) { var pk = e.Row.Table.PrimaryKey; if (pk != null && pk.Length == 1) { OleDbCommand com = new OleDbCommand("SELECT @@IDENTITY", e.Command.Connection, e.Command.Transaction); e.Row[pk[0]] = (int) com.ExecuteScalar(); e.Row.AcceptChanges(); } } } } namespace DataSetChildTest.DataSetChildTableAdapters { public partial class ParentTableAdapter { public void HookUpEvents() { this._adapter.RowUpdated += (sender, e) => { AccessIDHelper.SetPrimaryKey(this.Transaction, e); }; } }

    public partial class ChildTableAdapter { public void HookUpEvents() { this._adapter.RowUpdated += (sender, e) => { AccessIDHelper.SetPrimaryKey(this.Transaction, e); }; } } }

    Then somewhere on your form, probably the form load event:

    private void Form1_Load(object sender, EventArgs e)
    {
      //fill top-down
      this.parentTableAdapter.Fill(this.dataSetChild.Parent);
      this.childTableAdapter.Fill(this.dataSetChild.Child);
      //hookup the events
      this.parentTableAdapter.HookUpEvents();
      this.childTableAdapter.HookUpEvents();
    }

    You will also need to remember to:

    • Set your Access Database relationships to be cascaded.
    • Set your DataSet relationships the same (FK+Relationship), cascade for the Update/Delete and None for Accept/Reject.
    • In your Save method, make sure you call childBindingSource.EndEdit() as well as the parentBindingSource.EndEdit().
    • Edited by cjb110 Thursday, May 24, 2012 10:48 AM Added details of the solution.
    • Marked as answer by cjb110 Thursday, May 24, 2012 10:48 AM
    Thursday, May 24, 2012 9:31 AM

All replies

  • What you ask is impossible in every real database (although in a very difficult way you can force to create orphans and then let them adopt) 

    However, you can be sure that the same client will sue you at a certain moment, because errors are guaranteed in this kind of design.



    Success
    Cor

    Wednesday, May 23, 2012 6:38 AM
  • Hi,

    i think Cor is right when you'd literally mean 'before'; you can't save child records before you save the parent record.

    However, i suspect what you want to say is, that the save of the dataset fails when you've added a NEW parent with NEW child records in the same dataset.

    That should normally work. 

    There are some conditions though; all the relations should have 'Both relation and foreign key constraint', and al update and delete rules should be 'Cascade'; accept/reject ruse stays 'none'. 

    Also be sure that all fields that have an autoincrement have an autoincremtn seed of -1 and an increment step of -1


    Regards, Nico

    Wednesday, May 23, 2012 6:59 AM
  • Sorry? I think there's some misunderstanding, I don't want to create orphans, and I don't want to attempt to save the child before the parent...but I don't know if *I* have to code that or if I can setup the dataset in some way so that it will save the parent first, and update the child then save it.

    If you create a parent form, and a child subform in Access.  You'll see the related records without an issue.  If you then create a new parent record, you can then go into the child subform and create a child record.  Access will give the child record's FK the right value.  

    Now it might well be that, Access does this by saving the Parent first in the background.  However it does it, I would like to emulate that behaviour in the cleanest way possible.

    At the moment I'm thinking that the best way to achieve this would be to replace the tableAdapterManager.UpdateAll().  If I split that, I can update/save the parent first, then the children.  I might have to update the child FK with the actual new parent PK manually, not sure yet.

    Nico, I will change the accept/reject rule, as I had all the others set as you suggested.


    • Edited by cjb110 Wednesday, May 23, 2012 8:20 AM Just saw Nico's responce!
    Wednesday, May 23, 2012 7:58 AM
  • If I change the accept/reject rule to none, it does throw an exception that an row is required in the parent.  If I set to cascade, it doesn't throw the exception, and just saves the parent without saving the child.

    One thing, could it be the order that the TableAdapterManager is doing things? Looking at the code there doesn't seem to be any logical order to it.

    Wednesday, May 23, 2012 8:25 AM
  • If I change the accept/reject rule to none, it does throw an exception that an row is required in the parent.  If I set to cascade, it doesn't throw the exception, and just saves the parent without saving the child.

    One thing, could it be the order that the TableAdapterManager is doing things? Looking at the code there doesn't seem to be any logical order to it.

    The accept/reject rule should be none, otherwise the child records will not be saved.

    A ta-manager would do the add's and updates in a top-down way (parent first), and the deletes in a bottom-up way (children first).

    If i remember correctly, one can change if it does the adds and updates first, or the deletes first, but  that should not matter.

    It has to be one or other setting that is not correct. What is the exact error message you get, and when do you get it? when adding the child row, or when doing the update? Are you sure all autoincrements are set correctly?


    Regards, Nico

    Wednesday, May 23, 2012 9:03 AM
  • I get "You cannot add or change a record because a related record is required in table 'ParentTable'.", when I call TableAdapterManager.UpdateAll() if the accept/reject is none. If its set to cascade, I get no error but only the ParentTable record is saved, the child is ignored.

    Just checking the AutoIncrements should be set on the PK's?, not the FK in the child as well?  As its on the PK, just double checked.

    I've also tried turning the Hierarchical update on and off again...thinking that maybe as I've been trying things, its not updated the TAManager code...but no luck.

    tis annoying, as the UI looks like everything is fine, I can create the parent, and the child and move off it and come back...every things there with the -1's in the PK and in the FK.  If I create two new parents, and just add child's to the 2nd, I correctly get -2 in the FK for that child.  So the UI has them all linked correctly!


    • Edited by cjb110 Wednesday, May 23, 2012 9:40 AM
    Wednesday, May 23, 2012 9:37 AM
  • are you using VS 2010 and have you installed SP1? there's a bug reported like this that would be solved in SP1:

    https://connect.microsoft.com/VisualStudio/feedback/details/583180/the-auto-generated-code-of-tableadaptermanager-class-in-vs2008-and-vs2010-are-different


    Regards, Nico

    Wednesday, May 23, 2012 11:56 AM
  • I am using Visual Studio 2010 SP1, the dataset designer says it was generated by:

    Runtime Version:4.0.30319.233
    

    Can't find out if that's right version or not...

    Going to create a brand new project with a simplified dataset! to see if it changes.

    Wednesday, May 23, 2012 1:22 PM
  • Created new project, same version of dataset designer, so I don't think its that.

    Stepped through it and it falls over on this point of the auto-gened code:

    if ((this._parentTableAdapter!= null)) 
    {
      global::System.Data.DataRow[] addedRows = dataSet.Parent.Select(null, null, global::System.Data.DataViewRowState.Added);
        if (((addedRows != null) && (0 < addedRows.Length))) 
        {
        result = (result + this._parentTableAdapter.Update(addedRows));
        allAddedRows.AddRange(addedRows);
        }
    }
    if ((this._childTableAdapter != null)) 
    {
    global::System.Data.DataRow[] addedRows = dataSet.Child.Select(null, null, global::System.Data.DataViewRowState.Added);
        if (((addedRows != null) && (0 < addedRows.Length))) 
        {
        result = (result + this._childTableAdapter.Update(addedRows));
        allAddedRows.AddRange(addedRows);
        }
    }

    Specifically the this._childTableAdapter.Update(addedRows) call.

    What I don't see in the code is anything that says 'the parent id is not -1 any more, its x, update the child FK to x'? Or infact anything that seems to use both the parent and child in the same block, its all being done on an individual basis.

    Is that the problem?  Access doesn't do -ve auto numbers?

    Wednesday, May 23, 2012 1:56 PM
  • What I don't see in the code is anything that says 'the parent id is not -1 any more, its x, update the child FK to x'? Or infact anything that seems to use both the parent and child in the same block, its all being done on an individual basis.

    Is that the problem?  Access doesn't do -ve auto numbers?

    When the relation update rule is set to cascade, the primary parent key, that got a positive value from the db, should be cascaded to the child records. This happens when (right after) the parent rows are saved, and before the child rows are saved. So, if you put a breakpoint on

     result = (result + this._childTableAdapter.Update(addedRows));

    then you should see that cascaded, new positive value in the foreign key field value of the child records.

    The mechanism of this cascade only will work if the generated autonumber is returned right after the parent row is added.

    If i remember correctly, this is done by a select statement that is appended to the insert statement, (at least for sql server) which would be generated in the commandtext property of the insertcommand property of the parenttableadapter. Can you confirm this?

    what do you mean by -ve ?


    Regards, Nico

    Wednesday, May 23, 2012 2:39 PM
  • If you want to do it controlled, than change that AutoIdentifier for a GUID (which you have to add yourself before new inserting)

    dim ID = Guid.NewGuid
    var ID = Guid.NewGuid();



    Success
    Cor

    Thursday, May 24, 2012 5:51 AM
  • I think you've hit upon the problem.  

    The insert statement for the parent table, has just the one INSERT query.  And I can't get it to accept any second SELECT statement.  'SELECT @@IDENTITY;' isn't valid, but even trying 'SELECT MAX(ParentID) FROM ParentTable', just brings back an exception 'Extra characters found after the SQL statement.

    So the insert statement in a dataset for an Access 97 database won't return the new AutoNumber, so the TableAdapaterManager has no way of updating the child insert statement.

    oh and -ve = negative! :)

    Thursday, May 24, 2012 7:39 AM
  • hi,

    well, that would explain it.

    Apparently, this doesn' t work for ms access; this thread has an answer from the ms ado.net program manager, so he should know:

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/791f1532-d161-4ba5-a6e4-17467aacc35e

    His solution seems acceptable though.

    I think in this blog, the same sort of solution is used:

    http://blogs.msdn.com/b/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx?PageIndex=4

    So we've both learned something: autonumbers with access don't get updated with tableadapters out of the box.


    Regards, Nico

    • Marked as answer by cjb110 Thursday, May 24, 2012 9:30 AM
    Thursday, May 24, 2012 8:30 AM
  • I'm just trying the same design in Access 2010 to see if it's still a problem.

    First thing I've come across is that in Access 2010 by default it will save orphaned children, with their FK of -1, with the Accept/Reject rule set to None.

    Well Access 2010 wont accept multi-statement SQL queries either.  I tried in the DataSet Designer query window, and also tried to create a view in Access directly that would insert and return the @@IDENTITY value.

    So it doesn't look like DataSet Hierarchical updates work for 'free'.  

    I'm going to look what you need to do manually to achieve the same result now.


    Thursday, May 24, 2012 9:28 AM
  • Thanks Nico, I'll try the solution to see if that works!

    I've added a comment to the Hierarchical Update MSDN page for any other poor soul that comes across this!

    Ok, following the link in Nico's post and converting it to C#, you end up with the following that does seem to work!

    In the DataSet 'partial' code file that gets generated for you:

    using System.Data.OleDb; using System.Data; public static class AccessIDHelper { public static void SetPrimaryKey(OleDbTransaction trans, OleDbRowUpdatedEventArgs e) { if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) { var pk = e.Row.Table.PrimaryKey; if (pk != null && pk.Length == 1) { OleDbCommand com = new OleDbCommand("SELECT @@IDENTITY", e.Command.Connection, e.Command.Transaction); e.Row[pk[0]] = (int) com.ExecuteScalar(); e.Row.AcceptChanges(); } } } } namespace DataSetChildTest.DataSetChildTableAdapters { public partial class ParentTableAdapter { public void HookUpEvents() { this._adapter.RowUpdated += (sender, e) => { AccessIDHelper.SetPrimaryKey(this.Transaction, e); }; } }

    public partial class ChildTableAdapter { public void HookUpEvents() { this._adapter.RowUpdated += (sender, e) => { AccessIDHelper.SetPrimaryKey(this.Transaction, e); }; } } }

    Then somewhere on your form, probably the form load event:

    private void Form1_Load(object sender, EventArgs e)
    {
      //fill top-down
      this.parentTableAdapter.Fill(this.dataSetChild.Parent);
      this.childTableAdapter.Fill(this.dataSetChild.Child);
      //hookup the events
      this.parentTableAdapter.HookUpEvents();
      this.childTableAdapter.HookUpEvents();
    }

    You will also need to remember to:

    • Set your Access Database relationships to be cascaded.
    • Set your DataSet relationships the same (FK+Relationship), cascade for the Update/Delete and None for Accept/Reject.
    • In your Save method, make sure you call childBindingSource.EndEdit() as well as the parentBindingSource.EndEdit().
    • Edited by cjb110 Thursday, May 24, 2012 10:48 AM Added details of the solution.
    • Marked as answer by cjb110 Thursday, May 24, 2012 10:48 AM
    Thursday, May 24, 2012 9:31 AM