943,625 Members | Top Members by Rank

Software Development > C# > LastRecord in DB
Ad:
  • C# Discussion Thread
  • Unsolved
  • Views: 628
  • C# RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
Aug 3rd, 2009
0

LastRecord in DB

Expand Post »
Hi
How I Can Get To Last Records in DataBase tthat User Enter?
Similar Threads
Reputation Points: 6
Solved Threads: 0
Junior Poster in Training
darab is offline Offline
50 posts
since Jul 2009
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

my DB is SQL2005
How I can get to my last records? I need it's ID
Reputation Points: 6
Solved Threads: 0
Junior Poster in Training
darab is offline Offline
50 posts
since Jul 2009
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

If the column is an identity the value can be retrieved by calling SCOPE_IDENTITY()
sql Syntax (Toggle Plain Text)
  1. DECLARE @ID INT
  2.  
  3. INSERT INTO aTable (aColumn) VALUES (aValue)
  4. SET @ID = Cast(SCOPE_IDENTITY() as INT)
Featured Poster
Reputation Points: 1749
Solved Threads: 735
Senior Poster
sknake is offline Offline
3,948 posts
since Feb 2009
Permalink
Aug 3rd, 2009
-1

Re: LastRecord in DB

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

C# Syntax (Toggle Plain Text)
  1. SELECT TOP 1 * FROM tableName ORDER BY columnWithYourID DESC
  2.  
  3. or if you just need the id then
  4.  
  5.  
  6. 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.
Reputation Points: 155
Solved Threads: 41
Posting Whiz in Training
rapture is offline Offline
294 posts
since Jul 2007
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

Click to Expand / Collapse  Quote 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.
Featured Poster
Reputation Points: 1749
Solved Threads: 735
Senior Poster
sknake is offline Offline
3,948 posts
since Feb 2009
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

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.
Reputation Points: 155
Solved Threads: 41
Posting Whiz in Training
rapture is offline Offline
294 posts
since Jul 2007
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

Quote ...
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
Featured Poster
Reputation Points: 1749
Solved Threads: 735
Senior Poster
sknake is offline Offline
3,948 posts
since Feb 2009
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

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

SELECT IDENT_CURRENT(’tablename’)
Reputation Points: 155
Solved Threads: 41
Posting Whiz in Training
rapture is offline Offline
294 posts
since Jul 2007
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

Click to Expand / Collapse  Quote 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.
Featured Poster
Reputation Points: 1749
Solved Threads: 735
Senior Poster
sknake is offline Offline
3,948 posts
since Feb 2009
Permalink
Aug 3rd, 2009
0

Re: LastRecord in DB

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
Last edited by rapture; Aug 3rd, 2009 at 1:46 pm.
Reputation Points: 155
Solved Threads: 41
Posting Whiz in Training
rapture is offline Offline
294 posts
since Jul 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in C# Forum Timeline: How do I round off
Next Thread in C# Forum Timeline: DLL callback





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC