Jump to content
Osama Ghazal

TFDUpdateSQL Problem onInsert

Recommended Posts

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

Share this post


Link to post

@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!

Edited by programmerdelphi2k

Share this post


Link to post

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

Share this post


Link to post

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)

Share this post


Link to post
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.

Share this post


Link to post
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

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×