DaniWeb Forum Index > Software Development > C#

C# RSS

LastRecord in DB

Please support our C# advertiser: Intel Parallel Studio Home

•

Join Date: Jul 2009

Posts: 50

Reputation: darab is an unknown quantity at this point

Solved Threads: 0

darab

Offline

Junior Poster in Training

LastRecord in DB

Aug 3rd, 2009

Hi
How I Can Get To Last Records in DataBase tthat User Enter?

•

Join Date: Jul 2009

Posts: 50

Reputation: darab is an unknown quantity at this point

Solved Threads: 0

darab

Offline

Junior Poster in Training

Re: LastRecord in DB

Aug 3rd, 2009

my DB is SQL2005
How I can get to my last records? I need it's ID

•

Join Date: Feb 2009

Posts: 3,187

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

Re: LastRecord in DB

Aug 3rd, 2009

If the column is an identity the value can be retrieved by calling SCOPE_IDENTITY()

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
DECLARE @ID INT
 
INSERT INTO aTable (aColumn) VALUES (aValue)
SET @ID = Cast(SCOPE_IDENTITY() as INT)
Declare @ID int

Insert Into aTable (aColumn) Values (aValue)
Set @ID = Cast(SCOPE_IDENTITY() as int)

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2007

Posts: 276

Reputation: rapture has a spectacular aura about

Solved Threads: 37

rapture

Offline

Posting Whiz in Training

Re: LastRecord in DB

-1

Aug 3rd, 2009

To get the last record in a SQL database you simply type

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
SELECT TOP 1 * FROM tableName ORDER BY columnWithYourID DESC
 
or if you just need the id then
 
SELECT TOP 1 (columnWithYourID ) FROM tableName ORDER BY columnWithYourID DESC
SELECT TOP 1 * FROM tableName ORDER BY columnWithYourID DESC

or if you just need the id then

SELECT TOP 1 (columnWithYourID ) FROM tableName ORDER BY columnWithYourID DESC

This will return the last record in the column you want from the table you are looking for. How to translate that into C# without using SQL I do not know.

Last edited by rapture; Aug 3rd, 2009 at 9:41 am.

•

Join Date: Feb 2009

Posts: 3,187

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

Re: LastRecord in DB

Aug 3rd, 2009

•

Originally Posted by rapture

SELECT TOP 1 (columnWithYourID ) FROM tableName ORDER BY columnWithYourID DESC

That is bad advice. It creates a race condition with concurrency in SQL Server where it can return the wrong ID. Use @@IDENTITY or SCOPE_IDENTITY() depending on what ID you need. Consult SQL Books Online for the difference in the two values. Based on the original post SCOPE_IDENTITY() would best suit your needs.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2007

Posts: 276

Reputation: rapture has a spectacular aura about

Solved Threads: 37

rapture

Offline

Posting Whiz in Training

Re: LastRecord in DB

Aug 3rd, 2009

If you want to get technical, you're advice is bad as well, as scope_identity will:

"SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function."

Thus it may not return the proper id that the user entered right?

my solution was just a quick fix for a small database and someone new, it will work in most instances but the proper method would technically be

SELECT IDENT_CURRENT(’tablename’)

as this will return the last identity in the table regardless of the other variables mentioned. I suppose I should have answered this way the first time to prevent disagreement and confusion of the original poster or those who search the subject. Thanks for the advice.

Also of note, there is a possible bug when run in parallel with scope_identity as well that get more complicated
http://blog.sqlauthority.com/2009/03...-and-solution/

Darab,

You can use either method mentioned. If you're just working on a small project then scope_identity may work fine. Otherwise you can use SELECT IDENT_CURRENT(’tablename’) as stated. This should answer your question. Go ahead and ask anything else you need on this subject or mark the thread as solved.

•

Join Date: Feb 2009

Posts: 3,187

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

Re: LastRecord in DB

Aug 3rd, 2009

•

If you want to get technical, you're advice is bad as well, as scope_identity will:

"SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function."

No, it is not wrong in this case. Based on the original post it seems apparent that the OP wants the identity of the inserted record and not an identity from a trigger that may be defined on the table that was never mentioned. If you insert a record to a user table and a trigger inserts a record in an "audit" table for logging then SCOPE_IDENTITY() will return the user id, and @@IDENTITY will return the audit log id (if the tables are both set with identities and the user table has a trigger).

>> Thus it may not return the proper id that the user entered right?
Yes, it will. They did not enter an ID in this case as I didn't not see the OP indicating about enabling IDENTITY INSERT on the table.

>>my solution was just a quick fix for a small database and someone new, it will work in most instances but the proper method would technically be: SELECT IDENT_CURRENT(’tablename’)
That is the exact same problem as Select Max(id) from Table and is still a race condition. This is far more likely to be an issue than the parallelism condition of SCOPE_IDENTITY()

>> Also of note, there is a possible bug when run in parallel with scope_identity as well that get more complicated
I was not aware of this bug. However this bug occurs sometimes where parallel execution plans are generated by the SQL Server which does not happen with Insert Into Table () Values () , and if it does I have failed to see anything from Microsoft that indicates this. That being said I doubt that bug affects this situation.

I do appreciate you posting that bug though. I myself have queries which are affected by that condition so I'll be sending out a company wide email

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2007

Posts: 276

Reputation: rapture has a spectacular aura about

Solved Threads: 37

rapture

Offline

Posting Whiz in Training

Re: LastRecord in DB

Aug 3rd, 2009

Intersting, thank you - and what was your take on using

SELECT IDENT_CURRENT(’tablename’)

•

Join Date: Feb 2009

Posts: 3,187

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

Re: LastRecord in DB

Aug 3rd, 2009

•

Originally Posted by rapture

Intersting, thank you - and what was your take on using

SELECT IDENT_CURRENT(’tablename’)

DO NOT use it. Use SCOPE_IDENTITY() or @@IDENTITY . Consult SQL books online for the differences.

Read the Microsoft Connect report that was posted here to be aware of a potential bug when using the identity features.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2007

Posts: 276

Reputation: rapture has a spectacular aura about

Solved Threads: 37

rapture

Offline

Posting Whiz in Training

Re: LastRecord in DB

#10

Aug 3rd, 2009

Can you find an example? on MSDN here http://msdn.microsoft.com/en-us/libr...7(SQL.80).aspx

it looks like it is similar to @@IDENTITY AND SCOPE_IDENTITY but just returning the last id value from any session or scope.

I see a problem if the table is null
http://support.microsoft.com/kb/835188

but if the table is created why wouldn't this be the best way to go?

--and thanks for the advice