Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Introduction:

In this article I will explain how to write query to get last inserted record id in sql server or last modified record id value in SQL Server.

Description:

In previous post I explained how to set identity property or auto increment column in data table and many articles relating to SQL Server. Now I will explain how to write a query to get last inserted record id value or last modifies record id in SQL Server. Generally if we create data table we will set identity column or auto increment column in table to increase column id value automatically whenever new record inserted in SQL Server. In one situation I got requirement like to get last inserted record Id from data table in SQL Server.

Before write query first design one table and give name as EmployeeDetails in your database as shown below

EmployeeDetails Table

Column Name
Data Type
Allow Nulls
EmpId
Int (set Identity=true)
No
EmpName
varchar(50)
Yes
Role
Varchar(50)
Yes
To insert new record in above table we need to write query like this


INSERT INTO EmployeeDetails(EmpName,Role) VALUES('SureshDasari','Team Lead')
Now if we want to get last inserted record we have multiple options check below queries

The table which is having Identity Property from that table we can get the last inserted record id will be like this


SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT('TableName')
Another way we can get it in


SELECT MAX(EmpId) FROM EmployeeDetails
Other way

SELECT TOP 1 EmpId FROM EmployeeDetails ORDER BY EmpId DESC

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

4 comments :

Munavvar Patel said...

I am fan of ur posts...
tell me what if Identity=false?

Bijay Khatri said...

if identity is false you need to increment id by yourself so as not to conflict with previous id and use SELECT TOP 1 EmpId FROM EmployeeDetails ORDER BY EmpId DESC

venkat said...

how to write both queries in single line i mean how to get identity id after inserting a row in single query...l

Saranya Raman said...

Dim a As String
a = "select TOP 2 RecieptNo, Date,TutionFeeRs,TutionFeePs from tutuionreciept where AppNo =" & TextBox1.Text & " "
cmd = New SqlCommand(a, con)
cmd.Connection = con
dr = cmd.ExecuteReader
dr.Read()
If (dr.HasRows = True) Then
TextBox14.Text = dr.GetValue(0)
TextBox16.Text = dr.GetValue(1)
TextBox15.Text = dr.GetValue(2) + dr.GetValue(3)
End If
dr.NextResult()
dr.Read()
If (dr.HasRows = True) Then
While (dr.NextResult = True)
TextBox19.Text = dr.GetValue(0)
TextBox18.Text = dr.GetValue(1)
TextBox17.Text = dr.GetValue(2) + dr.GetValue(3)
dr.NextResult()
End While
Else
MsgBox("No record")
End If
Its showing only one record.............
what can i do to get second record

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.