TIBCQuery Update-Problem

[michaelJ]

Hello,

I have a table with the following structure in Firebird V1.5:

Code: Select all

CREATE TABLE SHPMRKMTG (
    MNF_ID   INTEGER NOT NULL,
    SHOPID   INTEGER NOT NULL,
    FLAECHE  INTEGER
);
ALTER TABLE SHPMRKMTG ADD CONSTRAINT PK_SHPMRKMTG PRIMARY KEY (MNF_ID, SHOPID);

ALTER TABLE SHPMRKMTG ADD CONSTRAINT FK_SHPMRKMTG_1 FOREIGN KEY (SHOPID) REFERENCES SHOPS (SHOPID);
ALTER TABLE SHPMRKMTG ADD CONSTRAINT FK_SHPMRKMTG_2 FOREIGN KEY (MNF_ID) REFERENCES PARTNERS (ID1_PRT);

The IBCQuery contains the following SQL:

Code: Select all

select MM.ABR1 as MARKEMTG,SM.FLAECHE, SM.MNF_ID 
from SHPMRKMTG SM, PARTNERS MM
where SM.MNF_ID=MM.ID1_PRT AND MM.ID1_TPP=1
AND SM.SHOPID=:SHOPID
order by MM.ABR1

When I update the Value FLAECHE I monitor the following statement:

Code: Select all

UPDATE SHPMRKMTG SET FLAECHE = ? WHERE FLAECHE = ? AND MNF_ID = ?

and the error: Q_SHPMRKMTG Field OLD_SHOPID not found.

But the automatic statement have to be:

Code: Select all

UPDATE SHOPMRK SET FLAECHE = ? WHERE MNF_ID= ? AND SHOPID = ?

What ist the reason for the wrong created UpdateStatement and what could be s solution? UPDATINGTABLE is correctly set to SHPMRKMTG.

Thanks

Michael

[AndreyZ]

Hello,

To solve the problem, you should include all primary key fields into your initial query:

Code: Select all

select MM.ABR1 as MARKEMTG,SM.FLAECHE, SM.MNF_ID, SM.SHOPID 
from SHPMRKMTG SM, PARTNERS MM 
where SM.MNF_ID=MM.ID1_PRT AND MM.ID1_TPP=1 
AND SM.SHOPID=:SHOPID 
order by MM.ABR1

, and set the TIBCQuery.KeyFields property to the primary key fields:

Code: Select all

IBCQuery.KeyFields := 'MNF_ID;SHOPID';

[michaelJ]

Thank you, that workx!