TADOQuery Join tables - Insert \ Delete records from result

Question

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 ?

Answer 1

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