Using a single TADOQuery
i pull records from two different tables using a left outer join:
Select M*, D.* from Courier M Left outer join Courier_VT D on M.Courier_Identifier = D.FK_Courier_Identifier
I use a TDBGrid
to successfully post field updates to my MSSQL DB.
Since there is foreign key reference (FK_Courier_Identifier
with Courier_Identifier
) I get a error when I insert a record,
Cannot Insert the value Null in to column 'FK_Courier_Identifier', table Courier_VT; column does not allow null
but a record is posted in Courier table, i do know that i need to assign the Courier_Identifier
to FK_Courier_Identifier
before posting but don't how and where to do it
How do we Insert \ Delete records in this scenario ? Is it possible to achieve using a single TADOQuery
?
AFAIK TADOQuery is unable to handle insert/delete/update statements when multiple tables are joined. The reason behind it is that it can not know which table it has to update or how to do it.
The usual approach with other database access components is to either provide a property for each type of DML sentence (ODAC components are one example) or you have to add a second "update SQL" component linked to your query which will contain the DML sentences (Zeos is one example of components that use this approach).
Said this, probably your best bet is to use the BeforeDelete and BeforePost event handlers to treat your scenario. Basically you would use them to issue the DML sentence, execute it with some storedproc or sql component and then abort the event handler. Check the accepted answer to this SO question for more information and a code sample.
EDIT: if your code can handle the updates and deletes as you say in your comment, then the problem only lies with the assignment of the FK_Courier_Identifier
on inserting (should have read the question more carefully...), which you can solve by using the OnBeforePost event handler:
procedure TMyForm.MyADOQueryBeforePost(Sender: TObject);
begin
MyADOQuery.FieldByName('FK_Courier_Identifier').AsString := CourierId;
end;
Of course, you will need to adapt this code since here I am supposing the field is a varchar
and that you know prior to inserting in the database the value of Courier ID.
HTH
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With