locked
new record in parent/child tables RRS feed

  • Question

  • hi
    i have a problem with insert new record into parent/child tables.
    i want to have a data entry window with detail(not datagrid) form, both for parent and child tables.
    also i don't want to use datanavigator.
    i use this code for save button:
    this.validate();
    this.parentBindingSource.endEdit();
    this.childBindingSource.endEdit();
    this.parentTableAdapter.update(dsTest.parent);
    this.childTableAdapter.update(dsTest.child);
    

    after all i just have a new record on the parent table and inputed data in the child form disapear. why?
    note: i use vs2005 + access db and i have many fields. so i devide fields into some tables with one-to-one relations and
    i want to use wizards and data binding for textboxes and other controls.
    please help me.
    thanks.
    Sunday, July 18, 2010 6:05 PM

Answers

  • Hi Mehdi0016,

    Do you get any exception or error ? Are the parent/child tables' primary keys auto-genereated ? If so, please note that if you are doing inserts, especially for related parent-child data, you need a way to intercept the DataRow and set the primary key right after the row is inserted into the database and before any children are inserted. And you need to populate the parent key back in the DataRow inside the DataSet before you use this key to insert new record into child table. You can refer to the following article to get some very useful information about "Using TableAdapters to Insert Related Data into an MS Access Database".

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

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Mehdi0016 Wednesday, July 21, 2010 10:00 AM
    Tuesday, July 20, 2010 2:57 AM

All replies

  • Ah, what makes you think the data has disappeared? Note that Access/JET caches data in RAM and only writes to the database during an idle period (to help performance).

    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Sunday, July 18, 2010 6:52 PM
  • Hi Mehdi0016,

    Do you get any exception or error ? Are the parent/child tables' primary keys auto-genereated ? If so, please note that if you are doing inserts, especially for related parent-child data, you need a way to intercept the DataRow and set the primary key right after the row is inserted into the database and before any children are inserted. And you need to populate the parent key back in the DataRow inside the DataSet before you use this key to insert new record into child table. You can refer to the following article to get some very useful information about "Using TableAdapters to Insert Related Data into an MS Access Database".

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

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Mehdi0016 Wednesday, July 21, 2010 10:00 AM
    Tuesday, July 20, 2010 2:57 AM
  • sorry for my late. i'm too busy. yes my parent table has autonumber id column and as Alex said i must update parent table first and then get back the last key(id) for child table. but if i bind the parent primary key(autonumber) to textbox on the form then could i use the that textbox.text for new id in child table? or it just a temp id? thanks again.
    Wednesday, July 21, 2010 10:09 AM
  • Yes, when adding new rows, parents come first, then the children. This means you'll need to get (by whatever means necessary based on the DBMS and binding) the new parent PK. This can be used to create a new child row. If the parent PK is created via an INSERT to the DBMS, you get back the new (permanent) ID. If rows are created on the client to be posted to the DBMS later then these are negative "temporary" client-side IDs. Since an INSERT statement does not include the PK value when using IDENTITY columns you don't provide a PK value to the INSERT.

    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, July 22, 2010 5:11 PM