Zeoslib Portal

Posted: **01.08.2006, 06:33**

[font=Courier New]

Hi All,

I´m having a error message using master detail relationship and firebird.
When i apply updates i get an "foreign key violation'

The primary key value on master is generated in a before insert trigger on firebird server.

The problem is, when changes are applied on details, the primary key value on master is not updated with the value generated by the trigger.

I have a master table with 2 details.

I set the details properties like this :

Code: Select all

  DataSource := dsCadCli;  // master datasource
  IndexfieldName := 'IDCAD';    // foreign key on detail table
  MasterFields := 'ID';  // primary key on master table
  MasterSource := dsCadCli; // same above

On Save button of my application i put this code :

Code: Select all

    qryCadCli.DisableControls;
    try try
      dbConnection.StartTransaction;
      if( (qryCadCli.State = dsEdit) OR (qryCadCli.State = dsInsert) ) then qryCadCli.Post;
      if( (qryCliEnd.State = dsEdit) OR (qryCliEnd.State = dsInsert) ) then qryCliEnd.Post;
      if( (qryCliTel.State = dsEdit) OR (qryCliTel.State = dsInsert) ) then qryCliTel.Post;
      qryCadCli.ApplyUpdates;    // master table
      qryCliEnd.ApplyUpdates;     // detail table
      qryCliTel.ApplyUpdates;      // detail table
      dbConnection.Commit;
      Result := True;
    except
      dbConnection.Rollback;
      raise;
    end;
    finally
      qryCadCli.EnableControls;
    end;

On master new record event, this code :

Code: Select all

procedure TdmDB.qryCadCliNewRecord(DataSet: TDataSet);
begin
  DataSet.FieldByName('ID').AsString          := '-1';  // this is the primary key
end;  // TdmDB.qryCadCliNewRecord

On detail new record event, this code :

Code: Select all

procedure TdmDB.qryCliEndNewRecord(DataSet: TDataSet);
begin
  qryCliEnd.FieldByName('IDEND').AsString  := '-1';
  qryCliEnd.FieldByName('IDCAD').AsString  := qryCadCli.FieldByName('ID').AsString;  // this is the foreign key
end;  // TdmDB.qryCliEndNewRecord

Anyone can help me with some direction on this problem ?

Thanks in advanced,

Gustavo.
[/font]

Posted: **01.08.2006, 22:30**

Hello Gustavo! I'm Gustavo too

Take a look here: http://zeos.firmos.at/viewtopic.php?p=2199#2199
This post is from april, and a user asked about master detail relationship. I'm sending you there becouse isn't needed to link the MasterSource property. At least i've never linked it with the master, and everything works.

I've Attached with this post a sample program, take a look!
The database you can create with the following metadata:

Code: Select all

SET SQL DIALECT 3;

SET NAMES NONE;

CREATE DATABASE 'C:\Delphi\testes\masterdetail\pedidos.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET NONE;

CREATE TABLE ITENS (
    NUMERO     INTEGER NOT NULL,
    DESCRICAO  VARCHAR(30),
    VALOR      DECIMAL(15,2)
);


CREATE TABLE PEDIDOS (
    NUMERO   INTEGER NOT NULL,
    CLIENTE  VARCHAR(50),
    DATA     DATE
);

ALTER TABLE PEDIDOS ADD CONSTRAINT PK_PEDIDOS PRIMARY KEY (NUMERO);

CREATE INDEX IDX_INTENS ON ITENS (NUMERO);

Zeoslib Portal

Master/Detail , Firebird & Delphi7

Master/Detail , Firebird & Delphi7