Databinding a One to Many Relationship with new records

[zachattack05]

I'm having a problem with using the table adapters to accomplish this and was wondering if someone might know of something I could be doing wrong.

The scenario is this:

Adding a new record to the underlying SQL database in this case requires that both a record exist in TableA and TableB. TableA holds the "One" and TableB holds the "Many" in this case.

The form I have is a Wizard style form that prompts the user for multiple pieces of data required to make these records.

All of the data is saved for TableA, but completely dropped from existance with no error displayed for TableB.

I can't figure this out...I keep thinking it has something to do with the PK. At the time of save, the PK for TableA hasn't been set, so it can't create the relationship to TableB because the PK doesn't exist yet. At least that's my theory.

The solution (though not very attractive to me) is to break the wizard into two steps, prompt for TableA data and then TableB data and saving the data between steps to the database. I don't like this solution though because it just complicates things if the user decides to cancel the wizard in the second step and not complete it, or the network drops and the user closes the form without completing the wizard. With everything on one page and not sent to the database until the step is completed (the "wizard" is only 1 step) orphan records can't really exist unless the moon and stars are lined up just right and the forces of evil band together to break the network in that single microsecond. Course, with my luck lately that's a high probability.

Any ideas or suggestions?

[Antenka]

Hello, zachattack05.
Can you provide a code, which is used to save your data? So I can analyze what's happening wrong there.
I don't see the advantages from breaking all this into 2 steps (you still need to update both tables). Maybe I misunderstood something :-/

[zachattack05]

Its the code generated by VS:

this.Validate();
            this.accountParentsBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.suiteData);

However, there are two tables, the PK is located in accountParents, and the FK is located in accountBranches.

I need to add a record to both using that relationship, but it will only save the accountParents data, not the branch data. I've tried adding this.accountBranchesBindingSource.EndEdit(); and even switching them around and saving one before the other, nothing. I get a FK constraint violation if I try to save the accountBranches first, but if I have the "EndEdit" on both, it doesn't matter. Nothing.

It's making me nuts!

[Antenka]

Oh .. I guess, I found it .. take a look at this: How to: Save Dataset Changes to a Database. There's a section Updating Two Related Tables in a Dataset that might be highly interesting for you ;)

[zachattack05]

Am I wrong in thinking that the Table Adapter Manager does all of this? I was under the impression it did.

What I am doing has worked before, but for some reason it's not now.

In fact, I have a form in the same project that does the same thing, but this one isn't working. I think its because they are both NEW records, not existing ones. I don't need to update the data, I need to create two new related rows in two different tables.

I think I need to divide it up, I can't assign a FK to a row if the PK doesn't exist in the first row, so when I go to save it, the key doesn't exist, so it doesn't save it. I'm just going to keep playing around with it. I'll figure it out :)

Thanks for the link, I'll look at it, but I don't think all of that is necessary, though I could be wrong.

[Antenka]

Can you make a test project and attach it to post so I could play a bit with it? (by the way, that's how I found the answer on one of my questions .. so this won't make any harm .. I guess :D)

[zachattack05]

I could try, but the problem is that the data is being saved to a SQL server, unless you have that installed and the same database it wouldn't work.

I'll try when I get a minute and I'll post it.

[Antenka]

Well .. I have one more link for you: Walkthrough: Saving Data from Related Data Tables (Hierarchical Update).
And there, find the section, that says:

In addition to committing changes on a related child table before saving data to a database, you might also have to commit newly created parent records before adding new child records to a dataset. In other words, you might have to add the new parent record (Customer) to the dataset before foreign key constraints enable new child records (Orders) to be added to the dataset. To accomplish this, you can use the child BindingSource.AddingNew event.

.
If it wont work - then I'm waiting for the test app :)

[zachattack05]

I can't get the project to upload, I'll try again later

[zachattack05]

Meh, I'll figure it out. I can't get it to upload.

I'll just keep playing around until I figure it out. I'll probably just end up breaking it up into 2 steps for my own sanity.

[Antenka]

*sigh* .. owkies .. btw, have you tried solution from my last post?

[zachattack05]

Not yet, I have been working on a different part of the application. I'll be revisiting that tomorrow morning perhaps.