ExecuteScalar() returns null altough data was added to DB

Question

I have a code as bellow where I try to insert a data into a table and return the ID (given by auto increment) of the new element.

int newEquipmentID = new int();

query = database.ParameterizedQueryString("INSERT INTO Equipment (EquipmentTypeID) VALUES ({0})", "equipmenttypeID");

newEquipmentID = (int)database.Connection.ExecuteScalar(query, DefaultTimeout, equipment.EquipmentTypeID);

But it fails and returns null, as if the new item wasn't added yet. But actually I can see the new item making a simple consult at the DB.

My question is "when" the data is actually added into the DB and how can I get the ID of the new added item. Thanks!

Answer 1

You don't need two queries to create the new record and retrieve the new identity value:

using (var con = new SqlConnection(ConnectionString)) {
    int newID;
    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newID = (int)insertCommand.ExecuteScalar();
    }
}

Side-Note: I wouldn't use such a Database-Class since it's prone to errors.

Answer 2

Your SQL query does not return the newly generated Id. To return it, use an OUTPUT clause:

INSERT INTO Equipment (<list of fields>) 
    OUTPUT inserted.EquipmentTypeID 
VALUES (<list of values>)

Some things to be careful about:

• <list of fields> represents a comma separated list of columns for which you will supply values
• the list of fields should not include the auto-increment ID column (that will be automatically assigned a value)
• <list of values> represents a comma separated list of values that will be inserted in the above specified fields. The number of values should be equal to the number of fields and the data types must match