在 SQL Server 中插入后获取 ID [英] Getting ID after insert in SQL Server

问题描述

我使用这个存储过程进行插入并返回插入行的id

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;

但是如何使用vb.net代码读取返回的id

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

使用这个命令返回-1

推荐答案

我假设您在 VB.NET 代码中使用了调用 .ExecuteNonQuery() 的返回值(您'没有向我们展示......所以我只能猜测).

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

这是读取错误的值 - 该值将返回受上次 SQL 语句影响的行数(例如，受 INSERT、UPDATE 或 >删除).

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

因为您的存储过程 IS 返回一个值 - 新插入的 ID - 并且它返回单行单列值(只是 ID代码>，没有别的)，您需要读取该值 - 例如通过调用 .ExecuteScalar() 代替:

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

这篇关于在 SQL Server 中插入后获取 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！