CODEWITHSUNDEEP
sql-servervb.netstored-procedures
Adham
Adham

Reputation: 64844

Getting ID after insert in SQL Server

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

Upvotes: 3

Views: 17079

Answers (2)

Nathanielle
Nathanielle

Reputation: 1

select * from (tablename) order by (id) DESC
for x = 0 to (tablename).rows.count - 1
savedid = (tablename).rows(x).item("id").tostring
next

Upvotes: 0

marc_s
marc_s

Reputation: 754468

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())

Upvotes: 3

Related Questions