2 rows inserted instead of 1 when using @@IDENTITY / scope_identity()

Question

using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        string query = "INSERT INTO SocialGroup (created_by_fbuid) VALUES (@FBUID); SELECT CAST(scope_identity() AS int)";
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@FBUID", FBUID);

        connection.Open();
        command.ExecuteNonQuery();

        int lastID = (int)command.ExecuteScalar();

    }

Without the

SELECT CAST(scope_identity() AS int)

One row is inserted. But since I need the ID from the created row im using scope_identity. However, when I use this, 2 rows are created instead of one.

Did I miss something?

Thanks

score 4 · Accepted Answer · answered May 24 '11 at 12:59

The problem in the code you've posted is that you run 2 times the same query... one with ExecuteNonQuery(); and the last with (int)command.ExecuteScalar();

If you try to use only the executeScalar i think you have the result's you want....

Try and hope this helps...

If you want you can use Parameter to retrieve the Identity, like they do in this Article

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

If you would use gbn or my answer from your first question, the problem shouldn't occur.

edited May 23 '17 at 12:04

Community

1
1

answered May 24 '11 at 12:55

Oliver

43,366
8
94
151

Tried the OUTPUT, same result though. – Johan May 24 '11 at 13:03
Never mind, forgot to remove the command.ExecuteNonQuery();. Thanks – Johan May 24 '11 at 13:10

score 0 · Answer 3 · answered May 24 '11 at 13:07

Try doing

 using (SqlConnection connection = new SqlConnection(ConnectionString))     
    {         
    string query = "INSERT INTO SocialGroup (created_by_fbuid) VALUES (@FBUID);";         
    SqlCommand command = new SqlCommand(query, connection);            
    command.Parameters.AddWithValue("@FBUID", FBUID);         
     connection.Open();         
    command.ExecuteNonQuery();
     query = "SELECT CAST(scope_identity() AS int)";     
     command = new SqlCommand(query, connection);                   
    int lastID = (int)command.ExecuteScalar();      
    }

2 rows inserted instead of 1 when using @@IDENTITY / scope_identity()

3 Answers3