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?
- DriveSoft
- Administrator
- Offline
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
Check duplicate.zip 4.86 kb, 1696 downloads since 2014-10-17
Dmitry.
3 2014-10-23 16:15:23 (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 ?
- DriveSoft
- Administrator
- Offline
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 2014-10-23 17:21:46 (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.'
- DriveSoft
- Administrator
- Offline
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 2014-10-23 20:19:45 (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 2014-10-24 16:20:13 (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.
- DriveSoft
- Administrator
- Offline
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 2014-10-25 09:25:53 (edited by v_pozidis 2014-10-25 10:21:22)
Re: avoid the same input if it exist in table ?
sent it, !!!!!
- DriveSoft
- Administrator
- Offline
Re: avoid the same input if it exist in table ?
Please, download project:
Post's attachments
Check dublicate on Linked List.zip 7.3 kb, 711 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
- DriveSoft
- Administrator
- Offline
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 2014-10-27 13:58:40 (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
- tcoton
- Member
- Offline
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?
- pavlenko.vladimir.v
- Member
- Offline
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?
?????
- tcoton
- Member
- Offline
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.
- pavlenko.vladimir.v
- Member
- Offline
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 2023-11-09 15:40:28 (edited by tcoton 2023-11-09 15:40:49)
- tcoton
- Member
- Offline
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
ChckDuplicateSaveRecord.zip 337.64 kb, 47 downloads since 2023-11-09
21 2023-11-09 16:01:34 (edited by pavlenko.vladimir.v 2023-11-09 16:01:42)
- pavlenko.vladimir.v
- Member
- Offline
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
ChckDuplicateSaveRecord.rar 3.52 kb, 64 downloads since 2023-11-09
- tcoton
- Member
- Offline
Re: avoid the same input if it exist in table ?
Thanks pavlenko.vladimir.v, that looks so simple, I feel dumb.
- tcoton
- Member
- Offline
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 2023-11-10 15:17:16 (edited by pavlenko.vladimir.v 2023-11-10 15:17:46)
- pavlenko.vladimir.v
- Member
- Offline
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;- tcoton
- Member
- Offline
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!!! 