CODEWITHSUNDEEP
c#sqlsql-server
Johan
Johan

Reputation: 203

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

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

Upvotes: 1

Views: 477

Answers (3)

FIre Panda
FIre Panda

Reputation: 6637

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();      
    }

Upvotes: 0

2GDev
2GDev

Reputation: 2466

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

Upvotes: 4

Oliver
Oliver

Reputation: 45109

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

Upvotes: 0

Related Questions