3

In my query I don't use primary key field because identity setting is enabled. 

   string sql = @"
                  INSERT INTO [tblTemplates] (personID, hash, data) 
                      VALUES (@personID, @hash, @data)";

   cmd = new SqlCeCommand(sql, cn);
   cmd.Parameters.AddWithValue("@personID", newTemplate.personID);
   cmd.Parameters.AddWithValue("@hash", newTemplate.templateHash);
   cmd.Parameters.AddWithValue("@data", newTemplate.templateData);

   cmd.ExecuteNonQuery();

Randomly I can or cannot insert a record then an exception thrown:

A duplicate value cannot be inserted into a unique index.
[ Table name = tblTemplates,Constraint name = PK_tblTemplates_templateID ]

This is the table schema:

-- Script Date: 26.08.2011 10:37  - Generated by ExportSqlCe version 3.5.1.5
CREATE TABLE [tblTemplates] (
  [templateID] int NOT NULL  IDENTITY (1,1)
, [hash] nvarchar(100) NOT NULL
, [data] image NOT NULL
, [personID] int NOT NULL
);
GO
ALTER TABLE [tblTemplates] ADD CONSTRAINT [PK__tblTemplates__templateID] PRIMARY KEY ([templateID]);
GO
CREATE INDEX [IDX_tblTemplates_personID] ON [tblTemplates] ([personID] ASC);
GO
CREATE UNIQUE INDEX [UQ__tblTemplates__templateID] ON [tblTemplates] ([templateID] ASC);
GO

Why I get this error?

Nime Cloud
  • 6,162
  • 14
  • 43
  • 75
  • Because the PK column can never have duplicate entries. But i am sure your expecting much more than this, but from your question its not clear. – Zenwalker Aug 26 '11 at 08:00
  • Is this code the only one that writes in the table? Or might there be other people/applications writing it? Was the table empty when you started or was there any data in it (maybe imported from an older application's DB) – Paolo Falabella Aug 26 '11 at 08:07
  • 2
    You don't need UQ__tblTemplates__templateID because a primary key *is* unique. – gbn Aug 26 '11 at 08:12
  • 2
    also, if your application is multi-threaded this may be a [bug in SQL CE](http://connect.microsoft.com/SQLServer/feedback/details/641518/sql-ce-4-0-entity-framework-a-duplicate-identity-key-error-while-inserting-in-multi-threading-application) – Paolo Falabella Aug 26 '11 at 08:16

2 Answers2

4

It seems like a bug!

Workaround: Converting field data type from int to uniqueidentifier works.

My workaround attempts:

Attempt #1: Same connection

        bool executed = false;
        int counter = 0;

        while (!executed)
        {
            try
            {
                cmd.ExecuteNonQuery();
                succes = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("SERVER> (Error) Exception in AddTemplate() {0},{1}", ex.Source, ex.Message);
                System.Threading.Thread.Sleep(100);
            }

            counter++;

        }

Result: This seems like an endless loop.

Attempt #2: New connection

            try
            {
                cmd.ExecuteNonQuery();
                succes = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("SERVER> (Error) Exception in AddTemplate() {0},{1}", ex.Source, ex.Message);
                System.Threading.Thread.Sleep(100);
                AddTemplate(newTemplate); //Warning: Recursive call!
            }

Result: This try helped after a few recursive calls.

Nime Cloud
  • 6,162
  • 14
  • 43
  • 75
  • Switching away from auto-assigned Ids and using uniqueidentifier/Guid seems to have 'fixed' this issue for me. – danw Mar 19 '13 at 03:58
0

I had similar problem. I used IDENTITY_INSERT on my table like 

SET IDENTITY_INSERT MyTable ON;
-- some identity insert on MyTable
SET IDENTITY_INSERT MyTable OFF;

After this, all inserts on MyTable throw "duplicate value" error.

Solution to this problem was 

var cmd = Connection.CreateCommand();                
cmd.CommandText = "SELECT MAX([Id] ) + 1 from [MyTable]";
object i = cmd.ExecuteScalar();
if (i != null && i is int)
{
    cmd.CommandText = "ALTER TABLE [MyTable] ALTER COLUMN [Id] IDENTITY (" + i + ",1)";
    cmd.ExecuteNonQuery();
}
Hacko
  • 1,551
  • 1
  • 15
  • 11