Getting the Unique identity number from inserted record of MSSQL table

SQL PHP HTML ASP JavaScript articles and free scripts to download

Collecting the auto generated column value of just inserted record

If we are using one identity column to generate auto increment numbers, then we can get the recently added number of the record just inserted by using IDENT_CURRENT function. This function will collect the auto generated number of MSSQL and display it to the visitors.

In the part I we have already discussed how to create the identity field and how MSSQL automatically insert the next incremental number to each record.

We will see the complete code of record insertion and getting the record number of the table generated by IDENT_CURRENT function.

Here is the complete code.

Dim conn,rs,SQL,RecsAffected,dt

Set conn=Server.CreateObject("ADODB.Connection")
conn.Mode=adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs =Server.CreateObject("ADODB.Recordset")
rs.open "insert into auto (name,city) values('smo1','vizag1')", conn
rs.open "select IDENT_CURRENT('auto')"
dt=rs(0).value
Response.Write dt
Set rs = Nothing
conn.Close
Set conn = Nothing

Each time the above code is executed one new number is inserted along with the record.

Read more on IDENT_CURRENT at MSDN

Read how to get this unque record id after inserting a record in MySQL

You can get the sql script to create the table in part I of this tutorial