获取新的 SQL 记录 ID [英] get new SQL record ID

问题描述

如何取回刚刚插入的新记录的自动生成 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.

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