Retrieving the last identity value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Retrieving the last identity value

Author

Topic

akol
Starting Member

1 Post

Posted - 2005-11-16 : 22:18:39

Hi all,

I'm having some problems retrieving the last identity value inserted into an IDENTITY column. The calling application is an ASP.NET Web Application, and the DB is SQL Server 2000.

The (simplified) table structure I'm inserting into is:

CREATE TABLE [dbo].[SomeTable] (
[NameID] [int] IDENTITY (1, 1) NOT NULL,
[Name] [varchar] (255) NOT NULL
) ON [PRIMARY]
GO

And the stored procedure I'm executing:

CREATE PROCEDURE [dbo].[SP_InsertName]
(
@Identity int output,
@Name varchar(255)
)
AS
INSERT INTO [dbo].[SomeTable] (Name) VALUES(@Name)
SET @Identity = SCOPE_IDENTITY()
GO

The following code is executing the stored procedure and retrieving the last identity value (for later use).

using(SqlConnection con = new SqlConnection(conStr))
{
SqlCommand cmdInsert = new SqlCommand("SP_InsertName", con);
cmdInsert.CommandType = CommandType.StoredProcedure;

SqlParameter prmName = new SqlParameter("@Name", SqlDbType.VarChar, 255);
prmName.Value = "SomeName";
cmdInsert.Parameters.Add(prmName);

SqlParameter prmNameID = new SqlParameter("@Identity",SqlDbType.Int);
prmNameID.Direction = ParameterDirection.Output;
cmdInsert.Parameters.Add(prmNameID);

conn.Open();
int groupID = (int)cmdInsert.ExecuteScalar();
}

When executing the query, the value returned is always null (even though the insert is successful).

I'm not quite sure where to go from here. Any help would be greatly appreciated.

Edit: apologies for the formatting, the code tags appear to break things in Firefox.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-16 : 22:57:11

You need to check the value of the output parameter, not the return value of ExecuteScalar. You should be calling ExecuteNonQuery. ExecuteScalar simply returns the value in the first column of the first row returned by a command. If you want to use that method, you don't need output parameters and you can just end your stored proc with SELECT SCOPE_IDENTITY().

If you do wish to use an output parameter, then you just check the value of the output parameter (which you have already declared):
i.e.,

cmdInsert.ExecuteNonQuery();
int groupID = (int) prmNameID.value;

Great job giving info on the question, by the way .... very easy to help you troubleshoot your situation since you provided all of the details nicely. Usually that's the hardest part !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources