获取新的 SQL 记录 ID

2022-01-16 00:00:00 sql sql-server-2005 asp-classic sql-server

如何取回刚刚插入的新记录的自动生成 ID?(使用 ASP 经典和 MSSQL 2005)

How can I get back the autogenerated ID for a new record I just inserted? (Using ASP classic and MSSQL 2005)

推荐答案

感谢所有建议 SELECT SCOPE_IDENTITY() 的人.我能够创建一个存储过程:

Thanks all who suggested SELECT SCOPE_IDENTITY(). I was able to create a stored procedure:

USE [dbname]
GO 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsert]
(
   @Nn varchar(30)
)
AS
BEGIN TRANSACTION InsertRecord 
   INSERT INTO A (Nn) 
   VALUES (@Nn) 
   SELECT NewID = SCOPE_IDENTITY()   -- returns the new record ID of this transaction
   COMMIT TRANSACTION InsertRecord

并使用VB调用存储过程:

and call the sproc using VB: 

Dim strNn '<- var to be passed'
Set cn = Server.CreateObject("ADODB.Connection") 
connectString = "DSN" 
cn.Open connectString, "user", "PW0rd" 
Set rs = Server.CreateObject("ADODB.Recordset") 
set rs = cn.Execute("EXEC [dbname].[dbo].[A] @Nn=" & strNn)
'return the value'
resultID = rs(0)

我现在可以在任何时候引用新创建的 ID 时使用 resultID.

I can now use resultID anytime I refer to the newly created ID.

相关文章