0

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

samsam114
  • 987
  • 2
  • 8
  • 20
  • Are you turning it on from within the C# program? I don't think it's a global setting -- it's per-session. You'll need to turn it on from within your C# code, do your inserts and then turn it back off. – PatrickSteele Jul 30 '10 at 13:39
  • I have a great deal of concern that you are using set identity_insert on from C#. This is not something that should be done routinely from a user interface. It is very dangerous practice Doing this wrong is threat to data integrity and these tasks should only be used by experience dbas and not by an application. Usually this is only done on rare occasions when inserting legacy data. If you are planning your design to use it regulary, you need to examine if there isn't a better solution. – HLGEM Jul 30 '10 at 16:56

1 Answers1

2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459