SCOPE_IDENTITY worked before, not anymore

[Zoran565]

I need to get IDENTITY of the record I just inserted so that I can use it for other tables. In one of my older programs I used TMSSQL component with this SQL code:

INSERT INTO xxxx
(
Name,
ZIPCode
)
VALUES
(
:Name,
:ZIPCode
)
SET :RowId=SCOPE_IDENTITY()

Parameters are all IN, except RowId which id IN/OUT. At the beginning of the program I do TMSSQL.Prepare.

It worked fine few months ago. I wrote a new program yesterday and it doesn't work anymore. I get this error:

---
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
---

Did something changed in TMSSQL component? I am using the latest build. Database engine is the same (MS SQL Server 2005).

Or, what is the proper way to get IDENTITY now?

Thanks.
Zoran

[Jackson]

Thank you for information.
We have reproduced the problem and fixed it. This fix will be included in the next SDAC build.

[Zoran565]

Hi Evgeniy

Can I get the fix? I need to run this program today.... and I can't re-install the old version of SDAC because I don't have it anymore (it was overlayed with the latest version).

Is it possible to get updated build? I don't have the source.

Zoran

[Jackson]

Please send us (evgeniym*crlab*com) your license information.

[pgawli]

In version 4.10.0.9 I still cannot retrieve identity value after insert.
Now I have version 4.10.0.10 but results are the same.
Is this problem solved ?
Should I change some options in TMsQuery ?

Best regards.
Piotr Gawlicki

[Sergio Bertolotti]

Dear Piotr,

use TMSQuery.BeforeUpdateExecute event to set parameter type to ptInputOutput for your insert statement.

For example:
Code:
procedure TForm1.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
StatementTypes: TStatementTypes; Params: TMSParams);
begin
if stInsert in StatementTypes then
Params.ParamByName('PARAM_IDENTITY').ParamType := ptInputOutput;
end;

Don't forget to set property TCustomDADataSet.Options.ReturnParams to True.

With this settings and SDAC 4.10.0.10 I don't have any type of problems.

Best regards.

Sergio Bertolotti