TADOQuery Join Tables - Insert \ Delete Records from the Result

Question

Using the same TADOQuery i, pull records from two different tables using the 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 TDBGrid to successfully publish field updates in my MSSQL database.

Since there is a link to a foreign key ( FK_Courier_Identifier with Courier_Identifier ), I get an error when inserting an entry,

Unable to Insert Null into column "FK_Courier_Identifier", table Courier_VT; column does not allow null

but the entry is published in the Courier table, I know that before publication I need to assign Courier_Identifier to FK_Courier_Identifier , but not how and where to do it

How do we insert \ delete entries in this script? Can I use one TADOQuery ?

Answer 1

AFAIK TADOQuery cannot process insert / delete / update statements when multiple tables are joined. The reason for this is because he cannot know which table he should 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 clause ( ODAC components are one example) or you need to add a second "SQL update" component related to your query that will contain DML clauses ( Zeos - this is one example of components that use this approach).

The foregoing is probably the best way to use the BeforeDelete and BeforePost event handlers to process your script. Basically, you should use them to issue a DML clause, execute it using some storedproc or sql component, and then interrupt the event handler. Check the accepted answer to this SO question for more information and sample code.

---

EDIT : if your code can handle updates and delete, as you say in your comment, then the problem is only to assign FK_Courier_Identifier on insertion (should have read the question more carefully ...), which you can solve 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 assume that the field is varchar and that you know before inserting the Courier ID value into the database.

NTN