constraint failed

[oz8hp]

I use the following code to insert 7 records for a user in a table.
But the function gives me a 'coinstraint failed' when the record exists already. The reason for running the code more than once is to be absolute sure that all 7 records exist.
I have a function that creates 365 records for a year in another table and there it is the same problem.
How can I avoid this error and get my records created?
Delphi XE, UniDAC 3.70.0.19, SQLite (the error also is on MS Access and MySQL)
FormatSettings.ISOLongDayNames is an extension to FormatSettings.LongDayNames that gives me localized daynames
DefaultDay is a Double with value 7.4
UserProfile.ID is the current logged on user

procedure CalendarWeekCreate;
var
Query: TUniQuery;
DayNumber: integer;
begin
Query := TUniQuery.Create(Nil);
try
Query.Connection := frmDBConn.conDBserver;
Query.SQL.Clear;
Query.SQL.Add('INSERT INTO ' + TableCalendarWeek);
Query.SQL.Add('(flddaynumber, flddaytext, flddaynormal, flduser)');
Query.SQL.Add('values (:flddaynumber, :flddaytext, :flddaynormal, :flduser)');
try
for DayNumber := 1 to 7 do
begin
Query.ParamByName('flddaynumber').AsInteger := DayNumber;
Query.ParamByName('flddaytext').AsString := FormatSettings.ISOLongDayNames(DayNumber);
if DayNumber < 6 then
Query.ParamByName('flddaynormal').AsFloat := DefaultDay
else
Query.ParamByName('flddaynormal').AsFloat := 0;
Query.ParamByName('flduser').AsString := UserProfile.ID;
Query.Execute;
end;
except
on E: exception do
begin
Logfile.Error('CalendarWeekCreate: ' + E.Message);
end
end;
finally
Query.Free;
end;
end;

[AlexP]

Hello,

This error is connected with the fact that there is a unique constraint for one or several fields. To avoid this problem, you can change the fields' uniqueness or check the record's availability in the table before inserting it.

P.S. Please send a complete script to create your table.

[oz8hp]

But in VBA ex you can just catch the error and then resume at next step - this I cant figure out how to do here.

In an SQL query you can also do it so it just continues with the next step.

PS: I have mailed you a complete Access db with the table tblweek (and others)

[oz8hp]

After messing a bit around with my code I found a solution for getting on with the project.
As you can see it is all about where the try-except-end are located

procedure CalendarWeekCreate;
var
Query: TUniQuery;
DayNumber: integer;
begin
FormatSettings.Create('da-DK');
Query := TUniQuery.Create(Nil);
Query.Connection := frmDBConn.conDBserver;
try
Query.Connection := frmDBConn.conDBserver;
Query.SQL.Clear;
Query.SQL.Add('INSERT INTO ' + TableCalendarWeek);
Query.SQL.Add('(flddaynumber, flddaytext, flddaynormal, flduser)');
Query.SQL.Add('values (:flddaynumber, :flddaytext, :flddaynormal, :flduser)');
for DayNumber := 1 to 7 do
try
begin
Query.ParamByName('flddaynumber').AsInteger := DayNumber;
Query.ParamByName('flddaytext').AsString := FormatSettings.ISOLongDayNames(DayNumber);
if DayNumber < 6 then
Query.ParamByName('flddaynormal').AsFloat := DefaultDay
else
Query.ParamByName('flddaynormal').AsFloat := 0;
Query.ParamByName('flduser').AsString := UserProfile.ID;
Query.Execute;
end;
except
on E: exception do
begin
Logfile.Error('CalendarWeekCreate: ' + E.Message);
Continue;
end
end;
finally
Query.Free;
end;
end;

[AlexP]

Hello,

To obtain the functionality you need, you can actually use the Query.Execute method directly in the try...except block. However, in this case, the cycle will run independently of any error that occurs (all errors will be suppressed).

[oz8hp]

You are right there - Query.ParamByName should not be necessary to include in try-except.
Maybe I will change that at a later time when I go to work on optimizing the code.
I use to make code that works and then I will spend time optimizing after that. Users tend to need program at once