获得新的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)

我现在可以使用resultID任何时候我指的是新创建的ID。

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

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