ExecuteScalar () returns zero absolute value data added to DB

Question

I have a code, as shown below, where I am trying to insert data into a table and return the identifier (set 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 a new element had not yet been added. But actually I see a new element that makes simple advice in the database.

My question is “when” data is actually added to the database and how can I get the identifier of the newly added item. Thanks!

Answer 1

You do not need two queries to create a new record and get a new identification 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 would not use such a database class, as it is error prone .

Answer 2

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

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

and INSERT does not select / return nothing.

 insert ... select ... 

See @Tim answer for more details.

Answer 3

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

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

Some things to take care of:

• <list of fields> is a comma separated list of columns for which you will specify values
• the list of fields should not include an auto increment identifier column (which is automatically assigned a value)
• <list of values> is a comma separated list of values ​​that will be inserted into the above fields. The number of values ​​must be equal to the number of fields, and the data types must match

Answer 4

The OUTPUT clause helps you get the identifier of the newly added item. For more information, see the link below:

Click here ! for more details