error with scope_identity and parameter adoquery - MS SQL

Something very strange happens when I use parambyname and try to get the scope_identity().

I use MS SQL 2000 and there is a function in it that can return the last session number created by a
autonumber field after inserted.

This way all work fine:

Query.Sql.Add('INSERT INTO TABLE');
Query.Sql.Add('(FIELD1, FIELD2)');
Query.Sql.Add('VALUES');
Query.Sql.Add('"X2474","TR7485"');
Query.Execute

Query.Sql.Clear;
Query.Sql.Add('SELECT SCOPE_IDENTITY AS ID');
Query.Open;

ID := Query.FieldByName('ID').AsInteger;

Query.Close;

It returns the correct number created after inserte

But If I make parambyname

Query.Sql.Add('INSERT INTO TABLE');
Query.Sql.Add('(FIELD1, FIELD2)');
Query.Sql.Add('VALUES');
Query.Sql.Add(':P01, :P02');
Query.Parameters.ParamByName('P01').Value := 'X24578';
Query.Parameters.ParamByName('P01').Value := 'X74854';
Query.Execute

It insertes the new register

Query.Sql.Clear;
Query.Sql.Add('SELECT SCOPE_IDENTITY AS ID');
Query.Open;

ID := Query.FieldByName('ID').AsInteger;

But it returns 0 - Always when I use parambyname

I use delphi 7 with updated patch.

Is it a bug?
Alexandre
LVL 1
hidrauAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcsentinelCommented:
what happens when you use

'SELECT @@IDENTITY AS ID' for the second statement
hidrauAuthor Commented:
So, If I use the @@Identity I will get not the session ID but the last ID inserted that could probably be another user inserted.
Did you test it? If I am not wrong, Identity also returns 0 when used with parameters

Thanks
BlackTigerXCommented:
the definition for scope_identity is:
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

seems like using parameters puts it in another scope, I have always used stored procedures, works fine there
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

_Katka_Commented:
Hi PCSentinel, like BTX said it has to be in the same transaction, try using:

CREATE PROCEDURE dbo.TABLE
 @Value1 Field1Type,
 @Value2 Field2Type
AS
  INSERT INTO TABLE (FIELD1,FIELD2) VALUES (@Value1,@Value2)
  RETURN SCOPE_IDENTITY
GO

regards,
Kate
_Katka_Commented:
I wrote it by hand so here is more sophisticated version:

In MSSQL:

CREATE PROCEDURE dbo.TABLE
 @Value1 Field1Type,
 @Value2 Field2Type
AS
  BEGIN TRANSACTION
  INSERT INTO TABLE (FIELD1,FIELD2) VALUES (@Value1,@Value2)
  DECLARE @@INDEX int
  SET @@INDEX=SCOPE_IDENTITY()
  COMMIT
  RETURN @@INDEX
GO

In Delphi:

..
with StoredProc1 do
begin
   StoredProcName:='YourProcedureName';
   Prepare;
   <set parameters here>
   ExecProc;
   Index:=Params[0].AsInteger;     << here's stored your index
end;
..

regards,
Kate

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcsentinelCommented:
Katka, why are you replying to me?

hidrau, as you can see from all the above comments I think the fact that either of your queries returns an identity at all is lucky normally you would do as BlackTigerX and Katka have shown and that is to use an SP and return the identity as a parameter from the SP

regards
_Katka_Commented:
Sorry PCSentinel, I guess it was some kind of mind-shift :))))
hidrauAuthor Commented:
Thanks Katka
_Katka_Commented:
You're welcome :)

best regards,
Kate
hidrauAuthor Commented:
Kate, I would like to get in touch with you, your user skype is correct? Do you speak english?
_Katka_Commented:
Hi, I'm only using the Skype at work (ordered by Boss) but I'm not speaking well..let's say almost none :)
But my written English is much better.. I would prefer the contact on ICQ or by mail :)

regards,
Kate
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.