<%
Dim conn, rs, newID
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("/db/mydb.accdb")
Set rs = Server.CreateObject("ADODB.Recordset")
' 關鍵:CursorType=adOpenKeyset(1), LockType=adLockOptimistic(3)
rs.Open "SELECT * FROM Orders WHERE 1=0", conn, 1, 3
rs.AddNew
rs("CustomerName") = "王小明"
rs("OrderDate") = Now()
rs("Amount") = 1234
rs.Update
' 更新後立即可讀取自動編號欄位 (假設欄位名為 OrderID)
newID = rs("OrderID")
Response.Write "新訂單 ID: " & newID
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
%>
要點:rs.Open sql, conn, 1, 3 (adOpenKeyset, adLockOptimistic) 才能在 Update 後直接取得 Identity 值。
<%
Dim conn, cmd, rs, newID
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=ERP;UID=sa;PWD=xxx"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO Orders (CustomerName, OrderDate, Amount) VALUES (?, ?, ?); SELECT SCOPE_IDENTITY() AS NewID;"
cmd.CommandType = 1 ' adCmdText
' 參數化防注入
cmd.Parameters.Append cmd.CreateParameter("@CustomerName", 200, 1, 100, "王小明") ' adVarChar
cmd.Parameters.Append cmd.CreateParameter("@OrderDate", 135, 1, , Now()) ' adDBTimeStamp
cmd.Parameters.Append cmd.CreateParameter("@Amount", 5, 1, , 1234) ' adDouble
Set rs = cmd.Execute
If Not rs.EOF Then newID = rs("NewID")
Response.Write "新訂單 ID: " & newID
rs.Close: Set rs = Nothing
Set cmd = Nothing
conn.Close: Set conn = Nothing
%>
| 函數 | 範圍 | 適用場景 |
|---|---|---|
| SCOPE_IDENTITY() | 當前 Session、當前 Scope | 最推薦,不受 Trigger、巢狀 Scope 影響 |
| @@IDENTITY | 當前 Session、所有 Scope | 若資料表有 INSERT Trigger 會回傳 Trigger 產生的 Identity,易導致錯誤 |
| IDENT_CURRENT('table') | 所有 Session、指定資料表 | 跨 Session 查詢最後 Identity,不適合剛插入即取 |
conn.Execute "INSERT INTO Orders (CustomerName, OrderDate, Amount) VALUES ('王小明', Now(), 1234)"
Set rs = conn.Execute("SELECT @@IDENTITY AS NewID")
newID = rs("NewID")
注意:Access 不支援 SCOPE_IDENTITY(),@@IDENTITY 在無 Trigger 時可用。
SELECT MAX(ID) 或 SELECT TOP 1 ID ORDER BY ID DESC,高併發下極易取錯。Function InsertAndGetId(table, fields, values),全站復用。