?
Solved

insert identify is off

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 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