Osama Ghazal

22 hours ago, programmerdelphi2k said:

@Osama Ghazal

 

I think there is a basic error here!

You cannot insert a new product in the price list if it does not exist in the products table, however, to insert a new product in the products table, you must inform all required fields (id, name, price, etc...) in this table.
At the end, then, you could get the "ID" of this new product to use in the price list table!
Thus "ID" is the primary key in the product table, and it is the foreign key in the price list table!

Typical Master-Details usage!

@programmerdelphi2k

Ok, I Update the InsertSQL as the following:

INSERT HIGH_PRIORITY INTO products
(ProductID, Barcode, ProductName, CategoryID, LevelNo, ProductType, QtyHasDigits, IsService, MinQty, UserID)
  VALUES
(:ProductID, :Barcode, :ProductName, :CategoryID, 4, 0, 0, 0, 0, 1);

Insert INTO pricelists
(ProductID,
  SalePrice,
  MinSalePrice,
  Service)
VALUES
(:ProductID,
  :SalePrice,
  :MinSalePrice,
  :Service)

 and it is success.

Thanks you

12 hours ago, Hans J. Ellingsgaard said:

The problem is your join in the query. Sometimes FireDAC is smart enough to write an update script that's ignoring the join fields, but if not, then you have to write the update query yourself. If I remember correctly there is a description of how to do it in Cary Jensen's book. ( I saw you mentioned it in an another post)

Thanks,

I have many solutions for this but I am trying to optimize my code and using less code as I can.

I will look for other solution.

I put it already in this event

qryProductsBeforePost : qryProductsProductID.Value := fdConnection1.ExecSQLScalar('SELECT IFNULL(MAX(ProductID) + 1, 1) AS NewID FROM products');

and it gives me a new id 151

I talked about it,

not many peoples says that it is deep and it not cover a many topics, only few

Hi All,

is there is any documentation or books for firedac that help me to upgrade my knowledge?

I look for FireDAC in Depth,  but I found the most topics on fdmemorytable, and there is nothing deep.

and the Embarcadero documentations and samples are very bad and complex, there is no explains enough.

I have recordesd many videos on youtube and I feel that I am not strong enough in FireDAC.

so, what is the solution?

Hi All,

I am using MySQL Database and I create a query called qryProducts like this:

SELECT  p.ProductID,  p.Barcode,  p.ProductName,  p.CategoryID,  p.LevelNo,  p.ProductType,  p.UserID,  p.IsService,  pl.SalePrice,  pl.MinSalePrice,  pl.Service
FROM products p
  LEFT JOIN pricelists pl  ON p.ProductID = pl.ProductID

pricelists table primarykey is ProductID and it is related with products by the ProductID field

I connect the query with a datasource and with dbEdits to fill the values

I am using also FDUpdateSQL and i fill InsertSQL with this sentence:

Insert INTO pricelists (ProductID,  SalePrice,  MinSalePrice,  Service)
VALUES (:ProductID,  :SalePrice,  :MinSalePrice,  :Service)

the DFM file for qryProducts is like this

object qryProducts: TFDQuery
    BeforePost = qryProductsBeforePost
    Connection = FDConnection1
    UpdateOptions.KeyFields = 'ProductID'
    UpdateObject = FDUpdateSQL2
    SQL.Strings = (
      'SELECT'
      '  p.ProductID,' ...

qryProductsBeforePost : qryProductsProductID.Value := fdConnection1.ExecSQLScalar('SELECT IFNULL(MAX(ProductID) + 1, 1) AS NewID FROM products');

and the result is ok = 151 this productid is not exists

I write qryProducts.Append;

and fill the values by dbEdits

then I write qryProducts.Post;

it gives me this error:

[FireDAC][Phys][MySQL] Cannot add or update a child row: a forign key contraint fails(pricelist', Constraint 'pricelists_prod...'foreign key ('productid'' references 'products'(productID'on delete cascade on update cascade.

i tried many solutions as I put a    UpdateOptions.KeyFields = 'ProductID' and remove it later, i put the update table pricelists and so on, with no result