Search code examples
c#sqlsql-server-cesql-server-ce-4

SQLCE: Why I get "duplicate value" error? The field is identity enabled?


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?


Solution

  • 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.