Devart Forums

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

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

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

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

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

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