identity_insert is turned on but there is an error,yet

Question

With following code,I have identity_insert turned on to be able to insert some value explicitly:

SET IDENTITY_INSERT dbo.myTable ON
Go

the identity insert is set to be primary key and not null

in my c# program,when i want to insert a value,this error is generated:

IDENTITY_INSERT is turned off

but i have turned it on

what's the problem and how to solve it?

thank you

Answer 1

The IDENTITY_INSERT setting only works in the current SQL session, e.g. you cannot turn it on on a given table in SQL Server Management Studio and then run your C# code against it and expect it to work.

What you need to do is have that SET IDENTITY_INSERT dbo.myTable ON statement be part of your C# app - first, set the setting to ON in your command, then do whatever you need to do from your C# app, and in the end, turn off that setting, again, from your C# code.