How to get the value of your @@IDENTITY from SQL Server

How to get the value of your @@IDENTITY from SQL Server

To get the value of your @@IDENTITY from SQL Server

Let’s say that you have set one of your columns to identity increment to a value and you want to retrieve the value of your last insert data.

Here’s the code to do it.

Dim Conn, lsSQL, loRs
Set Conn = CreateObject("ADODB.Connection")

' Open a connection to the database
Conn.Open("DSN=mydb;UID=user;PWD=password;")

' Insert a new record into the table
lsSQL = "SET NOCOUNT ON;" &_
"INSERT INTO tMembers (MemberName) VALUES ('Manohar');" &_
"SELECT @@IDENTITY AS NewID;"

' Execute the SQL statement
Set loRs = Conn.Execute(lsSQL)

' Get the inserted ID
llID = loRs.Fields("NewID").value

' Close the connection
Conn.Close()
Set Conn = Nothing

SET NOCOUNT function disable the output messages like ‘1 rows affected" from MSSQL Server.