Get the identity value inserted by the current SQL Server scope
When an INSERT creates an IDENTITY value, the application often needs that new ID immediately. The historical version of this page used IDENT_CURRENT() and described it as the ID “just inserted” by the current request. That is not safe under concurrent use.
IDENT_CURRENT('table') returns the last identity generated for that table in any session and any scope. For the identity generated by the current INSERT in the current scope, use SCOPE_IDENTITY().Insert a row and return its identity
<%
Dim cmd, rs, newId
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO dbo.auto (name, city) VALUES (?, ?); SELECT CAST(SCOPE_IDENTITY() AS int) AS new_id;"
cmd.Parameters.Append cmd.CreateParameter("pName", adVarWChar, adParamInput, 50, "Smo")
cmd.Parameters.Append cmd.CreateParameter("pCity", adVarWChar, adParamInput, 50, "Vizag")
Set rs = cmd.Execute
newId = rs("new_id")
Response.Write "New record ID: " & Server.HTMLEncode(CStr(newId))
rs.Close
Set rs = Nothing
Set cmd = Nothing
%>This example assumes ADO constants such as adCmdText, adVarWChar and adParamInput are available, commonly through adovbs.inc. SET NOCOUNT ON suppresses the INSERT row-count message so the returned SELECT result is straightforward to consume through ADO.
If triggers insert rows into other identity tables, @@IDENTITY can also return a different value than the row created by your statement. SCOPE_IDENTITY() stays within the current session and scope.
Where IDENT_CURRENT() is useful
IDENT_CURRENT('dbo.auto') can be useful when you explicitly want the table's most recently generated identity regardless of which session created it. It should not be used to identify the row created by a particular visitor request.
ASP Home
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
