How to retrive @@Identity value in aspx page

Question

0.00/5 (No votes)

See more:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Registration] (
@CustomerId int,
@EmailId varchar(50),
@FirstName varchar(50),
@Lastname varchar(50),
@Phone1 varchar(15),
@Phone2 varchar(15),
@CompanyName varchar(50),
@Address varchar(max),
@ZipCode varchar(20),
@City varchar(50),
@State varchar(50),
@Country varchar(50),
@TransactionDate datetime,
@Status varchar(30)
)
AS
BEGIN
insert into dbo.CustomerDetails values(@EmailId,@FirstName,@Lastname,@Phone1,@Phone2,@CompanyName
,@Address,@ZipCode,@City,@State,@Country,@TransactionDate,@Status)
Declare @NewId as int
select @NewId= @@IDENTITY
END
In above table customerId is a primary key and auto generated. After inserting this information i need last record i mean recently added record Customer Id Please need help

Posted 21-Aug-12 21:54pm

Lakshmimsridhar

Updated 21-Aug-12 21:59pm

v2

Add a Solution

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

Santhosh Kumar Jayaraman · Answer 1 · 2012-08-21T22:27:00

Just make the following changes in stored proc.

Add select @@identity

Instead of this,

SQL

Declare @NewId as int
select @NewId= @@IDENTITY
END

now ur code will look like

SQL

Select @@Identity
END

Now in aspx page when executing command.

C#

SqlCommand cmd = new SqlCommand();
CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
int ID=(Int32)cmd.ExecuteScalar();
sqlConnection1.Close();

Sunil Kumar Pandab · Answer 2 · 2012-08-21T22:04:00

Solution 1

If your Id is in auto-increment then retrieve MAX(ID) after inserting the data.

Posted 21-Aug-12 22:04pm

Sunil Kumar Pandab

Aarti Meswania · Answer 3 · 2012-08-21T22:05:00

Solution 2

use return keyword,

SQL

Declare @NewId as int
select @NewId= @@IDENTITY
return @NewId

Happy Coding!
:)

Posted 21-Aug-12 22:05pm

Aarti Meswania

Comments

Lakshmimsridhar 22-Aug-12 4:13am

how in aspx.cs page??

__TR__ · Answer 4 · 2012-08-21T22:08:00

Solution 3

Hi,
Change the last line in your stored procedure to

SELECT @@Identity

and use ExecuteScalar() method in your code behind to get the ID.

Below link should give you some idea
SqlCommand.ExecuteScalar Method[^]

Posted 21-Aug-12 22:08pm

__TR__

Comments

Lakshmimsridhar 22-Aug-12 6:28am

public int InsertRegistrationDetails()
{
SqlCommand cmd = new SqlCommand("sp_Registration",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CustomerId", CustomerId);
cmd.Parameters.Add("@EmailId",EmailId);
cmd.Parameters.Add("@FirstName",FirstName);
cmd.Parameters.Add("@Lastname",Lastname);
cmd.Parameters.Add("@Phone1",Phone1);
cmd.Parameters.Add("@Phone2",Phone2);
cmd.Parameters.Add("@CompanyName",CompanyName);
cmd.Parameters.Add("@Address",Address);
cmd.Parameters.Add("@ZipCode",ZipCode);
cmd.Parameters.Add("@City",City);
cmd.Parameters.Add("@State",State);
cmd.Parameters.Add("@Country",Country);
cmd.Parameters.Add("@TransactionDate",TransactionDate);
cmd.Parameters.Add("@Status",Status);
cmd.ExecuteNonQuery();
int NewId = 0;
NewId = Convert.ToInt32(cmd.ExecuteScalar().ToString());
return NewId;
}
this is a class. this function returns scalar value. how to take that value in code behind and assign this return value to customer id

__TR__ 22-Aug-12 8:18am

Since the method returns an integer, just call the method and assign it to an int variable.
something like:
int CustomerID = InsertRegistrationDetails();