hidrau
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","TR 7485"');
Query.Execute
Query.Sql.Clear;
Query.Sql.Add('SELECT SCOPE_IDENTITY AS ID');
Query.Open;
ID := Query.FieldByName('ID').As Integer;
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.ParamByNa me('P01'). Value := 'X24578';
Query.Parameters.ParamByNa me('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').As Integer;
But it returns 0 - Always when I use parambyname
I use delphi 7 with updated patch.
Is it a bug?
Alexandre
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","TR
Query.Execute
Query.Sql.Clear;
Query.Sql.Add('SELECT SCOPE_IDENTITY AS ID');
Query.Open;
ID := Query.FieldByName('ID').As
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.ParamByNa
Query.Parameters.ParamByNa
Query.Execute
It insertes the new register
Query.Sql.Clear;
Query.Sql.Add('SELECT SCOPE_IDENTITY AS ID');
Query.Open;
ID := Query.FieldByName('ID').As
But it returns 0 - Always when I use parambyname
I use delphi 7 with updated patch.
Is it a bug?
Alexandre
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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 :))))
ASKER
Thanks Katka
You're welcome :)
best regards,
Kate
best regards,
Kate
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
But my written English is much better.. I would prefer the contact on ICQ or by mail :)
regards,
Kate
'SELECT @@IDENTITY AS ID' for the second statement