ExecuteSQLCommand SCOPE_IDENTITY() always return 1 (.NET C#)

Question

I'm using dynamic sql for insert data into dynamic table (.NET C#). I need to get the identity column value in return and I try to use scope_identity but the result always shows 1.

This is my code

dqDbContext dqx = new dqDbContext();
string queryHdr = "INSERT INTO " + tableHdrName + " VALUES ("
                        + configId + ",'Administrator',GETDATE()); SELECT SCOPE_IDENTITY();";

int id = dqx.Database.ExecuteSqlCommand(queryHdr);

Is there something that I missing here?

Thanks

Felix

Hi stakx, it was built in System.Data.Entity.. I use ms sql server db and the table have identity column.. I'll try another method.. Thanks — Felix Laksana, Feb 12 '15 at 11:14
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Feb 12 '15 at 11:16
hi marc_s is it possible to do some sql injection for parameter that not inputted by the user? because the parameter is not from user input.. but I'll give it a try to change it into parameterized.. thanks marc_s — Felix Laksana, Feb 12 '15 at 11:25
That quesion is not related with SQL Injection so i can't see any useful answer here??? — Can Ürek, Sep 08 '15 at 15:00
@CanÜrek it's *very* helpful and *very* relevant. What is the type of `configId` and what does it look after it gets formatted as a string? You *assume* it's an integer - what if it's a string or GUID? The query will be invalid — Panagiotis Kanavos, Feb 06 '18 at 14:23
@FelixLaksana most likely you are seeing the *first* result, which is the number of inserted rows. In any case, INSERT has an OUTPUT clause which can be used to return the new ID without an extra query, eg `INSERT INTO MyTable OUTPUT inserted.ID VALUES(…)` — Panagiotis Kanavos, Feb 06 '18 at 14:26
@FelixLaksana you aren't *truncating* the table before each test by the way? That will reset the ID to 1 — Panagiotis Kanavos, Feb 06 '18 at 14:27

score 2 · Answer 1 · answered Feb 06 '18 at 14:20

2

You can use SqlQuery instead, e.g:

var identity = ctx.Database
    .SqlQuery<int>(@"INSERT INTO MyTable (Column1) VALUES ('MyValue');
        SELECT CAST(SCOPE_IDENTITY() AS INT)")
    .Single();

answered Feb 06 '18 at 14:20

Moo

849
7
16

score 0 · Answer 2 · edited Feb 12 '15 at 10:55

0

add SET NOCOUNT ON;, I think you getting the value 1 is number of rows affeted.

string queryHdr = @"SET NOCOUNT ON; 
                    INSERT INTO " + tableHdrName + " VALUES ("
                         + configId + @",'Administrator',GETDATE());
                    SELECT SCOPE_IDENTITY();";

edited Feb 12 '15 at 10:55

stakx - no longer contributing

83,039
20
168
268

answered Feb 12 '15 at 10:51

Saravana Kumar

3,669
5
15
35

Hi Thanks for your answer, I just try your suggestion and now it return -1.. When I try it on Ms SQL Server Management Studio it show the last identity that I insert but when I try it on C# it return like that.. – Felix Laksana Feb 12 '15 at 11:02
@Felix Laksana: Post the code of 'dqx.Database.ExecuteSqlCommand' function – Saravana Kumar Feb 12 '15 at 11:04
It was built in System.Data.Entity here.. I'll try another method, I think ExecuteSQLCommand can't be used like that.. – Felix Laksana Feb 12 '15 at 11:10

ExecuteSQLCommand SCOPE_IDENTITY() always return 1 (.NET C#)

2 Answers2