Delete rec in detail table

From: J Arty (jarty67_at_hotmail.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 15:21:03 -0500

I have a master details form... when I delete the record in the master, I
want to be able to delete the detail records..

I am using Delphi 7, with sql 7...

In sql I can't put the id of the details table as primary because I need to
be able to put duplicates rec... therefore it is a foreign key with index
yes duplicates allowed...

if I use the relationship in sql and select cascade update s and deletes I
get an error, with the details key...

But when I run my code it deletes the record in the master but not in the
details, I have tested many different ways and saw that it is because the
table primary key is not the field I have in my delete sql, the details
primary key is an auto number...

here is the code I have:

DMmain.ADOConnection1.BeginTrans;
  try
    DMmain.ADOConnection1.Execute('Delete from table1 where table1ID ='+
inttostr(PId) );
    DMmain.ADOConnection1.Execute('Delete from table2 where table2fkid = '+
inttostr(PId));
    DMmain.ADOConnection1.CommitTrans;
  Except
    DMmain.ADOConnection1.RollBackTrans;
  end;//try

I also tried
 with AdoDelete do begin
      Parameters.ParamByName('PID').Value := Pid;
      Execute;
    end;
but both of them execute without errors but when I go to the details table I
see the record is still there...

Any help would be greatly appreciated

Relevant Pages

  • Re: Separate PK in Jxn Tbl?
    ... AutoNumber Field Duplicates Previous Values After You Compact ... Dim Sql As String ... primary key auto-number ensures that forms bound to the table will be ...
    (comp.databases.ms-access)
  • Cant create Primary Key???
    ... I've migrated an access DB of ours over to SQL 2000 and when I try to set ... the primary key I get the error below. ... 'Master' table ... INDEX terminated because a duplicate key was found for index ID 1. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Search Form - Take 2
    ... While in Design mode, highlight all of the fields you want to include in the ... selected fields to the primary key. ... as its due to the fact that the columns in master allow Nulls. ... well enter the same 'keyword' slightly differently, ...
    (microsoft.public.access.gettingstarted)
  • Re: Search Form - Take 2
    ... selected fields to the primary key. ... description column in Recommendations or Accomplishments you'll have some ... as its due to the fact that the columns in master allow Nulls. ... well enter the same 'keyword' slightly differently, ...
    (microsoft.public.access.gettingstarted)