Solved

insert identify is off

Posted on 2009-05-12
3
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

688 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