Deleteing from some tables....

I have made a simple database containing two tables, one is the master of the other.

The first one is a table of files. The second is a table of discs that these files are stored on.

Files.db and Discs.db.

I have a query for showing this in a TDBGrid with the SQL below:

Select Files.FileName, Discs.Label from Files, Discs
  where Files.Disc = Discs.ID
order by Files.FileName, Discs.Label

This query has an UpdateSQL connected to it.

I also have a separate Query for the discs with the following SQL:

Select ID, Label from Discs

This query is just for showing a list of the discs in a separate TDBGrid.

Now I want to be able to select a disc from the disc-grid and have my program to delete that disc from the database including all of its detail files. For this I have yet another query, the EraserQuery. Here's the SQL for that one:

Delete from Files
  where Disc = :CurrentDisc


"CurrentDisc" is defined below, it is for the program to know which disc the user has selected. -

procedure TModules.DiscQueryBeforeDelete(DataSet: TDataSet);
begin
  EraserQuery.ParamByName('CurrentDisc').AsInteger := Discs.FieldByName('ID').AsInteger;
  EraserQuery.Prepare;
  EraserQuery.ExecSQL;
end;


This works fine with the deletion and all, but it doesn't update the first query, the one showing all files and which disc they're on. It updates the tables and I have to restart my program for it to get updated. What can I do to fix this? Is there a better way deleting detailrecords?

regards,

Johan
LVL 1
sagerydAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
hi zif,

recordnumber is not on all databases properly filled,
also it is not properly filled, if not all records are fetched.

sageryd,
if zif suggestion will not work,
then save a unique fieldvalue into a var and
after open the query do a locate with it

meikl
0
 
kretzschmarCommented:
does a close and reopen of the first query not solve the problem without starting the whole app?
0
 
ZifNabCommented:
A Refresh should normally do fine.

(Question: Is the query updated when you move a record in the first table and then move back to the record)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
sagerydAuthor Commented:
It works when i close and re-open the first query, but then I lose the selection the use might have. Is there another way? Refresh doesn't work.

johan
0
 
ZifNabCommented:
Hi sageryd,

perhaps bookmarking it, before closing? (don't know if this works)

Or, get the recordnumber  (thus position of record)

and then after closing do a  

query.First (not needed I think)
query.MoveBy(recordnumber) ?

Zif.
0
 
sagerydAuthor Commented:
well, ok thanx! I guess Meikl should get the points as he was the first to give an answer.

Thanks again!


regards,

Johan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.