Hi Frans.

Sql Server 2000. DataAdapter.

Parent entity: Account, child entities: Account.Contacts

Using a wizard control, a new entity, Account is created and populated on the form. On the next wizard page, an UltraGrid is used to populate Account.Contacts. On the following wizard page, the Account.Contacts collection entered on the previous page is used to populate a DropDownList control to select a ContactKey for the Account.ManagerContact field. In other words, I'm using a child entity (Contact) to relate back to the parent (Account). I'm manually setting the Contact PK so that there is a key value that gets applied as a FK to Account.ManagerContact before any persistance.

But... oops! It doesn't work because, as I'm beginning to see, during an INSERT operation, the parent is persisted first and SQL Server throws an exception because it can't relate a Contact row to Account.ManagerContact.

Is it required that I save Account and Contact BEFORE I relate Contact back to Account? Is this a necessary limitation?

Thanks!

Jeff

Correct me if I'm wrong: you have a loop in your model, if I interpret your text correctly: Account -> Contact, via Account.ManagerContact => Contact.ContactID and reverse: Contact -> Account, via Contact.AccountID => Account.AccountID ?

You can't save this in one go, not in SQL and not in LLBLGen Pro, as both entities depend on each other. The solution is to make one of the FK's nullable, save and then save again. The first save will insert NULL in the nullable FK, and after the save the FK is synced with the PK and will be the only changed entity and the second save will update the entity.

It's best to avoid loops in models, as they're a signal of redundant data. The ManagerContact flag for example is actually (IMHO) an attribute of the relation Account - Contact. But this is a grey area, I admit. You see this often too in Customer - Address relations, where Address has a CustomerID FK field and Customer a 'Prefered address' FK.

This is what I thought, but I wanted to check. The Customer - Address analagy is exactly the same as the Account - Contact model here. The "looped" model doesn't end with just "Account.ManagerContact", but it extends to "Account.BillingContact" and "Account.ShippingContact" and others, so it's a pretty common programming requirement and I don't think that there's a good alternative.

Jeff

It's a complicated issue indeed. Let me know if the 2-save action doesn't work...