Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

618 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