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?
My Visual Database → Script → 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?
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:
Ok with that when I choose a new record.
When the recrod exist but we like to do some changes
what then ?
Then you should find this record using search and to do some changes.
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.'
I checked example from second post in current topic, and don't see this error.
May be you made some changes in your project?
Found the error, my mistake.!!!
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.
v_pozidis
Please, send me your project, with description, for which tables you need check.
support@drive-software.com
sent it, !!!!!
Please, download project:
It works.!!!!!
Great!!!
Thank's!!!!!!!
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
v_pozidis
Please, send me your project with link on this post
support@drive-software.com
Sent it by e-mail
or download it from dropbox
https://dl.dropboxusercontent.com/u/31552341/Tala.zip
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?
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 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.
I am using Dmitry example up in the post.
Action='SaveRecord' - occurs when saving
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.
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;
Thanks pavlenko.vladimir.v, that looks so simple, I feel dumb.
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;
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;
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!!!
My Visual Database → Script → avoid the same input if it exist in table ?
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi