[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

insert identify is off

Posted on 2009-05-12
3
Medium Priority
?
317 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 1000 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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