How do you delete, update, etc. tables produced by queries in Delphi ADO?

Question

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

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

Outline
  OutlineID (PK)
  etc.

Reference
  RefID (PK)
  etc.

Note
  NoteID (PK)
  OutlineID
  RefID
  NoteText

I enforced referential integrity on the joins in Access, but didn't tick the checkboxes to cascade deletes or updates. Meanwhile, over in Delphi my Query.SQL.Text is

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;

Initially I left out the references to keys in the SELECT statement, producing an 'insufficient key column info' error when I tried deleting a record from the resulting table. I think I understand: you have to SELECT all the fields the db will need for any operations it will be asked to perform. It can't delete, update, etc. joined fields if it doesn't know what's joined to what. (Is this right?)

So, then, how do I go about deleting a record from this query? In other words, I want to (1) display a grid showing NoteText, Citation, and OutlineText, (2) select a record from the grid, (3) do something like click the Delete button on a DBNavigator, and (4) delete the record from the Note table that has the same NoteID and NoteText as the selected record.

Are you using TADOQuery and what do you mean by "deleting a record from this query"? — Marcus Adams, Jul 27 '12 at 16:46
Yes, TADOQuery. And by 'deleting a record...' I can see your confusion; thanks for pointing this out. See my edit. — Al C, Jul 27 '12 at 16:59
When I use TADOQuery with join in the sql & displayed in a dbgrid, I did not use the query itself to delete record. I use a TADOCommand with parameter to do the delete. I assigned the parameter with value from the dbgrid.datasource.dataset.fieldbyname('param').asString (or whatever field type). After that I execute the command, and refresh the grid. — Hendra, Jul 28 '12 at 04:16

crefird · Accepted Answer · 2012-07-29T18:52:36.363

2

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 properly. The Abort is necessary to prevent TADOQuery from also trying to delete the record after you have deleted it. The primary down side is that the TADOQuery.ReQuery does not preserve the cursor position, i.e. the current record will be the first record.

Update:

The following attempts to restore the cursor. I do not like the second Requery, but it appears to be necessary to restore the DataSet after attempting to restore a 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;

edited Jul 29 '12 at 18:52

answered Jul 29 '12 at 01:46

crefird

1,590
11
17

Regarding preserving the cursor position, I have not tried this, but will this work? How about getting the bookmark of the current record prior delete, do the code above, goto the saved bookmark (will go to next record), then free the bookmark and abort? I don't know if access database is unidirectional or not. – Hendra Jul 29 '12 at 03:00
@Hendrea. Your idea about capturing the bookmark of the current record and then restoring it after the delete works until it is the last record that is deleted. With D2007, I could not avoid an exception with TBookmark even using TADOQuery.BookValid inside a try .. except. I got TBookmarkStr to work, but do not like some of the steps necessary to make it work (within my limited testing). I have updated my answer with the code. As for you comment about Access being unidirectional, that is a function of TADQuery rather than the database. – crefird Jul 29 '12 at 18:48
I got idea about avoiding the second requery. How about only go to the bookmark if it is not the last record? So, before execute the sql above, check first for last record (i.e. dataset.next; if dataset.eof then set isLastRecord = true; dataset.prior;). After requery, replace try...except with if not isLastRecord then Dataset.Bookmark := bm else Dataset.last; So, the second requery is not needed. – Hendra Jul 30 '12 at 05:31
1

Thank you for your response @crefird. Combined with the other responses, it is just the sort of info I was looking for. I will be able to use it in the future. ... FWIW, I ended up addressing my initial issue via ADODataSet's 'Unique Table' property. See http://stackoverflow.com/questions/7981718/adodataset-deleting-from-joined-table for more info. – Al C Jul 30 '12 at 14:52
@Hendra I will give your sugestion a try. But I think Al C found a better solution with the 'Unique Table' property. – crefird Jul 30 '12 at 16:29
Yes, after seeing the unique table property, it looks like a better solution – Hendra Jul 30 '12 at 20:38

score 0 · Answer 2 · answered Jul 27 '12 at 17:41

If you were using a TADOTable, then the components handle the deletes in the database when you delete them from the TADOTable dataset. However, since you are using a TADOQuery that joins multiple tables, you need to handle the database delete differently.

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

Since the code that deletes the records from the database is in the BeforeDelete event, if an exception occurs and the database records aren't deleted, the local delete will be cancelled too, and the local record will not be deleted -- and the error will be displayed (e.g., 'foreign key violation'...).

L -- I tried your suggestion, executing the DELETE statement through the ADOConnection component I'm already using. I receive an error saying a row can't be located for updating, that values may have changed since it was last read. Interestingly, the record is nevertheless deleted--but so is a record in one of the other tables. — Al C, Jul 27 '12 at 18:12
It sounds like the original `TADOQuery` is trying to delete it from the database too, after you do the delete manually. Perhaps just doing the `TADOQuery.Delete` without doing anything in the `BeforeDelete` event is sufficient? I don't use the ADO components very often -- maybe they handle the updates/deletes if the query is not too complex... Perhaps the `TADOQuery` is deleting records from all participating tables? If you just want the *one* record deleted, you need to find a way to turn off the auto-updates from the original `TADOQuery`... and handle it manually in `BeforeDelete`... — James L., Jul 27 '12 at 18:20

How do you delete, update, etc. tables produced by queries in Delphi ADO?

2 Answers2

Linked