Topic: [Query] Check for duplicate for beginners

Hello everyone,


Today, I would like to share a tip with beginners on how to make sure you are not inserting duplicate terms in your database. This is directly in your procedure, this is not a function you can call again and again, you'll have to re-write it each time for each procedure.


If you're looking for some function to do this, you can have a look at this thread by Tcoton : Function CheckDuplicate


It's very simple and supported by SQLite AND MySql.


Imagine you have a word in a Edit, for example a name, let's say "Mathias".


You want to add this customer to your database but there are so many off them already, that it's a hassle to review them all before saving.


You're going, before insertion in database, to query the table and see if there is already a customer named "Mathias", right ?


But what if the name is spelled "mathias", or "mathiaS"... different cases ?


Well, here is how to query the table to avoid duplicates :


var
     new_name : String;
     existing_name_count : Integer
begin
     //What is the new name you want to save ?
     new_name := Form1.Edit1.Text;
     //Let's see if this name is alredy present by counting it's occurence
     existing_name_count := SQLExecute('SELECT COUNT(name) FROM customer WHERE upper(name)=upper("'+new_name+'")');
     if existing_name_count <> 0 then
          begin
               This is a duplicate
          end
     else if existing_name_count = 0 then
          begin
               This is not a duplicate, we can save it
          end;
end;

Did you notice the upper() in the query ? This way, you are comparing strings form the database and from your Form1.Edit1.Text, with the same case type.


You could also use lower() if you wish.


That's all for now, hope it will be useful.


Cheers


Mathias


PS : there might be other solutions, this is just the one I use.

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

2 (edited by Роман 2015-12-03 00:57:08)

Re: [Query] Check for duplicate for beginners

Спасибо Вам mathmathou!

Re: [Query] Check for duplicate for beginners

Thank You Very much mathmathou.

Result Display in Edit1 if i  will to Show is Popup  how to edit code ?

My Visual Database : I Love You
Easy For Beginner Student For Me

Re: [Query] Check for duplicate for beginners

Hello prahousefamily,


if you want to display a simple popup message you can use the ShowMessage command.


It's used like this :


if existing_name_count <> 0 then
          begin
               ShowMessage('This entry is a duplicate !');
          end

It will give you a popup with your message and an OK button.


You can include variables in your message. for example :


var
     new_name : String;
     existing_name_count : Integer
begin
     //What is the new name you want to save ?
     new_name := Form1.Edit1.Text;
     //Let's see if this name is alredy present by counting it's occurence
     existing_name_count := SQLExecute('SELECT COUNT(name) FROM customer WHERE upper(name)=upper("'+new_name+'")');
     if existing_name_count <> 0 then
          begin
               ShowMessage('The name '+new_name+' is already in database');
          end
     else if existing_name_count = 0 then
          begin
               SQLExecute('INSERT INTO customers(name) VALUES("'+new_name+'")');
          end;
end;

Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: [Query] Check for duplicate for beginners

i guess  you can add this to a button save procedure and add the Cancel := True;  to the code right?

Re: [Query] Check for duplicate for beginners

Hello VascoMorais,


Sure you can put that on On_Click event  !!


Cancel := True;

would go here


if existing_name_count <> 0 then
          begin
               Cancel := True;
               ShowMessage('The name '+new_name+' is already in database');
          end
I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: [Query] Check for duplicate for beginners

Hey Mathmathou, i implemented the code on every textbox i wanted to be checked:

Phone numbers, Emails, and even Brand Names on the database, the problem is:

Everytime i insert an item on the brand name with ' " ' char, i get a bug.
For example

MBP 13" 2010 (refering to the macbook pro 13")
I changed that to MBP13pol and i didn't had any more problems, until i wrote the follwing email today: 7star@7star.pt And it gave me another bug refering to this piece of code,

Do you know what could this be? in the meanwhile i'll just be careful when insertnig brands, and i'll deactivate the duplicate search for emails (as probably phone numbers would do just fine)

Re: [Query] Check for duplicate for beginners

Hello VascoMorais

I encounter a similar problem
When I want to enter an Irish author (ie O'BRIEN), MVD refuses to register it.
With Delphi, when a word contains an apostrophe, programming wants to be the double (ie O''BRIEN).
It is the eternal problem of the key characters in Delphi.
As I have not yet found a solution, I forget the apostrophe and I write OBRIEN

JB

Re: [Query] Check for duplicate for beginners

Hello partners smile


Unfortunately, we have the same problem.


One solution would be to create a function that trims automaticly "forbidden" characters  or substitute them with "authorized" ones before saving strings in database.


We can use UpperCase command in scripts instead of upper in SQLite, but it's obsolete, replaced with the AnsiUpperCase in latest Delphi version that supports more characters.


I don't know which one Dmitry implemented behind his UpperCase script command.

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: [Query] Check for duplicate for beginners

If you need insert text to database from TextBox component, you should use property sqlValue, example:

new_name := Form1.Edit1.sqlValue;

sqlValue return text value ready to insert in SQL query.

Dmitry.

Re: [Query] Check for duplicate for beginners

Woopsy, I'll try that right away.


Thanks for the tip Dmitry



Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: [Query] Check for duplicate for beginners

One question though... is that what MVD does when we link a textbox to a database field and save with MVD without using personnal script ?

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: [Query] Check for duplicate for beginners

mathmathou
Almost.

Dmitry.

14 (edited by jrga 2018-12-02 15:47:57)

Re: [Query] Check for duplicate for beginners

KEY DUPLICATE

Mathias, obrigado pela informação. Eu usei a função no MVD 2.8 e funciona. Entretanto, ela não funciona quando estou inserindo um novo registro num formulário que tem um tablegrid (registros filhos). Quando tento gravar o registro, após a inserção de registros no tablegrid, o MVD dá a mensagem que a chave já existe. Eu entendi que o MVD primeiro grava o registro pai antes de inserir o registro filho no tablegrid....

Você tem alguma sugestão para esse caso? Obrigado.

Eu usei o trigger abaixo, que encontrei na internet e funcionou. Achei o de insert na Internet e criei o de update:

CREATE TRIGGER [nf_duplicada_update] BEFORE UPDATE ON [Vendas] WHEN [OLD].[Nota_Fiscal] <> [NEW].[Nota_Fiscal]
BEGIN
  SELECT RAISE(ABORT, "Nota Fiscal já existe")
  WHERE  EXISTS (SELECT *
         FROM   [Vendas]
         WHERE  [Nota_Fiscal] = [NEW].[Nota_Fiscal]);
END;

CREATE TRIGGER [nf_duplicada_insert] BEFORE INSERT ON [Vendas]
BEGIN
  SELECT RAISE(ABORT, "Nota Fiscal já existe")
  WHERE  EXISTS (SELECT C *
         FROM   [Vendas]
         WHERE  [Nota_Fiscal] = [NEW].[Nota_Fiscal]);
END;