如何獲得剛插入數(shù)據(jù)庫的記錄的ID號?

1.SQL Server
對于SQL Server 2000來說，它提供了兩個全新的函數(shù)(IDENT_CURRENT,SCOPE_IDENTITY),并且改進了@@IDENTITY的不足.當(dāng)你插入新記錄后，可以調(diào)用函數(shù):
PRINT IDENT_CURRENT('table') '這將獲得新的IDENTITY值，不管數(shù)據(jù)庫中是不是有記錄添加(這就避免了@@IDENTITY的連接限制)
或者：PRINT SCOPE_IDENTITY() '這將獲得在當(dāng)前存儲過程，觸發(fā)器等其他程序創(chuàng)建的最新記錄的IDENTITY值.
而全局變量@@IDENTITY有一個問題，當(dāng)對一張表執(zhí)行insert時，如果該表有觸發(fā)器程序在執(zhí)行插入操作，然后，接著在另一張表中插入記錄，這樣返回@@IDENTITY值就是第二張表的IDENTITY值。
如果你用的不是SQL Server 2000，你最好一個簡單的存儲過程來解決這個問題。
CREATE PROCEDURE myProc
@param1 INT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO someTable
(
intField
)
VALUES
(
@param1
)
SET NOCOUNT OFF
SELECT NEWID = @@IDENTITY
END
在ASP中你可以這樣做:
<%
fakeValue = 5
set conn = Server.CreateObject("ADODB.Connection")
conn.open "<conn string>"
set rs = conn.execute("exec myProc @param1=" & fakeValue)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>

2.Access
對于Access,你可以用下面這樣的方法：
<%
fakeValue = 5
set conn = Server.CreateObject("ADODB.Connection")
conn.open "<conn string>"
conn.execute "Insert into someTable(intField) values(" & fakeValue & ")"
set rs = conn.execute("select MAX(ID) from someTable")
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%>
然而對于多人同時向數(shù)據(jù)庫中添加數(shù)據(jù)，我們就要利用記錄集的adOpenKeyset游標(biāo)來防止出錯。例如下面的例子:
<%
fakeValue = 5
set conn = Server.CreateObject("ADODB.Connection")
conn.open "<conn string>"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open "select [intField] from someTable where 1=0", conn, 1, 3
rs.AddNew
rs("intField") = fakeValue
rs.update
response.write "New ID was " & rs("id")
rs.close: set rs = nothing
conn.close: set conn = nothing
%>