• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Cannot insert explicit value for identity column in table 'Tbl_DataEntry' when IDENTITY_INSERT is set to OFF.

I am getting this error while trying to insert/update into a table.

The primary key in that table is already set to ON. and incremented by 1.
Cannot insert explicit value for identity column in table 'Tbl_DataEntry' when IDENTITY_INSERT is set to OFF.
0
shpresa
Asked:
shpresa
1 Solution
 
shpresaAuthor Commented:
Here is my code  where DataentryID is primary key thehe table.
ALTER   PROCEDURE [dbo].SP_CCC
      @OrgID as int,
            @TaskAss1 as nchar(10),
      @PosFeed1 as nchar(10),
      @BriefHuddle1 as nchar(10),
      @Step1 as nchar(10),
      @Handoff1 as nchar(10),
      @Challenges1 as nchar(10)

)      

 AS BEGIN
 declare @DataEntryID int         
 
 set @DataEntryID  = (select DataEntryID from Tbl_DataEntry where OrgID = @OrgID)
IF EXISTS (SELECT * FROM Tbl_DataEntry WHERE DataEntryID = @DataEntryID AND OrgID = @OrgID)
      update Tbl_DataEntry set
 OrgID = @OrgID,

            TaskAss1 = @TaskAss1,
      PosFeed1 = @PosFeed1 ,
      BriefHuddle1 = @BriefHuddle1,
      Step1 = @Step1,
      Handoff1   = @Handoff1,  
      Challenges1   = @Challenges1

      
      where DataEntryID = @DataEntryID
 ELSE
      insert into Tbl_DataEntry
      
(
      DataEntryID,
      OrgID ,
            TaskAss1 ,
      PosFeed1 ,
      BriefHuddle1 ,
      Step1 ,
      Handoff1 ,
      Challenges1
 )
      
      VALUES
      (
      @DataEntryID,
      @OrgID ,
            @TaskAss1,
      @PosFeed1,
      @BriefHuddle1,
      @Step1,
      @Handoff1,
      @Challenges1,

)



end
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Don't insert the data entry ID . . . that be populated for you:

 ELSE
      insert into Tbl_DataEntry
     
(
         OrgID ,
            TaskAss1 ,
      PosFeed1 ,
      BriefHuddle1 ,
      Step1 ,
      Handoff1 ,
      Challenges1
 )
     
      VALUES
      (
   
      @OrgID ,
            @TaskAss1,
      @PosFeed1,
      @BriefHuddle1,
      @Step1,
      @Handoff1,
      @Challenges1,

)

select  @@identity  -- return the newly created primary key.

end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now