Retrieve ID after the record has been created

Question

0.00/5 (No votes)

See more:

Good day,

I am very new to development, so would appreciate any help.

I am curently trying to work out how to retrieve an ID from My database after I have added a record using a webform compiled of c# in ASP.net.

scenario : Create an order, and upon creation the page will post back the Order Number.

Kind regards,

JD

Posted 24-May-11 0:10am

Member 7949758

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2011-05-24T00:16:00

Yes possible, using SCOPE_IDENTITY()
In ADO.NET, one can send multiple queries in one go or call a stored procedure.

For quick start try:

C#

myCommandForData.CommandText = "INSERT INTO [Country] (CName) VALUES (@CNameValue); SELECT SCOPE_IDENTITY()";
int newCountryID = (int)myCommandForData.ExecuteScalar();

Handling it in an SP and calling an SP would be a better way to use it.

I would suggest you to read on ADO.NET here:
Look at these:
MSDN: ADO.NET[^]
MSDN: Accessing Data with ADO.NET[^]

SP specific, go here:
MSDN: HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET[^]
MSDN: HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]
ADO.NET : Using Stored Procedures[^]

Christoph Keller · Accepted Answer · 2011-05-24T00:17:00

Solution 2

Hi,

This depends upon how you access the datalayer / your database:

If you use dbml / Linq to SQL, the best way would be that you use a Guid as ID and generate the Guid in your code.
If you use direct SQL statements, you can get the last inserted identity by executing the TSQL command scope_identity() (see: http://msdn.microsoft.com/de-de/library/ms190315.aspx[^])
If you use stored procedures, you can add a return value to the SP which returns you the scope_identity() (see: http://msdn.microsoft.com/de-de/library/ms190315.aspx[^])

I hope this gives you some starting points to solve your problem.

Best regards and happy coding,
Stops

Posted 24-May-11 0:17am

Christoph Keller

Updated 24-May-11 0:18am

v2

Comments

Member 7949758 24-May-11 7:04am

Thanks for the quick replies.

All solutions are of use but im not really sure what is the best choice.

I use OpenAccess to acces my data layer, And use C# to write to database exc.

I have tried going down the route of creating a stored procedure. And then using Scope_Identity() to call it.

Any advise or example code you could Suggest ?

I would try to better explain myself but i come from a background in Database design not implementation...

I have just finsished University in a degree which was not related to Programming so I am a complete Newbie to it.

And im trying my best to teach myself using c#

Christoph Keller 24-May-11 8:29am

Hi,

Please look at the links in the Solution 1 from S Mevara. There you will find the stored procedure (SP) related links. In your case, I would prefer a SP which have one output declared parameter (see Using a Stored Procedure with Output Parameters). And inside the SP you write something like: SET @outputParameterName = SCOPE_IDENTITY()

Hope this helps.

Best regards and good luck,
Stops

**R. Giskard Reventlov** · Accepted Answer · 2011-05-24T00:19:00

Solution 3

This[^] will get you started.

Posted 24-May-11 0:19am

R. Giskard Reventlov