How do you delete, update, etc. Tables created by queries in Delphi ADO?

Question

I think that I am missing something fundamental in working with SQL statements and the Query Delphi ADO component and / or setting up relationships between fields in (Access 2003) databases. I get error messages whenever I want to delete, update, etc. Something more complicated than SQL.Text = "SELECT something from aTable".

For example, I created a simple many-to-many relationship between the Outline and Reference tables. The join or join table is called Note:

Outline OutlineID (PK) etc. Reference RefID (PK) etc. Note NoteID (PK) OutlineID RefID NoteText 

I enabled referential integrity in connections in Access, but did not check the boxes to cascade deletions or updates. Meanwhile, in Delphi my Query.SQL.Text

 SELECT Note.NoteID, Outline.OutlineID, Ref.RefID, Note.NoteText, Ref.Citation, Outline.OutlineText FROM (Note LEFT JOIN Outline ON Outline.OutlineID=Note.OutlineID) LEFT JOIN Ref on Ref.RefID=Note.RefID; 

First, I left the key references in the SELECT statement, creating the "insufficient column column information" error when I tried to delete a record from the summary table. I think I understand: you should select all the fields that db needs for any operations that will be proposed for execution. He cannot delete, update, etc., if he does not know why. (Is it correct?)

So how can I remove an entry from this query? In other words, I want (1) to display a grid showing NoteText, Citation and OutlineText, (2) to select a record from the grid, (3) to do something like clicking the "Delete" button in DBNavigator and (4) to delete a record from the notes table that has the same NoteID and NoteText as the selected record.

Answer 1

Both James L and Hendra provide the essence of how to do what you want. The following is a way to implement it.

 procedure TForm1.ADOQuery1BeforeDelete(DataSet: TDataSet); var SQL : string; begin SQL := 'DELETE FROM [Note] WHERE NoteID='+ DataSet.FieldByName('NoteID').AsString; ADOConnection1.Execute(SQL); TADOQuery(DataSet).ReQuery; Abort; end; 

This will allow TADOQuery.Delete to work correctly. An interrupt is necessary to prevent TADOQuery from deleting the record after it is deleted. The main downside is that TADOQuery.ReQuery does not keep the cursor position, i.e. the current record will be the first record.

Update:

The following attempts to restore the cursor. I don’t like the second query, but it seems like you need to restore the DataSet after trying to restore an invalid bookmark (due to deleting the last record). This worked with my limited testing.

 procedure TForm1.ADOQuery1BeforeDelete(DataSet: TDataSet); var SQL : string; bm : TBookmarkStr; begin SQL := 'DELETE FROM [Note] WHERE NoteID='+ DataSet.FieldByName('NoteID').AsString; bm := Dataset.BookMark; ADOConnection1.Execute(SQL); TADOQuery(DataSet).ReQuery; try Dataset.BookMark := bm; except TADOQuery(DataSet).Requery; DataSet.Last; end; Abort; end; 

Answer 2

If you used TADOTable , then the components handle deletions in the database when you delete them from the TADOTable . However, since you are using TADOQuery , which combines multiple tables, you need to process the database differently.

When you make the record you want to delete the current record in the db grid, it scrolls the TADOQuery cursor to that row in its dataset. Then you can use TADOQuery.Delete to delete the current record. If you write code for the TADOQuery.BeforeDelete event, you can capture the id fields from the record before it is locally deleted locally and using another TADOQuery or TADOCommand , you can create and execute SQL to delete the record from the database.

Since the code that deletes records from the database is in the BeforeDelete event, if an exception occurs and database records are not deleted, the local deletion will also be canceled and the local record will not be deleted - and an error will be displayed (for example, "foreign key violation "...).