avoid the same input if it exist in table ? (Page 2 of 2)

That is what the collate nocase does!

So... I have been struggling quite a bit with the duplicate checking on records already existing when updating and I finally found something that works.

The above works well for a new record but it blocks any update on existing records considering an edit as a systemic duplicate.

My form has a double function of inserting new entries and displaying existing records so, the "show record" and "new record" do not behave as expected, especially the "show record" that requires to double-clic twice (4 clicks) to retrieve the current id/generated id.

If someone has the same kind of issue, here is the fruition of a long battle and some headaches.

The key is in the operator != which compares the values left and right and returns a false instead of the <> which returns a true and with SQL all conditions must be true to get a true result. If at least one condition is false then the result could be either false or incomplete, This is why it did not worked for updates since the result was always true, hence duplicate alert. In a new record, the ids old (-1) and new (next id number) are different while when updating, the ids are the same. SQLite creates internally an "old.id" and a "new.id" before each transaction, those can also be used in triggers and are used for internal rollback.

procedure Form_SaveButtonName_OnClick (Sender: string; var Cancel: boolean);
var id: integer;
begin

  if  (SQLExecute ('SELECT COUNT(id) FROM table WHERE (column COLLATE NOCASE ="'+Form.TextBox.Text+'") and (id!="'+inttostr(Form.SaveButtonName.dbGeneralTableId)+'")')) > 0 then
     begin  // Replace <Record> by proper descriptive field
          MessageDlg('This <Record> already exists:  ' +Form.TextBox.Text +#13+'Entry canceled', mtError, mbOk, 0); // Displays a red 'X', header is: Error
          Cancel := True;
          end
          else
          begin
           MessageDlg('This <Record> has been saved:  ' +Administration.Administr_WinID.Text, mtInformation, mbOk, 0); // Displays an Info icon header is: Information
          Cancel := False;
     end;

end;