Search Tools Links Login

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 %>


Document Title


<%
' 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
'
%>


About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 101 times

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.