C# with mssql, identity error

Question

Have a problem with setting up Identity_insert.

SqlCommand AddValueRecord = new SqlCommand($"SET IDENTITY_INSERT {TableName} ON "
                        + $"INSERT INTO " + TableName + $" VALUES ('{row.Field<string>(0)}',{row.Field<double>(1)},'{sqlFormattedDate}')"
                        + $"SET IDENTITY_INSERT {TableName} OFF", cn, tr);
                    AddValueRecord.ExecuteNonQuery();

All i have is ex

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

http://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity, http://stackoverflow.com/questions/12006273/set-identity-insert-on-is-not-working, and so on. — CodeCaster, Mar 18 '16 at 12:15
The error message says it all, you must specify a column list. — Lasse V. Karlsen, Mar 18 '16 at 12:23

Rahul Tripathi · Accepted Answer · 2016-03-18T12:17:45.363

1

You need to provide the missing the column list when you are doing the INSERT. try like

SqlCommand AddValueRecord = new SqlCommand($"SET IDENTITY_INSERT {TableName} ON "
                        + $"INSERT INTO " + TableName + $" + " (col1, col2, col3) " +VALUES ('{row.Field<string>(0)}',{row.Field<double>(1)},'{sqlFormattedDate}')"
                        + $"SET IDENTITY_INSERT {TableName} OFF", cn, tr);
                    AddValueRecord.ExecuteNonQuery();

edited Mar 18 '16 at 12:17

answered Mar 18 '16 at 12:14

Rahul Tripathi

168,305
31
280
331

Add extra space at the end of `INSERT INT ...` string. – tchelidze Mar 18 '16 at 12:16
@tchelidze:- Added the space. – Rahul Tripathi Mar 18 '16 at 12:18
Still a syntax error at `$" + " (col1, col2, col3) "`. – CodeCaster Mar 18 '16 at 12:19
Should i include column with identity? – Yura Gumenyuk Mar 18 '16 at 12:26

score 0 · Answer 2 · answered Mar 18 '16 at 12:14

You are trying to insert a value into the identity column of the table.

You are just inserting values without specifying columns so it will be trying to insert values into all the columns. You need to specify the columns, or better still use a parametrised query with the values you want to insert.

C# with mssql, identity error

2 Answers2