SQL delete detail records 
Author Message
 SQL delete detail records

I'm trying to delete detail records that match current master record but get
'error creating cursor handle'.  I'm using D3 and Paradox.

procedure MastertableBeforeDelete(DataSet: TDataSet);
begin
   with query do
    begin
    close;
    SQL.clear;
    SQL.Add('delete from table2 where table2."field" = :field);
     prepare;
       paramByName('field').asstring :=
mastertable.fieldbyname('field').asstring;
     open;
   end;
end;

There will not always be records in the detail table and if you have any
suggestions how to handle that I would appreciate it.

thanks a bunch

Dianne



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL delete detail records
Dianne,

   Replace the "Open" statement with "ExecSql"

  Open, expects a cursor handle to come back from the BDE.  Here you are issuing
a command, and as such, opening not a thing.

   Alternatively,
   you can

   try
     Open
   except
   end;

   and ignore the error.

  Lotsa Luck.

 Joe.

Quote:

> I'm trying to delete detail records that match current master record but get
> 'error creating cursor handle'.  I'm using D3 and Paradox.

> procedure MastertableBeforeDelete(DataSet: TDataSet);
> begin
>    with query do
>     begin
>     close;
>     SQL.clear;
>     SQL.Add('delete from table2 where table2."field" = :field);
>      prepare;
>        paramByName('field').asstring :=
> mastertable.fieldbyname('field').asstring;
>      open;
>    end;
> end;

> There will not always be records in the detail table and if you have any
> suggestions how to handle that I would appreciate it.

> thanks a bunch

> Dianne



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL delete detail records

Quote:
>    begin
>    close;
>    SQL.clear;
>    SQL.Add('delete from table2 where table2."field" = :field);
>     prepare;
>       paramByName('field').asstring :=
>mastertable.fieldbyname('field').asstring;

Another thing you may need to be concerned with is the transaction life.  What
if the master does not delete?  Do you still want to delete the detail records?

I like routines like:

with masterQry do begin
  SQL.Clear;
{ I don't use parameters if I have to create the SQL statement anyway - just
make it part of the statement }
  SQL.Add( 'delete from mastertbl where MyID = ''5''');
  MyDbase.StartTransaction
  try
    execSQL;
    if RowsAffected = 1 then begin
      with detailQry do begin
        SQL.Clear;
        SQL.Add('delete from detailtbl where ParentID=''5''')
        execSQL;
    { no need to check for rowsaffected - if there were no details - who cares
if it failed }
      end;  {with detailQry}

     MyDBase.Commit;
    else
      raise exception.create('No master with ID=''5''');
  except
     on E: Exception do begin
       MyDBase.Rollback;
       MessageDlg( 'Could not delete' + #10 + E.Message, mtWarning, [mbOK], 0
);
    end;  {on Exception }
  end;  { except }

HTH - Lance



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL delete detail records


Wed, 18 Jun 1902 08:00:00 GMT  
 SQL delete detail records
Thanks for all the help.  It now works great.


Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Deleting a detail record using Delphi 1.0 on a MS Access 2.0 database

2. how to delete master/detail record

3. Deleting records in master-detail database form

4. Deleting a master-detail record

5. Master-detail-detail-detial with Cascade delete

6. Counting Detail records with SQL or Recordcount

7. existence of detail record in detail table

8. HELP: Master-detail form: sequence of detail records

9. Sorting detail records in a master detail relation.

10. Problem when inserting records in the detail table (master/detail relation)

11. Using SQL to delete duplicate records

12. delete record in sql for delphi

 

 
Powered by phpBB® Forum Software