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.