?
Solved

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

Posted on 2012-03-20
9
Medium Priority
?
169 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
Independent Software Vendors: 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!

 

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

Independent Software Vendors: 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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

771 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