Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
ALTER TABLE [tblTemplates] ADD CONSTRAINT [PK__tblTemplates__templateID] PRIMARY KEY ([templateID]);
CREATE INDEX [IDX_tblTemplates_personID] ON [tblTemplates] ([personID] ASC);
CREATE UNIQUE INDEX [UQ__tblTemplates__templateID] ON [tblTemplates] ([templateID] ASC);
Why I get this error?
                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, 2011 at 8: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, 2011 at 8:07
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)
                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
                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.
                Switching away from auto-assigned Ids and using uniqueidentifier/Guid seems to have 'fixed' this issue for me.
– danw
                Mar 19, 2013 at 3:58
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();
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.