Cipto,



is it correct,that you want to have a master-detail-relationship between a


qrTbBeliM(aster) and qrTbBeliD(etail), where a column "idTbBeliM" in the
detail query links to the "id" column in the master query? And both "id"
columns
in the queries are primary keys?



Then you should set up your datasets and datasources in this way:



Set MasterSource of your detail dataset to the master's datasource.



If you use your parameterized detail query, define

MasterParamLinks: id (the name of the param - no quotes)
= [alias or table_name]."id" (master column)

[Translate it into: the param id is feeded by the value of Master."id"]



Or if you remove the Where-Clause in the detail dataset ("where
d."idTbBeliM" = :"id"),
set MasterLinks: [alias or table_name]."idTbBeliM"=
[alias or table_name]."id"



(In the schema I would have defined a foreign key constraint for the detail
table's "idTbBeliM"
referencing the masters "id".)



If you did not define KeyLinks in your datasets, they should be set to the
primary keys.

In your case it is in In both datasets the "id"-column.



In order to let IBO assign the primary key to the new row in your detail
dataset, set the
GeneratorLinks: [alias or table_name]."id"=name_of_generator.



IBO should do the rest and it should not be necessary to write
OnBeforePost-Events in
order to achieve such fundamental tasks as inserting a row in a detail
dataset, if only
the setup of the datasets properties is correct.



(By the way - it is recommended to avoid double quotes if they are not
really necessary; that means,
if you didn't use them in your schema e.g. to make identifiers case
sensitive. In fact IMO it makes handling
statements much more complicated and prone to errors. )



In your database schema, you should have BeforeInsert triggers for the
involved tables in the way it has
often been taught:



If (new.id is null) then
new.id = gen_id(name_of_generator, 1) ;

or (Firebird 2.0 onward recommended syntax)
new.id = next value for name_of_generator;






Herbert



Von: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] Im Auftrag
von Cipto
Gesendet: Samstag, 5. Mai 2012 12:52
An: <mailto:IBObjects@yahoogroups.com> IBObjects@yahoogroups.com
Betreff: Re: [IBO] Re: PK field that auto filled with wrong generator value





conIBO is a TIBODatabase component with tiCommitted isolation

Yes I have UpdateSQL on qrTbBeliD, the code is generate automatically by
TIBOQuery feature.

The "id" field of tbBeliD.ID is never visible to users when they using this
program. So the user cann't make it null when it already has a value from
generator "genTbBeliD". Yes I fill tbBeliD.ID on event OnBeforePost of
qrTbBeliD, the value from this OnBeforePost event is never wrong.

The problem is when user add a new record of qrTbBeliD, the value of
tbBeliD.ID is automatically filled by tbBeliM.ID which is I don't write
any single code from Delphi or from trigger of that table to make this
happen. That looks like TIBOQuery automatically do that.

If it's automatically do by TIBOQuery then it's wrong because the relation
between the master (qrTbBeliM) and the detail (qrTbBeliD) is on "id" field
of qrTbBeliM and the "idTbBeliM" field of qrTbBeliD.

I put qrTbBeliM and qrTbBeliD in a master detail relationship (both
qrTbBeliM and qrTbBeliD are the name of TIBOQuery component).
The master detail relationship that I do is:
1. Set the datasource property of qrTbBeliD to the TDatasource of qrTbBeliM
2. Make the SQL property of qrTbBeliD to have a param to "id". Here is the
SQL property of qrTbBeliD:
select d."id", d."idTbBeliM", d."idTbBarang", d."idTbGudang", d."qty",
d."satuan", d."konversi", d."qtyKecil",
d."hargaSat", d."disc", d."subTotal", d."ongkosKirim",
b."kode", b."nama", b."partNumber", b."satKecil"
from "tbBeliD" d
join "tbBarang" b on b."id"=d."idTbBarang"
where d."idTbBeliM"=:"id" // ====> Here the SQL that said that this
qrTbBeliD "id" param refer to qrTbBeliM.ID
order by d."id"

Hope make you clear at this point. Thank you for your patience. I'm from
Indonesia. TIA

----- Original Message -----
From: russellbelding
To: <mailto:IBObjects%40yahoogroups.com> IBObjects@yahoogroups.com
Sent: Saturday, May 05, 2012 5:30 AM
Subject: [IBO] Re: PK field that auto filled with wrong generator value

Hello Cipto

There are some details in your post I do not get. What is conIBO? I don't
want to guess.

Other details could influence the problem. In your TIBOQuery named qrTbBeliD

do you have Update SQL?

If tbBeliD.ID is the primary key for table tbBeliD then when qrtbBeliD
records will never be null at the time they are loaded into your client
program. The OnPostEvent will succeed in changing the ID
value only if this value is null. So the ID value will change by this event
only if it has been edited from a value to null.

When your client adds a new record to table tbBeliD by entering a new record

into qrtbBeliD and if the ID value is null then when this new record is
posted the event fills the ID value from generator gentbBeliD. Is this what
is happening? If so how are you determining that the ID value is wrong?

Maybe my comments will prompt some thought to you that leads to insight. (I
don't get the full picture.)

HTH.

--- In <mailto:IBObjects%40yahoogroups.com> IBObjects@yahoogroups.com,
"Cipto" < <mailto:cipto.milis@...> cipto.milis@...> wrote:
>
> Sorry for my bad English. That's not what I mean.
>
> I don't have any trigger on "tbBeliD" table. Yes, the d."id" is the
> primary
> key, and I will assign it's value from Delphi (OnBeforePost event of
> qrTbBeliD (TIBOQuery component)) with the same meaning code as you write
> from trigger:
>
> if qrTbBeliDid.IsNull then
> qrTbBeliDid.AsLargeInt:=conIBO.Gen_ID('"genTbBeliD"', 1);
>
>
> The question is when the program add a new record of qrTbBeliD, why the
> "id"
> field of qrTbBeliD always auto filled with "id" field from qrTbBeliM?
> There
> is no single code on my trigger or on OnNewRecord event of qrTbBeliD that
> assign that value. It's automatically filled by the TIBOQuery component
> and
> it's wrong.
>
>
> ----- Original Message -----
> From: russellbelding
> To: <mailto:IBObjects%40yahoogroups.com> IBObjects@yahoogroups.com
> Sent: Friday, May 04, 2012 3:14 PM
> Subject: [IBO] Re: PK field that auto filled with wrong generator value
>
> When you use event OnBeforePost the action is taken before the TIBO_NN
> object has data changes posted. Each time you change the table used and
> save
> the changes a post is done. So the field D.ID is changed each time the
> table
> changes.
>
> If D.ID is the primary key for your table and you want a unique key in
> this
> field each time you insert a record you could use a "before insert"
> trigger
> on the table.
>
> if (new.ID is null) then
> new.ID = gen_id(generatorname, 1);
>
> > > I also have a generator called "genTbBeliD".
> > >
> > > Now the problem is:
> > > Everytime I insert a new record on "tbBeliD" (just still in insert
> > > condition, before post) why the field d."id" is automatically filled
> > > with an integer value that I don't know where it come from??
> > >
> > > I'm not set any propoerty of TIBO_Query or TIBO_Database to
> > > automatically fill the "id" field from any generator, because I
> > > explicitly fill d."id" value onBeforePost of TIBO_Query with this
> > > statement:
> > > if qrTbBeliDid.IsNull then
> > > qrTbBeliDid.AsLargeInt:=conIBO.Gen_ID('"genTbBeliD"', 1);
> > >
> > > Any clue??
> > >
> > > TIA





[Non-text portions of this message have been removed]