Error entering SQL Insert command
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();
source to share
Yes, that means the column Id
is a column IDENTITY
, so you have to omit it in your statement INSERT
like below. Since this is a column IDENTITY
, you cannot explicitly insert values for it unless you set IDENTITY_INSERT
in OFF
, which you think you would not want to do anyway
INSERT INTO DriversDetailsTable (name, DateJoined) VALUES ('Driver4', '2017-07-06')
SideNote: See no reason for string interpolation syntax $@"INSERT
for C # 6 when you are using hardcoded / static values
source to share
Presumably Id
a column IDENTITY
. This way you shouldn't try INSERT
on a column Id
- omit it from the statement INSERT
and let the database tell you what number it got:
SqlCommand command = new SqlCommand($@"
INSERT INTO DriversDetailsTable (name, DateJoined) VALUES ('Driver4', '2017-07-06');
SELECT SCOPE_IDENTITY();", conn);
and use ExecuteScalar()
to return the value:
int id = (id)(decimal)command.ExecuteScalar();
(see Why select SCOPE_IDENTITY () returns decimal instead of integer? for an explanation of the cast)
Other parameters:
- make it not a column
IDENTITY
- temporarily disable insertion for identification during this operation if you need a specific value (not recommended in most cases)
source to share
If you really want to insert an ID for any reason, you need to set IDENTITY_INSERT
in ON
like this:
Set Identity_Insert DriverDetailsTable ON;
This can only be done for this specific command:
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);
source to share