CODEWITHSUNDEEP
t-sql
Richard77
Richard77

Reputation: 21609

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

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.

Upvotes: 0

Views: 334

Answers (2)

Farfarak
Farfarak

Reputation: 1517

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

Upvotes: 1

Oded
Oded

Reputation: 498894

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#.

Upvotes: 4

Related Questions