How to Insert row into SQL database with IDENTITY_INSERT set to OFF using WCF services?

Question

An SQL table has auto increment property set for the primary key AnnotationID of INT type.

The Silverlight application WCF service code:

    public void InsertImageAnnotation(int imageId, string imagePath)
    {
        DataClassDataContext db = new DataClassDataContext();
        ImageAnnotation row = new ImageAnnotation();

        row.ImageID = imageId;//foreign key
        row.ImagePath = imagePath;

        db.ImageAnnotations.InsertOnSubmit(row);
        db.SubmitChanges();            
    }

An exception is thrown on db.SubmitChanges() with the message: Cannot insert explicit value for identiy column in the table ImageAnnotations when IDENTITY_INSERT is set to OFF.

I am not specifying the primary key, which is supposed to be the identity key, becasue I expect the db would take care of the auto-incrementing the primary key value.

The table create script is here:

USE [ImagingSericesDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ImageAnnotations](

[AnnotationID] [int] IDENTITY(0,1) NOT NULL,

[ImageID] [int] NOT NULL,

[ImagePath] [text] NULL,

CONSTRAINT [PK_ImageAnnotations] PRIMARY KEY CLUSTERED

( [AnnotationID] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[ImageAnnotations] WITH CHECK ADD CONSTRAINT [FK_ImageAnnotations_Images] FOREIGN KEY([ImageID]) REFERENCES [dbo].[Images] ([AIImageID]) GO

ALTER TABLE [dbo].[ImageAnnotations] CHECK CONSTRAINT [FK_ImageAnnotations_Images]

What I am missing in my service call syntax? How can I specify or execute IDENTITY_INSERT to ON in my service code above? What is the syntax? Thanks a lot, Val

Answer 1

You are sending a value for the identity field when it wants to create it automatically. That's what the error message means. Check to see what code you are generating to see what the problem is. It is also entirely possible the problem is not in your insert but in an insert from a trigger, so check that as well.

Under no circumstances should you set the identity insert to on. Either you are sending an identity value that you don't need to send or you shouldn't be using an identity to generate the id. Identity insert is used generally only by dbas to insert legacy data before a databse goes live. It should very rarely be used at any other time and never by someone who has to ask the syntax. This is a command that can royally screw up your database, do not use it.