Problem with @@IDENTITY in D2005 worked in D7

[Guest]

In D7 and dbExpress i could use the following code to update a ClientDatatSet to the new autoincremented value after an insert.

In D2005 and latest dbExpress it does not work anymore, i allways get back 0 as the new Id.

Does the TSQLConnection use different connections for different Querys/DataSet in D2005 and only one connection in D7?

Is there any other way to get the AutoIncremented value by using dbExpress or any other component?


Example:

I have one TSQLConnection for all Querys and datasets.

I have a TSQLQuery Named IdentityQuery with the following SQL statement:
'SELECT @@IDENTITY'

I have a TSQLDataSet with the following SQL statement:
Select * From Supplier

I have a TDataProvider connected to the above SupplierDataSet
With the following properies changed from default:
In Options
poAutoRefresh = TRUE
poPropogateChanges = TRUE
poUseQuoteChar = FALSE

And Finally i have one TClientDataSet connected to the provider.

In the Provider AfterUpdateRecord Event I have the following code

Code: Select all

procedure TDataMod.UpdateId(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet; UpdateKind: TUpdateKind);
begin
 if UpdateKind = ukInsert then
  begin
    IdentityDataSet.Active := FALSE;
    IdentityDataSet.Active := TRUE;
    DeltaDS.Fields[0].NewValue := IdentityDataSet.Fields[0].AsInteger;  // Allways 0 !!!!!?
  end;
end;

[Ikar]

May be you use Fetchall = False? If not then please send us (dbxsda*crlab*com) complete small sample to demonstrate problem and include script to create and fill tables.

[Guest]

I have send you an example to support*crlab*com

[Ikar]

This is Delphi 2005 bug. Please see changes in NormalizeBcd function (unit FMTBcd) for details.

Use

select cast(@@IDENTITY as INT)

instead of

select @@IDENTITY

[Guest]

Works fine, many thanks I could have try to solve this for weeks without your excellent help.