How to capture the newly inserted row's ID value from C#?

Question

Here's my stored procedure

ALTER PROCEDURE dbo.spInsertNewTask
@ModuleID               int, 
@Task                   varchar(50), 
@StartDate              date, 
@PlannedEndDate         date, 
@EstimatedEndDate       date, 
@Status                 int,
@Comments               varchar(500),
@Started                bit
AS
INSERT INTO DTasks (ModuleID, Task, StartDate, PlannedEndDate, EstimatedEndDate, Status, Comments, Started, LastUpdated)
VALUES (@ModuleID, @Task, @StartDate, @PlannedEndDate, @EstimatedEndDate, @Status, @Comments, @Started, GETDATE())

RETURN SCOPE_IDENTITY()

When I try to capture the newly created ID, all I'm getting is always 1. How to capture the New row's ID?

newID = cmd.ExecuteNonQuery();

In fact I need this new ID for further processing.

Thanks for helping.

score 4 · Accepted Answer · edited May 23 '17 at 11:48

4

Instead of ExecuteNonQuery, which returns the number of effected rows, you should use ExecuteScalar, casting the returned object to the correct type:

newID = (int)cmd.ExecuteScalar();

For the above to work, you also need to change the SQL from returning a return value to select the value:

SELECT SCOPE_IDENTITY()

The code example on the page is pretty much what you are looking to do.

If you want to keep the return type, you will need to add a return type parameter to the command, execute as non-query as you currently do, then read the value of the return parameter, as described in Getting return value from stored procedure in C#.

edited May 23 '17 at 11:48

Community

1
1

answered Oct 22 '12 at 15:49

Oded

489,969
99
883
1,009

I'm getting an "Object reference not set to an instance of an object" exception. – Richard77 Oct 22 '12 at 15:56
Wouldn't that require the value to be returned by `SELECT SCOPE_IDENTITY()` (rather than using the `RETURN` keyword?) – marc_s Oct 22 '12 at 15:57
After I changed the key word from RETURN to SELECT, I am getting a different error exception: "Specified cast is not valid." – Richard77 Oct 22 '12 at 16:00
@Richard77 - What is the expected value? Does it for inside the range of an `int`? I'd expect that error if the returned value is not an integer. – Oded Oct 22 '12 at 16:02
@Obed, while researching other answers, I've found out that the return value is decimal. They suggest that the result be cast twice. Like this (int)(decimal). Now it's working. – Richard77 Oct 22 '12 at 16:06
1

@Richard77 - That's would do it... and it's Oded, not Obed. – Oded Oct 22 '12 at 16:09

score 1 · Answer 2 · answered Oct 23 '12 at 08:31

You need to pass [ID] assuming that it is you identity column as output parameter:

In C# you need to add new parameter which is output parameter and at the end of execution to retrieve :

Initialize:

    SqlParameter param = new SqlParameter("@ID", 0);
    param.Direction = ParameterDirection.Output;
    param.DbType = DbType.Int32;

Retrieve after execution:

    int id = Convert.ToInt32(sqlcommand.Parameters["@ID"].Value.ToString());

Stored procedure:

    ALTER PROCEDURE dbo.spInsertNewTask
        @ID                     INT = NULL OUTPUT,
        @ModuleID               int, 
        @Task                   varchar(50), 
        @StartDate              date, 
        @PlannedEndDate         date, 
        @EstimatedEndDate       date, 
        @Status                 int,
        @Comments               varchar(500),
        @Started                bit
    AS
    BEGIN
        INSERT INTO DTasks (ModuleID, Task, StartDate, PlannedEndDate, EstimatedEndDate, Status, Comments, Started, LastUpdated)
        VALUES (@ModuleID, @Task, @StartDate, @PlannedEndDate, @EstimatedEndDate, @Status, @Comments, @Started, GETDATE())

        SET @ID = SCOPE_IDENTITY()
    END 
    GO

How to capture the newly inserted row's ID value from C#?

2 Answers2