Hi there

I'm inserting a record into a sql db and then immediately needing that record back - in the table I have an id field which is auto incrementing and I also have a date field (datetime).

What's the best way to do this?

I'm using "select top 1 * FROM mytable" but I actually want the latest dated one.

So I would need something like:

"select * from mytable WHERE dateadded = LatestDate" though am not sure of the syntax for 'LatestDate' if you see what I mean.

thanks

Adam

Hi there,

What you *could* do is order by date descending and get the first record:

SELECT TOP 1 * FROM mytable ORDER BY dateadded DESC

However, you probably don't want this. If two users insert a record at the same time, you run the risk of getting the wrong record back.

Take a look at the following article to see some ways to get back the record's iderntity:

http://www.adopenstatic.com/experime...autonumber.asp

Cheers,

Imar

__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

In other words, just use SELECT @@IDENTITY

But one caution: Be sure you use the *SAME* connection (and do NOT close and reopen it!) for the INSERT and then the SELECT @@IDENTITY

Thanks guys - I got there with your help.