Hi,

I have below table schema.

(A) tblAssetStock -AssetStockID (PK) - rest of the fields...

(B) tblAssetStockTransaction -AssetStockTransactionID (PK) -AssetStockID (FK to (A)) - rest of the fields...

(C) tblAssetStockSubTransaction -AssetStockSubTransactionID (PK) -AssetStockTransactionID (FK to (A)) -AssetStockTransactionIDForParcel (FK to (A)) - rest of the fields...

Now I try to save data in above tables I get below error.

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'AssetStockTransactionIdForParcel', table 'XXX.dbo.AssetStockSubTransaction'; column does not allow nulls. INSERT fails.

How LLBLGen can able to SYNC the PK/FK when there are two 1 to many relationship between table (A) to table (B).

• There are two fields in table B which link to table A, as I need to insert different values in -AssetStockTransactionID and AssetStockTransactionIDForParcel fields in some cases.

Thanks.

Hi Amit,

Please show us a code snippet that reproduces the problem. And, do you have any inheritance on that structure?

LLBLGen runtime library version? (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7722)

Hi David,

No inheritance I have setup in LLBLGen's designer.

Runtime library version info: (latest version) version 2.6.9.1202 for SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll version 2.6.9.1106 for SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll version 2.6.9.917 for SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll

Now actually I have changed the code base as below to make it work by manually filling/setting the FK value into table (C) from table (B).

(C) tblAssetStockSubTransaction -AssetStockTransactionID (FK to (B)) <-- manually filled by saving the parent entity and get the PK of parent entity and set it here... -AssetStockTransactionIDForParcel (FK to (B)) <-- manually filled by saving the parent entity and get the PK of parent entity and set it here...

//PARENT ENTITY
AssetStockTransactionEntity assetStockTransactionEntity = new AssetStockTransactionEntity(); 
...
//fill all the entity field in assetStockTransactionEntity
...
//save here assetStockTransactionEntity entity so we can get PK (auto number value that can be used in setting in CHILD ENTITY.)

//CHILD ENTITY
AssetStockSubTransactionEntity assetStockSubTransactionForBuy = new AssetStockSubTransactionEntity();
//fill all the entity field in assetStockSubTransactionForBuy
--
//manualy PK/FK sync
assetStockSubTransactionForBuy.AssAssetStockTransactionId = assetStockTransactionEntity.AstAssetStockTransactionId;
//manualy PK/FK sync
assetStockSubTransactionForBuy.AssAssetStockTransactionIdForParcel = assetStockTransactionEntity.AstAssetStockTransactionId;

//save here assetStockSubTransactionForBuy entity (CHILD ENTITY)

all good in above case because we are setting the FK value manually as you can see by saving the PARENT ENTITY and then get the PKId value and set to CHILD ENTITY's both fields.

MY MAIN QUERY IS:

How LLBLGen synchronize/set the PK/FK when multiple FK in child table as in below situation (SIMPLE EXAMPLE AS BELOW).

I have 2 tables:

a. Customer (CustId - auto number, Name, City) //you can also think this as a Company entity b. Order (OrderId - auto number, CustId, CustIdParent, Notes) //all NOT NULLABLE fields.

Customer to Order (one to many relationship with CustId and CustIdParent)

Now when I save a Customer (with recursively flag set on save) - it will also save the Order. As CustId and CustIdParent are FK to Customer table so CustId and CustIdParent field in Order table need to set to the same as CustId (PK) in Customer table.

While I get the error on save "CustIdParent is NULL and NULL is not allowed in CustIdParent." because LLBLGen set CustId in Order table but CustIdParent in Order table is NULL.

Customer c = new Customer(); //set Customer entity fields here

Order o = new Order(); o.Notes = "blah blah"; customer.Order.Add(order);

customer.Save recursively.

I get the error on customer save "CustIdParent is NULL and NULL is not allowed in CustIdParent." because LLBLGen set CustId in Order table but CustIdParent in Order table is NULL.

Customer c = new Customer(); //set Customer entity fields here

Order o = new Order(); o.Notes = "blah blah"; customer.Order.Add(order);

customer.Save recursively.

I get the error on customer save "CustIdParent is NULL and NULL is not allowed in CustIdParent." because LLBLGen set CustId in Order table but CustIdParent in Order table is NULL.

Since you have 2 relation between Customer & Order, then you get 2 properties on each side that you should set before saving.

e.g.

order.Customer = customer1;
order.ParentCustomer = customer2; // if you didn't rename the field for better readability, you might find it as oreder.Customer_;

Walaa,

Thanks for the info.

That's right it will generate 2 properties and naming of _ as you mentioned.

A Order table have 2 FK to Customer table. Is LLBLGen automatically set both FK fields to PK key when Customer.Save get called or NOT?

If I have only one FK in Order table and if we perform Customer.Save() the PK/FK is automatically synchronized.

But if table got 2 FK to a table how this can be set automatically?

Thanks.

If you set both properties to the same Customer, then saving the Customer will sync it's PK to both of the FKs.

Thanks Walaa.