Re: avoid the same input if it exist in table ?

Got it, to check case insensitive duplicates, use COLLATE NOCASE:

function DupliChk (sTable, sField, sValue: string;): boolean;
var d: string;
begin

    d := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE '+sField+' COLLATE NOCASE = "' + sValue + '"');
    if StrToInt(d) > 0 then result := True else result := False;
end;

It works until someone tries to cheat it with a space before or after the entry...

27 (edited by pavlenko.vladimir.v 2023-11-11 14:29:46)

Re: avoid the same input if it exist in table ?

tcoton wrote:

It worked the other way around, I tested it but you are right, it looks more logical your way.

Now, I have to find a way to check on the vicious duplicates, the one mixing lower case and uppercase like: HR ..... Hr ..... hr ....  hR ...., Human Resources..... human resources  .... HUMAN RESOURCES ....  those are all duplicates!!! sad

procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
begin
  if DupliChk(Form1.Edit1.dbTable,Form1.Edit1.dbField,Form1.Edit1.Text) then
  begin   //replace object by self-explanatory text
    MessageDlg('This object already exists:  ' +Form1.Edit1.Text +#13+'Entry cancelled', mtError, mbOk, 0); // Displays a red 'X', header is: Error
    Cancel := True;
    Form1.Edit1.Clear;
  end;
end;

function DupliChk (sTable, sField, sValue: string;): boolean;
begin
  if (SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE upper('+sField+') = upper("' + sValue + '")') = 0) then
      result := False else result := True;
end;

Re: avoid the same input if it exist in table ?

That is what the collate nocase does! wink

Re: avoid the same input if it exist in table ?

tcoton wrote:

That is what the collate nocase does! wink

Search through LIKE in the program is case-insensitive.
And searching without "%" at the end and at the beginning will allow you to search for a complete match.
For spaces at the end and beginning of text, use TRIM. It’s better if it’s still at the stage of entering into EDIT.
Unfortunately, nothing will protect against extra spaces inside the text, commas, periods, apostrophes, etc.
Of course, all this can be processed.
As for COLLATE, there are some restrictions that do not always allow its use. And you only forcefully turn it on.
There are other options for limiting duplicate entries.

30 (edited by tcoton 2023-11-20 21:07:13)

Re: avoid the same input if it exist in table ?

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;

Re: avoid the same input if it exist in table ?

Hi T,
I guess we all have our own slightly different ways of testing for duplicates.
Attached is another option (as always, I like to keep things simple and my script as small as possible).
Its down-side (albeit not a major one) is that it introduces an element of data redundancy by storing a concatenation of the particular fields that you want to perform a duplicate check against.
Anyway, maybe it can give you some ideas.
Regards,
Derek.

Post's attachments

Attachment icon duplicate records test.zip 438.82 kb, 54 downloads since 2023-11-21