Identity Field

rcm · Post by **rcm** » Sun 26 Jun 2005 20:45

Can some one help me, where do i put my code to get the Identity return value after performing an Insert whit Tquery.

My Insert statement haves the SET 1: = SCOPE_IDENTITY()

And also i have the Tquery1BeforeUpdateExecute event statment like so Params.ParamByName('IDPAY').ParamType := ptInputOutput;

But i still cant get a return value back.

TIA

Ikar · Post by **Ikar** » Mon 27 Jun 2005 06:21

Please see StoredProcUpdates Demos for example

rcm · Post by **rcm** » Mon 27 Jun 2005 06:42

I did see the sample but being a storprocedure is different or to me it seems different and the only result i get is a big 0 that is what my variable is getting all the other work is ok.

Can you please send me a sample because there is no sample with Tquery direct use.

Where can a i get more samples on using all the components of SDAC ?

The product is 100% excellent but it realy needs more samples at least one to see how the components work with all the options there are.

In the StoreProc sample you are using TMSSQL component and im using MSquery so thats got to be a different thing.

TIA

Ikar · Post by **Ikar** » Mon 27 Jun 2005 12:09

> Can some one help me, where do i put my code to get the Identity return value
> after performing an Insert whit Tquery.

If you leave SQLInsert empty then Identity value will be obtained automatically and will be inserted to corresponding field of TMSQuery.

> Where can a i get more samples on using all the components of SDAC ?

We don't have another samples except including in installation package.

rcm · Post by **rcm** » Mon 27 Jun 2005 12:56

Yes I have SQLInsert empty and yes the field does have the new ID but how do i tell the INSERT instruction to return it to me.

In the sample the storeprocedure in the last part says RETURN @@Identity

In my INSERT statement i put SET :IDPAY = INDENTITY_SCOPE() the last part of the INSERT that is what the manual says to do.

In the StoreProcedure samples if you go and see de SQLInsert of the TMSquery you will find that it says {:Identity = CALL StoreProcedure() }

But in my .PAS file i can not put IDPAY := Tquery.Execute;

Still no Luck

Ikar · Post by **Ikar** » Mon 27 Jun 2005 14:47

> but how do i tell the INSERT instruction to return it to me.

Get its value from the proper field of the main query.

rcm · Post by **rcm** » Mon 27 Jun 2005 15:19

First of All Thank you for your fast replay

In the INSERT statement when a field is of IDENTITY nature does not go in the statement.

So if i try:
nIdPay := Tquery1.FieldByName('IDPAY').AsInteger;
I get an error saying that the field is not declared.

And I also tried:
nIdPay := Tquery1.Params.ParamByName('IDPAY').AsInteger;
But does not give nothing.

TIA
Ramiro

Ikar · Post by **Ikar** » Wed 29 Jun 2005 08:53

Please specify a sequence that you execute including filling properties of Tquery1.

Guest · Post by **Guest** » Fri 08 Jul 2005 03:27

New Question:
How to retrieve identity if i use 'instead of insert' trigger?
Thank you.

Ikar · Post by **Ikar** » Fri 08 Jul 2005 14:00

SDAC gets a value of Identity field based on SCOPE_IDENTITY() function. You can see detailed description of abilities and restrictions of this function in MSDN.