Cannot insert explicit value for identity column when IDENTITY_INSERT is set to OFF Error

0
votes

Getting this error when trying to insert a value to a table. the table was created using the below script:

    CREATE TABLE [dbo].[Audit_PatientControlledSubstanceAgreement](
    [PatientID] [bigint] NOT NULL,
    [UsersID] [bigint] NOT NULL,
    [DateAdded] [date] NOT NULL,
    [FileName] [nvarchar](500) NULL,
    [FilePath] [nvarchar](max) NULL,
    [FileType] [nvarchar](50) NULL,
    [NextScheduledDate] [date] NULL,
    [CreateDT] [datetime2](7) NULL,
    [AgreementType] [nvarchar](50) NULL,
    [Deleted] [bit] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Audit_PatientControlledSubstanceAgreement] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

I've even tried to change IDENTITY_INSERT to ON but still getting the same error. I'm using the below code to insert the item to the table:` 

    var Audit_PatientControlledSubstanceAgreement = new Audit_PatientControlledSubstanceAgreement
                            {
                                CreateDT = DateTime.Now,
                                Deleted = true,
                                AgreementType = model.AgreementType,
                                DateAdded = model.DateAdded,
                                FileName = model.FileName,
                                FilePath = model.FilePath,
                                FileType = model.FileType,
                                NextScheduledDate = model.NextScheduledDate,
                                PatientID = model.PatientID,
                                UsersID = model.UsersID
                            };

                            db.Audit_PatientControlledSubstanceAgreement.Add(Audit_PatientControlledSubstanceAgreement);
                            db.SaveChanges();

`

1
c#sql-serverentity-framework
In your code you do not specify a value for ID, do you? - Peter Bons
no I'm not specifying value for ID but still getting the exception. - Ahmed Mujtaba
What is the class definition of Audit_PatientControlledSubstanceAgreement ? And is it a code-first EF model? - Peter Bons

1 Answers

2
votes

Add this line above the Id:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Like this

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }