Get the ID of just inserted database record using INSERT clause.
This article demonstrates how to get the ID of just inserted database record using INSERT clause.
AI
AI Summary: This codebase represents a historical implementation of the logic described in the metadata. Our preservation engine analyzes the structure to provide context for modern developers.
Source Code
<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE>Document Title</TITLE>
</HEAD>
<BODY>
<%
' Please note that this example won't work with MS Access database since @@IDENTITY is only suported by SQL Server.
'
Dim loCN ' Connection Object
Dim loRS ' Recordset Object
Dim lsSQL ' SQL String
Dim lnNewEmployeeID ' New ID
'
'# Create a database connection
Set loCN = Server.CreateObject("ADODB.Connection")
Call loCN.Open("DSN=Northwind", "sa", "password")
'
'# Build INSERT statement
lsSQL = "INSERT INTO Employees " & _
"(FirstName, LastName, Title, HomePhone) " & _
"VALUES ('John','Doe','Some Title','123-456-7890')"
'
'# Append SELECT statement with identity which we will use later to retrieve the idetity of the new record
lsSQL = lsSQL & " SELECT * FROM Employees WHERE " & _
"EmployeeID = @@IDENTITY"
'
'# Execute SQL
Set loRS = loCN.Execute(lsSQL).NextRecordset
'
'# Here is an alternative to the line
' above
' Set loRS = Server.CreateObject("ADODB.Recordset")
' Call loRS.Open(lsSQL, loCN)
' Set loRS = loRS.NextRecordset
'
'# Get the ID of the record we
' just inserted
lnNewEmployeeID = loRS("EmployeeID").Value
'
Response.Write lnNewEmployeeID
'
' Clean Up
Set loRS = Nothing
Set loCN = Nothing
'
%>
</BODY>
</HTML>
Original Comments (3)
Recovered from Wayback Machine