?
Solved

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

Posted on 2012-03-20
9
Medium Priority
?
172 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 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 200 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 46

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 1800 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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