Solved

insert identify is off

Posted on 2009-05-12
3
308 Views
Last Modified: 2012-05-06
Cannot insert explicit value for identity column in table 'tbl_index_ticket_pocs' when IDENTITY_INSERT is set to OFF

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE dbo.sprInsertIntoIndexTicketPOCs
(
     @TicketID INT = null,
     @POCID INT = null,
     @DateAdded DATETIME = null,
     @AgentAdded NVARCHAR(50) = null,
     @DateCreated DATETIME,
     @ticketPOCIndexID INT = null OUTPUT,
     @resultCode INT OUTPUT,
     @resultMessage NVARCHAR(200) OUTPUT
)
 AS  
 BEGIN  
         SET NOCOUNT ON
         INSERT INTO [tbl_index_ticket_pocs]
         (
                 [TicketPOCIndexID],
                 [TicketID],
                 [POCID],
                 [DateAdded],
                 [AgentAdded],
                 [DateCreated]
         )
         VALUES
         (
                 @TicketPOCIndexID,
                 @TicketID,
                 @POCID,
                 @DateAdded,
                 @AgentAdded,
                 @DateCreated
         )
         SET @ticketPOCIndexID=SCOPE_IDENTITY()
 END    
GO

// Insert into tbl_index_pocs
                ItemCreatedResult res1 = new ItemCreatedResult();
 
                string sql1 = "sprInsertIntoIndexTicketPOCs";
                DbCommand cmd1 = db.GetStoredProcCommand(sql1);
                db.AddInParameter(cmd1, "@TicketID", DbType.Int32, res.ItemID);
                db.AddInParameter(cmd1, "@POCID",    DbType.Int32, Convert.ToInt32(strPOCString));
                
                db.AddInParameter(cmd1, "@DateAdded", DbType.DateTime, ticket.DateCreated);
                db.AddInParameter(cmd1, "@AgentAdded", DbType.String, ticket.AgentCreated); 
                db.AddInParameter(cmd1, "@DateCreated", DbType.DateTime, ticket.DateCreated);
           
                db.AddOutParameter(cmd1, "@ticketPOCIndexID", DbType.Int32, 4);
                db.AddOutParameter(cmd1, "@resultCode", DbType.Int32, 4);
                db.AddOutParameter(cmd1, "@resultMessage", DbType.String, 200);
                db.ExecuteNonQuery(cmd1);--> errors here
 
 
                res1.Code = Convert.ToInt32(db.GetParameterValue(cmd1, "resultCode"));
                res1.Message = db.GetParameterValue(cmd1, "resultMessage").ToString();
                res1.ItemID = Convert.ToInt32(db.GetParameterValue(cmd1, "TicketID"));
                res = res1;

Open in new window

0
Comment
Question by:mathieu_cupryk
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 24369306
ALTER PROCEDURE dbo.sprInsertIntoIndexTicketPOCs
(
     @TicketID INT = null,
     @POCID INT = null,
     @DateAdded DATETIME = null,
     @AgentAdded NVARCHAR(50) = null,
     @DateCreated DATETIME,
     @ticketPOCIndexID INT = null OUTPUT,
     @resultCode INT OUTPUT,
     @resultMessage NVARCHAR(200) OUTPUT
)
 AS  
 BEGIN  
         SET NOCOUNT ON
         INSERT INTO [tbl_index_ticket_pocs]
         (
               
                 [TicketID],
                 [POCID],
                 [DateAdded],
                 [AgentAdded],
                 [DateCreated]
         )
         VALUES
         (
               
                 @TicketID,
                 @POCID,
                 @DateAdded,
                 @AgentAdded,
                 @DateCreated
         )
         SET @ticketPOCIndexID=SCOPE_IDENTITY()
 END    
GO
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24369318
hmmm... you don't learn your lessons, don't you? ...
ALTER PROCEDURE dbo.sprInsertIntoIndexTicketPOCs
( 
     @TicketID INT = null,
     @POCID INT = null, 
     @DateAdded DATETIME = null,
     @AgentAdded NVARCHAR(50) = null,
     @DateCreated DATETIME,
     @ticketPOCIndexID INT = null OUTPUT,
     @resultCode INT OUTPUT,
     @resultMessage NVARCHAR(200) OUTPUT
)
 AS  
 BEGIN  
         SET NOCOUNT ON
         INSERT INTO [tbl_index_ticket_pocs]
         (
                 [TicketID],
                 [POCID],
                 [DateAdded],
                 [AgentAdded],
                 [DateCreated]
         )
         VALUES
         (
                 @TicketID,
                 @POCID,
                 @DateAdded,
                 @AgentAdded,
                 @DateCreated
         )
         SET @ticketPOCIndexID=SCOPE_IDENTITY()
 END     

Open in new window

0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24369629
What I understand is IDENTITY_INSERT must be set to ON to insert explicit values which actually put automatic identity value insertion OFF.
So use the code snippet provided in your stored procedure before the INSERT statement.
For further reading see this link:http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx

SET IDENTITY_INSERT tbl_index_ticket_pocs ON

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question