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.
Original Author: Sergei Belov
Code
<%@ Language=VBScript %>
<%
' 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 IGNORE 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
'
%>
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.