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

Hi how can i avoid the same input of a data if ti already exist in the table?

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

function for check:

function CheckDublicate (Action, sTable, sField, sValue: string; id: integer;): boolean;
var
   s: string;
begin
     result := False;
     if Action = 'NewRecord' then
     begin
          s := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE '+sField+' LIKE "' + sValue + '"');
          if StrToInt(s) > 0 then result := True;
     end;

     if Action = 'ShowRecord' then
     begin
          s := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE ('+sField+' LIKE "' + sValue + '") AND (id <> '+ IntToStr(id) +')');
          if StrToInt(s) > 0 then result := True;
     end;
end;


// Event OnClick of button for save record
procedure frmEmployee_Button2_OnClick (Sender: string; var Cancel: boolean);
begin
     if CheckDublicate(frmEmployee.dbAction, 'employees', 'lastname', frmEmployee.edLastName.Text, frmEmployee.ButtonSave.dbGeneralTableId) AND
        CheckDublicate(frmEmployee.dbAction, 'employees', 'firstname', frmEmployee.edFistName.Text, frmEmployee.ButtonSave.dbGeneralTableId) then
     begin
          ShowMessage('Person already exists.');
          Cancel := True;
     end;
end;


Please, download this project example:

Post's attachments

Attachment icon Check duplicate.zip 4.86 kb, 1678 downloads since 2014-10-17 

Dmitry.

3 (edited by v_pozidis 2014-10-23 16:15:38)

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

Ok with that when I choose a new record.
When the recrod exist but we like to do some changes
what then ?

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

Then you should find this record using search and to do some changes.

Dmitry.

5 (edited by v_pozidis 2014-10-23 17:32:29)

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

When the record need some changes I search it or double click  the table on the record
I do the changes But when pressing The Save Button

'Person already exists.'

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

I checked example from second post in current topic, and don't see this error.
May be you made some changes in your project?

Dmitry.

7 (edited by v_pozidis 2014-10-23 20:24:40)

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

Found the error, my mistake.!!!

8 (edited by v_pozidis 2014-10-24 16:29:06)

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

Can I use this script also for two diggerent tables ?
I mean if a word is similar in the two tables then this shoulb be avoid.

I have tried it changing the code but nothin happend.

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

v_pozidis
Please, send me your project, with description, for which tables you need check.
support@drive-software.com

Dmitry.

10 (edited by v_pozidis 2014-10-25 10:21:22)

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

sent it, !!!!!

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

Please, download project:

Post's attachments

Attachment icon Check dublicate on Linked List.zip 7.3 kb, 696 downloads since 2014-10-25 

Dmitry.

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

It works.!!!!!
Great!!!

Thank's!!!!!!!

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

Something easy for you, which I can not make.. I tried it but I cva not find the solution.
So the problem is when IO have in my database many times the same records how can I have In a combox the record just once.

Example
I have a table with 2 records
Country
City

I have added the city and the countru many times
Greece        Athens
Gremany     Frankfurt
Greece        Thessaloniki
USA               New York
France          Paris
Gremany       Berlin
USA               Boston

us you can see Greece and the USA is more than once, how can I have them only once in a combobox without using the upper script.
I am asking it because I make copy/paste many records form the internet (over 100 recoder the time) and I like to short the combobox when there are the same records

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

v_pozidis
Please, send me your project with link on this post
support@drive-software.com

Dmitry.

15 (edited by v_pozidis 2014-10-27 14:42:18)

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

Sent it by e-mail
or download it from dropbox
https://dl.dropboxusercontent.com/u/31552341/Tala.zip

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

This method works only when using a "New Record" button, it does not work with a "Save Record" button.

I tried to add the Action='SaveRecord' but it might be another string sent when using a "Save Record" button:

..[snip]....if (Action = 'NewRecord') or (Action='SaveRecord') then....[snip]....

Does anyone knows what the string sent is?

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

tcoton wrote:

This method works only when using a "New Record" button, it does not work with a "Save Record" button.

I tried to add the Action='SaveRecord' but it might be another string sent when using a "Save Record" button:

..[snip]....if (Action = 'NewRecord') or (Action='SaveRecord') then....[snip]....

Does anyone knows what the string sent is?

?????

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

pavlenko.vladimir.v wrote:
tcoton wrote:

This method works only when using a "New Record" button, it does not work with a "Save Record" button.

I tried to add the Action='SaveRecord' but it might be another string sent when using a "Save Record" button:

..[snip]....if (Action = 'NewRecord') or (Action='SaveRecord') then....[snip]....

Does anyone knows what the string sent is?

?????

I am using Dmitry example up in the post.

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

tcoton wrote:

I am using Dmitry example up in the post.

Action='SaveRecord'  -  occurs when saving

20 (edited by tcoton 2023-11-09 15:40:49)

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

It actually does not work with the action 'SaveRecord'. I have attached an example of what I am trying to achieve.

I want a very simple way of checking duplicates in a handful of tables in the same way as the example.

Post's attachments

Attachment icon ChckDuplicateSaveRecord.zip 337.64 kb, 38 downloads since 2023-11-09 

21 (edited by pavlenko.vladimir.v 2023-11-09 16:01:42)

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

tcoton wrote:

It actually does not work with the action 'SaveRecord'. I have attached an example of what I am trying to achieve.

I want a very simple way of checking duplicates in a handful of tables in the same way as the example.

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
begin
  if (SQLExecute ('SELECT COUNT(id) FROM HardWDType WHERE Kind="'+Form1.Edit1.Text+'"') > 0) then
  begin
    MessageDlg('This Kind is already in use: ' +Form1.Edit1.Text, mtError, mbOk, 0); // Displays a red 'X', header is: Error
    Cancel := True;
  end;
end;
Post's attachments

Attachment icon ChckDuplicateSaveRecord.rar 3.52 kb, 53 downloads since 2023-11-09 

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

Thanks pavlenko.vladimir.v, that looks so simple, I feel dumb.

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

I transformed your solution into a function as it is easier when used repeatedly. It works perfectly when using a form that was not called by a "New Record" button.

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

And the usage is:

procedure Form_ButtonSave_OnClick (Sender: string; var Cancel: boolean);
begin

   if DupliChk('Table','Column',Form.EditBox.Text) then
     begin   //replace object by self-explanatory text
          MessageDlg('This object already exists:  ' +Form.EditBox.Text +#13+'Entry cancelled', mtError, mbOk, 0); // Displays a red 'X', header is: Error
          Cancel := True;
          Form.EditBox.Clear;  
     end;
end;

24 (edited by pavlenko.vladimir.v 2023-11-10 15:17:46)

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

tcoton wrote:

I transformed your solution into a function as it is easier when used repeatedly. It works perfectly when using a form that was not called by a "New Record" button.

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

If I understand correctly, then you instantly changed the answer (False)
 
Do it in this way

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

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

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