SQL Insert command throwing errors

Question

 System.Data.SqlClient.SqlException occurred   HResult=0x80131904   Message=Cannot insert explicit value for identity column in table 'DriversDetailsTable' when IDENTITY_INSERT is set to OFF.   Source=.Net SqlClient Data Provider   StackTrace: <Cannot evaluate the exception stack trace>

I keep getting the above error when i run my code, not sure why. Here is my code:

SqlConnection conn = new SqlConnection(@"Data Source = ComputerName\SQLEXPRESS;Initial Catalog = Drivers;Trusted_Connection=True;");
        conn.Open();

        SqlDataAdapter adapter = new SqlDataAdapter();

        SqlCommand command = new SqlCommand($@"INSERT INTO DriversDetailsTable (name, Id, DateJoined) VALUES ('Driver4', 1, '2017-07-06')", conn);
        command.ExecuteNonQuery();

        conn.Close();

Your SQL is the problem. do you have a 4th column at the beginning that is meant to be auto incremented? — , Jul 06 '17 at 08:37

score 3 · Answer 1 · answered Jul 06 '17 at 08:17

Yeah, that's cause your Id column is an IDENTITY column and so you should omit it in your INSERT statement like below. Since it's an IDENTITY column you can't explicitly insert values for it unless you set IDENTITY_INSERT to OFF which don't think you wanted to do anyways

INSERT INTO DriversDetailsTable (name, DateJoined) VALUES ('Driver4', '2017-07-06')

SideNote: don't see a reason for C# 6 syntax of string interpolation $@"INSERT when you are using hardcoded/static values

score 2 · Answer 2 · answered Jul 06 '17 at 08:18

Presumably Id is an IDENTITY column. As such, you shouldn't attempt to INSERT into the Id column - omit it from the INSERT statement, and let the database tell you what number it got assigned:

SqlCommand command = new SqlCommand($@"
INSERT INTO DriversDetailsTable (name, DateJoined) VALUES ('Driver4', '2017-07-06');
SELECT SCOPE_IDENTITY();", conn);

and use ExecuteScalar() to get the value back:

int id = (id)(decimal)command.ExecuteScalar();

(see Why does select SCOPE_IDENTITY() return a decimal instead of an integer? for an explanation of the cast)

Other options:

make it not be an IDENTITY column
temporarily disable identity insert during this operation if you need a specific value (not recommended in most cases)

score 0 · Answer 3 · answered Jul 06 '17 at 08:21

If you do want to be able to insert the id for some reason you need to set IDENTITY_INSERT to ON like so:

Set Identity_Insert DriverDetailsTable ON;

This can be done just for that particular command like so:

SqlCommand command = new SqlCommand($@"
Set Identity_Insert DriverDetailsTable ON;
INSERT INTO DriversDetailsTable (name, Id, DateJoined) VALUES ('Driver4', 1, '2017-07-06');
Set Identity_Insert DriverDetailsTable OFF;", conn);

SQL Insert command throwing errors

3 Answers3