Link to home
Start Free TrialLog in
Avatar of hidrau
hidrauFlag for Brazil

asked on

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
Avatar of pcsentinel
pcsentinel

what happens when you use

'SELECT @@IDENTITY AS ID' for the second statement
Avatar of hidrau

ASKER

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
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
Avatar of _Katka_
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
ASKER CERTIFIED SOLUTION
Avatar of _Katka_
_Katka_
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Sorry PCSentinel, I guess it was some kind of mind-shift :))))
Avatar of hidrau

ASKER

Thanks Katka
You're welcome :)

best regards,
Kate
Avatar of hidrau

ASKER

Kate, I would like to get in touch with you, your user skype is correct? Do you speak english?
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