Solved

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

Posted on 2012-03-20
9
164 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:Kdo
Kdo 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Total in Access 4 47
SQL Server syntax question 13 31
get column names from table in vb.net 8 28
sql server insert 12 30
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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. …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 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