c# select scope_identity() always returns null even though raw sql works fine

Question

I have this simple query for inserting a new row into the database:

insert into Employees (name, salary) values ('123', 100); SELECT SCOPE_IDENTITY() as last_id;

This query run fine and return the recently added id but when i run the query in c#, it always return null.

Here is my c# code:

public static void InsertEmployee()
{
    string sql = @"insert into Employees (name, salary) values ('123', 100);  SELECT SCOPE_IDENTITY() as last_id; ";

    SqlCommand command = new SqlCommand(sql, GetConnection());

    command.Connection.Open();
    var id = command.ExecuteScalar();
    command.Connection.Close();
}

There's nothing wrong with SCOPE_IDENTITY but you don't need it. You can add an [`OUTPUT` clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15) in the INSERT statement to return the new ID directly, eg `output inserted.ID` — Panagiotis Kanavos, Oct 21 '19 at 14:51
BTW you should *always* create connections inside `using` blocks. This code will leave the connection open if anything goes wrong — Panagiotis Kanavos, Oct 21 '19 at 14:52
@PanagiotisKanavos thank you for replying, i used the ouput clause before but it returns null like the scope_identity — Thuan Nguyen, Oct 21 '19 at 14:53
That works perfectly well too - for thousands of developers, over at least a decade. Which means there's a bug in your code. Perhaps there *is* an error and a `try/catch` hides the problem and returns null? You'll have to post something that actually reproduces the problem — Panagiotis Kanavos, Oct 21 '19 at 14:56
How/where did you check the return value? Your method doesn't return anything — Panagiotis Kanavos, Oct 21 '19 at 14:56
@PanagiotisKanavos i debug to check the value, and it is always null. And the insert does work. I just tried the try catch and the insert query worked fine but it still returned null. — Thuan Nguyen, Oct 21 '19 at 15:00
Try removing `as last_id;` from your query and see what happens? — Casey Crookston, Oct 21 '19 at 15:03
There are a lot of other questions about this here on SO. I'll link to a few: https://stackoverflow.com/questions/40594973/select-scope-identity-after-insert-with-sqlcecommand -- https://stackoverflow.com/questions/39111849/trying-to-get-select-scope-identity-as-c-sharp-variable -- https://stackoverflow.com/questions/39111849/trying-to-get-select-scope-identity-as-c-sharp-variable -- https://stackoverflow.com/questions/21388358/retrieving-the-last-inserted-id-using-scope-identity -- https://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql — Casey Crookston, Oct 21 '19 at 15:20
https://stackoverflow.com/questions/3913115/scope-identity-in-c-sharp-range -- https://stackoverflow.com/questions/40594973/select-scope-identity-after-insert-with-sqlcecommand — Casey Crookston, Oct 21 '19 at 15:21
... if none of those help you find the answer, let us know and I'll try and reproduce your problem on my end and figure out the solution for you. — Casey Crookston, Oct 21 '19 at 15:21
@CaseyCrookston i tried them before but none of them worked. I am new to c# so i think there is something else that causes this. I call the above function when user click on a button, does is affect the query? — Thuan Nguyen, Oct 21 '19 at 15:38
No, how you call the function does not impact the result of the query. — Casey Crookston, Oct 21 '19 at 16:40
Thuan, I just ran your code, as is, and I got back the ID of the newly created row. So, I think the problem is in your table design. Do you have a column defined as a primary key? And is that column an auto-incrementing integer? — Casey Crookston, Oct 21 '19 at 16:50
@CaseyCrookston yes i have an identity column because if i run this query in sql server, it returns the newly created ID. — Thuan Nguyen, Oct 22 '19 at 02:16

score 0 · Answer 1 · answered Sep 14 '22 at 23:41

0

object returnValue = sqlCmd.ExecuteScalar(); 
int result = System.Convert.ToInt32(returnValue);

answered Sep 14 '22 at 23:41

Stewart

51
5

c# select scope_identity() always returns null even though raw sql works fine

1 Answers1