Get the key of recently inserted record!

I use SQL Server for store database.
And use Trigger to generate the primary key for new records.

I have problem when I write a programme on Winform:
   When I insert a record into database, I wanna know what the key SQL gennerate for it is so that I can use it for the future.

Show is any good way to do that?
tunhienAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AGBrownCommented:
The SQL function SCOPE_IDENTITY will return the identity used for the last record inserted. You can use a stored procedure to do your insert, and return the value of SCOPE_IDENTITY as an OUTPUT parameter from the stored procedure.

If you need help on specifics, just say.

Andy
0
tunhienAuthor Commented:
If I use store procedure then no need to use trigger! and SCOPE_IDENTITY because I can return the key I generated. :d

Show is there any way to do that with C# code not SQL Sever?
Thanks
0
AGBrownCommented:
I don't think I mentioned triggers. Also, if you use a stored procedure, the most efficient way to get the identity you have just inserted _is_ to use SCOPE_IDENTITY. Otherwise, how would you do it? You could do a SELECT Id From... using your inserted values, but that is very messy.

So the only way to do it with C#, if you don't want to do it with a stored procedure and SCOPE_IDENTITY is to execute a second statement after your insert which does just that ... selects the id based on a WHERE clause that finds your inserted values.

So how would you do it with a stored procedure? I'm intrigued.

Andy
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

tunhienAuthor Commented:
It's sound good way!

But please show me more about the store procedure! How can U generate code automatically?

If I've got a table like this:

create table test
(
         code       int,     -- Need to auto increase
         primary key (code)
)


Please write the SP to do that when Insert and return is the key has just been inserted!
Thanks

0
AGBrownCommented:
Heres a stored procedure that will do what you want, and the create table script to go with it...

CREATE TABLE [dbo].[test] (
      [code] [int] IDENTITY (1, 1) NOT NULL ,
      [primaryKey] [int] NOT NULL ,
GO

CREATE PROC DoMyInsert
(
    @PrimaryKey varchar(50),
    @AnotherColumn varchar(50),
    @intIdCreated int OUTPUT
)
AS
INSERT INTO dbo.test
    (primaryKey, ...)
VALUES
    (@PrimaryKey, @AnotherColumn)

SELECT @intIdCreated = SCOPE_IDENTITY()
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AGBrownCommented:
PS this way you can get rid of the trigger as well? Or do you need that for another reason?
0
tunhienAuthor Commented:
uupppp!

Thanks for your suggestion! I now I knew the IDENTITY in SQL server. :"> :d
I will try with the new way instead of trigger. :d
0
AGBrownCommented:
good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.