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