Question

I have created a database where I use uniqueidentifier as the primary key. I m trying to run a parametrized query that in the end looks like this. (insert into someTable (uniqueID) Values( 76c14693-5475-4224-ba94-7d30c919ac59 ) (uniqueID is my PK). This insert statement executes without issues when I run it in SQL Server Management Studio, however, it fails when I try to run it from my code.

Error: Cannot insert explicit value for identity column in table someTable when IDENTITY_INSERT is set to OFF.

I have looked this up and there are a lot of suggestions on turning ON the IDENTITY_INSERT but they all use stored procedures. I need to be able to do this using a parametrized query. Or I need a way to get the newly created guid in the database. This seems like something everyone would need at some point. Are there any simple solutions?

PS. I need the ID because I need to add records to other tables using that ID.

EDIT:

Here is the Structure of the table in question

CREATE TABLE [dbo].[someTable]
( 
        [NewsID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_someTable_NewsID] DEFAULT (newid()), 
        [ShortTitle] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
        [DateCreated] [datetime] NULL CONSTRAINT [DF_someTable_DateCreated] DEFAULT (getdate()), 
        CONSTRAINT [PK_cmc_News_1] PRIMARY KEY CLUSTERED ( 
            [NewsID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF
        ) ON [PRIMARY] 
) ON [PRIMARY]

Answer 1

Actually, you must have some other problem, or some other column in you table, because you only would get that error if you have an IDENTITY column in your table, and IDENTITY columns can t be of the datatype uniqueidentifier - are you sure you re inserting into the same table, or you aren t using an ORM tool of some kind that is trying to specify a specific value for an identity column? Perhaps you could script the table definition out and attach it?

EDIT: Adding additional info since the question now includes the schema.

Looks like there is no identity in this particular table, so I d first check to see if you have any triggers defined on the table in question and see if there is an attempted insert taking place in another table that uses an identity...

Answer 2

IN the first place GUIDs area a horrible choice for primary keys.

If you are using them however, you should not also have the same filed defined as an identity field. Please post your table structure code and we can help you redefine it correctly.

Answer 3

The problem is that you are not allowed to insert an ID yourself (I assume this is because the engine wants to do it itself). To turn this feature on, you need to execute this query before your query:

SET IDENTITY_INSERT <tablename> ON

Answer 4

You have set the Identity property to On for the field, which means that the database creates the id when you insert a record. If you set Identity to Off for the field, you can set the value in the insert.

Alternatively, let the database create the id, then get the id that was created:

insert into someTable ();select scope_identity()

Note: A stored procedure and a parameterised query are not exclusive. I always call the stored procedures using a parameterised query.

友情链接