Search code examples
sql-servervb.netscope-identity

SCOPE_IDENTITY() return 1

I use this code in SQL Server 2012:

INSERT INTO [dbo].[test] ([t]) 
VALUES ('tyy');

SELECT [Id] 
FROM [dbo].[test] 
WHERE ([Id] = SCOPE_IDENTITY())

It returns the last inserted id and it worked well.

But the same code in vb.net 2017 and .net Framework 4.7.2 is not working - for every insert, it returns 1.

This is the code:

Dim id = TestTableAdapter.InsertQuery("nvnh")

Table:

CREATE TABLE [dbo].[test] 
(
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [t]  NVARCHAR (50) NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([Id] ASC)
);
Solution

  • thanks for all. i finally found the solution.

    i use already any from above solutions,

    but the query eqcutemode is NonQuery ,so it give me the count of inserted rows i changed it to Scalar