Solved

if I insert a new record; is there a way to find it? for sure

Posted on 2012-03-20
9
167 Views
Last Modified: 2012-06-27
like on a form I have created a button to duplicate a record and then I want to display the newly added record.

I just cannot think of a way to find the last record entered.  If I do the query on the max ID then there always a possibility that some one else may have a created a record at the same time?

I am using MS SQL server on godaddy.com
0
Comment
Question by:goodk
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:TonyReba
ID: 37744175
What About TOP 1 ?
0
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 50 total points
ID: 37744184
Hi goodk,

Does the table have an identity column?  If so, the value of @@IDENTITY will contain the last identity value of the last row inserted from the current task.


Good Luck,
Kent
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 37744205
You can use Scope_Identity() to get the last record entered assuming you are using an identity column in your table.
Have a look here :
http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:goodk
ID: 37744271
CREATE TABLE [dbo].[AceData](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [Date] [datetime] NULL,
      [Ref] [nvarchar](50) NULL,
..

CONSTRAINT [AceData$PrimaryKey] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 37744308
Easy enough.  :)

INSERT INTO [AceData] (Date, Ref) VALUES (current_timestamp, 'Ace 1');

Set ACE_IDENTITY = @@IDENTITY;

Open in new window


The script is free to run other SQL and ACE_IDENITITY has the last ID that THIS process added.


Good Luck,
Kent
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 37744343
Your ID column is an identity column so you can use scope_identity as described in the article I linked it is considered better practice to use scope_identity() over @@Identity.
0
 

Author Comment

by:goodk
ID: 37744355
Scope_Identity()   How would this yield the correct result, if there are several people logged in and entering records?
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 450 total points
ID: 37744384
Scope_Identity returns the last identity created in the current scope I.e. Stored procedure, module, function.
So if you are using a stored procedure with an insert statement it will return the Id created by the insert statement in the same stored procedure instance i.e. The stored procedure that is being executed by a single user
0
 

Author Closing Comment

by:goodk
ID: 37745639
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question