Getting ID after insert in SQL Server

Question

I use this stored procedure to make insert and return the id of inserted row

ALTER PROCEDURE [dbo].[addObjective]
(
            @Name nvarchar(250)
           ,@ObjectiveGroupId int
           ,@CreatorId int
           ,@LanguageId int
           ,@isFinal bit
)
as 
insert into Objective values
(
            @Name
           ,@ObjectiveGroupId
           ,@CreatorId
           ,GETDATE()
           ,@LanguageId
           ,@isFinal
)

    select scope_identity() as Id;

But how to read the returned id using vb.net code

using this commands return -1

Answer 1

I assume you're using the return value from a call to .ExecuteNonQuery() in your VB.NET code (which you're not showing us...... so I can only guess).

That's the wrong value to read - that value would return the number of rows that were affected by your last SQL statement (e.g. by an INSERT, UPDATE or DELETE).

Since your stored procedure IS returning a value - the newly inserted ID - and it's returning a single row, single column value (just the ID, nothing else), you need to read that value - e.g. by calling .ExecuteScalar() instead:

Dim newID As Integer = CInt(yourInsertCmd.ExecuteScalar())