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!

Synthesis

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.

Katheykathi

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

Thomajan

Answer 3

To return the id of just inserted row you need to select it, because ExecuteScalar() returns

the first column of the first row in the result set returned by the query

and INSERT doesn't select/return anything.

insert ...
select ...

See @Tim's answer for more details.

Malmo

ExecuteScalar() returns the first field of an element (the top line left column element) – Synthesis

But I don't want the number of affected rows, I want the ID of the added row – Synthesis

Answer 4

OUTPUT Clause will help you to get the ID of the new added item. For more information please see the link below :

Click here! for more detail

Journey