Databases
StackOverflow2013
Postsdbo
POHow do you delete, update, etc. tables produced by queries in Delphi ADO?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do you delete, update, etc. tables produced by queries in Delphi ADO?
    text
    copied!<p>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."</p> <p>For example, I created a simple many-to-many relationship between tables called Outline and Reference. The junction or join table is called Note:</p> <pre><code>Outline OutlineID (PK) etc. Reference RefID (PK) etc. Note NoteID (PK) OutlineID RefID NoteText </code></pre> <p>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</p> <pre><code>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; </code></pre> <p>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 <em>think</em> 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?)</p> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload