An explicit value for the identity column in table error

Question

An explicit value for the identity column in table 'mani' can only be specified when a column list is used and IDENTITY_INSERT is ON.

sqlCon.Open();
string query = "INSERT INTO mani VALUES (@id, @name, @age, @place)";

SqlCommand cmd = new SqlCommand(query, sqlCon);

cmd.Parameters.AddWithValue("@id", tableModel.id);
cmd.Parameters.AddWithValue("@name", tableModel.name);
cmd.Parameters.AddWithValue("@age", tableModel.age);
cmd.Parameters.AddWithValue("@place", tableModel.place);

cmd.ExecuteNonQuery();

You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Dec 29 '21 at 16:33
The error is telling you the problem here... If you want to provide an **explicit** value for your `IDENTITY` column then you need to turn `IDENTITY_INSERT` to `ON`. Otherwise, don't provide a value for your `IDENTITY` column. Though if you want to provide a explicit value, perhaps you shouldn't be using an `IDENTITY` in the first place? Also, make sure you define the columns you want to `INSERT` into in your `INSERT` clause; not doing so is a bad habit and can result in broken code when/if the table definition is updated. — Thom A, Dec 29 '21 at 16:34

score 2 · Answer 1 · answered Dec 29 '21 at 16:42

Your column "id" is an identity column and will automatically be given a value during any insert operation. To write to the table, you need to specify every column except that one:

sqlCon.Open();
string query = "INSERT INTO mani (name, age, place) VALUES (@name, @age, @place)";

SqlCommand cmd = new SqlCommand(query, sqlCon);

cmd.Parameters.AddWithValue("@name", tableModel.name);
cmd.Parameters.AddWithValue("@age", tableModel.age);
cmd.Parameters.AddWithValue("@place", tableModel.place);

cmd.ExecuteNonQuery();

score 1 · Answer 2 · answered Dec 29 '21 at 16:45

Your ID column is set to be an Identity column, it is also most like your primary key as well. Identity columns work much like "auto increment" columns in other RDBMS: they will generate the next value by themselves when an INSERT ocurrs. Which means that you don't need to pass in a value for that column when doing your query. Just delete the .AddWithValue line that sets the value for @id, and remove @id from the query and it should work. If there is a reason why you want to specify an explicit value for the record, you should not set the column to be an Identity column. If you have an Identity column but also want to set explicit values, that is probably not a good idea and will come back to bite you in the future.

An explicit value for the identity column in table error

2 Answers2