How can I avoid storing duplicates in the database in this code?

Question

I have a user registration process which stores user info to my database using MYDAC components. Currently it allow duplicate users, which is not my intention. My code is below, but I don't know where the problem is.

procedure TForm1.Button1Click(Sender: TObject);
begin    
  if (edit1.Text <> '') and (edit2.Text <> '') and (edit3.Text <> '') and 
    (edit4.Text <> '') then
  begin
    MyQuery1.Close;
    MyQuery1.SQL.Text := 'select * from uyeler '+
                         'where nick=:0 and mail=:0 and site=:0';

    MyQuery1.Params[0].AsString:=edit1.text;
    MyQuery1.Params[0].AsString:=edit2.text;
    MyQuery1.Params[0].AsString:=edit3.text;

    MyQuery1.open;

    if MyQuery1.RecordCount = 0 then
      MessageDlg('The same information! Try again.', mtError, [mbOK], 0)
    else
      MyQuery1.Close;

    MyQuery1.SQL.Text := 'INSERT INTO uyeler (nick, mail, site, sifre) VALUES '+
                                            '(:nick, :mail, :site, :sifre)';

    MyQuery1.ParamByName('nick').AsString := Edit1.text;
    MyQuery1.ParamByName('mail').AsString := Edit2.text;
    MyQuery1.ParamByName('site').AsString := Edit3.text;
    MyQuery1.ParamByName('sifre').AsString := Edit4.text;
    MyQuery1.Execute;

    Button1.Enabled := False;
    MessageDlg('Mission complate!', mtInformation, [mbOK], 0);

    Edit1.Clear;
    Edit2.Clear;
    Edit3.clear;
    Edit4.Clear;

    PageControl2.Visible := False;
    PageControl1.Visible := True;
  end
  else
  begin
    MessageDlg('Information is missing! Try again.', mtWarning,[mbOK],0);    
  end;
end;

How can I prevent signing up with the same? What should I do in this case?

Answer 1

IMO the answer posted by @Ken White should work fine, but since you are finding troubles. I suggest you to try with this code. Its just the difference in the executing the queries.

I am considering the field datatypes to be Char or VarChar, hence " " while entering the data values

procedure TForm1.Button1Click(Sender: TObject);
begin
  Button1.Enabled:=false;

  if (edit1.Text <> '') and (edit2.Text <> '') and 
     (edit3.Text <> '') and (edit4.Text <> '') then
  begin
    MyQuery1.SQL.Clear;
    MyQuery1.SQL.Add(' select* from uyeler where nick="'+edit1.text+'"' +
                      'and mail="'+edit2.text+'" and site="'+edit3.text+'"');
    MyQuery1.Execute;

    if not MyQuery1.IsEmpty then   //--- can also use MyQuery1.RecordCount >0 
      MessageDlg('The same information! Try again.', mtError,[mbOK],0)
    else
    begin            //--- no duplicates present 
      MyQuery1.SQL.Clear;
      MyQuery1.SQL.Add(' INSERT INTO uyeler (nick, mail, site, sifre) VALUES '+
                            '("'+edit1.text+'", "'+edit2.text+'","'+edit3.text+'", "'+edit4.text+'")');

     try
        MyQuery1.Execute;
      finally
        MyQuery1.SQL.Clear;
      end;

     MessageDlg('Mission complate!', mtInformation,[mbOK],0);

     Edit1.Clear;
     Edit2.Clear;
     Edit3.clear;
     Edit4.Clear;

     PageControl2.Visible:=false;
     PageControl1.Visible:=true;
    end;                        
  end;                 
  else
   MessageDlg('Information is missing! Try again.', mtWarning,[mbOK],0);
end;

Answer 2

You're checking the wrong result. You need to change your test to

if MyQuery1.RecordCount > 0 then // At least one match found already

Better yet, if MyDac supports it, is to use

if not MyQuery1.IsEmpty then  // row already exists.

Actually, you have more issues than that, though. You have a mismatched begin and end block, because right now you're always running the insert part of the method. As @TLama says, you're also using the same pameter multiple times, assigning nick, mail, and site the all the same value. Use named parameters instead (shown below in both the SQL and the parameter assignments).

procedure TForm1.Button1Click(Sender: TObject);
var
  UserExists: Boolean;
begin
  Button1.Enabled:=false;

  if (edit1.Text <> '') and (edit2.Text <> '') and 
     (edit3.Text <> '') and (edit4.Text <> '') then
  begin
    MyQuery1.Close;
    MyQuery1.SQL.Text :=' select* from uyeler '+
                        'where nick=:nick and mail=:mail and site=:site';

    MyQuery1.ParamByName('nick').AsString:=edit1.text;
    MyQuery1.ParamByName('mail').AsString:=edit2.text;
    MyQuery1.ParamByName('site').AsString:=edit3.text;
    MyQuery1.open;
    try
      UserExists := not MyQuery1.IsEmpty;
    finally
      MyQuery1.Close;
    end;

    if UserExists then
      MessageDlg('The same information! Try again.', mtError,[mbOK],0)
    else
    begin            // <<--- Added begin
      MyQuery1.SQL.Text :=' INSERT INTO uyeler (nick, mail, site, sifre) VALUES '+
                                              '(:nick, :mail, :site, :sifre)';

      MyQuery1.ParamByName('nick').AsString        := Edit1.text;
      MyQuery1.ParamByName('mail').AsString        := Edit2.text;
      MyQuery1.ParamByName('site').AsString        := Edit3.text;
      MyQuery1.ParamByName('sifre').AsString        := Edit4.text;
      try
        MyQuery1.Execute;
      finally
        MyQuery1.Close;
      end;
    end;                      // <------------ Moved end from below where marked

    MessageDlg('Mission complate!', mtInformation,[mbOK],0);

    Edit1.Clear;
    Edit2.Clear;
    Edit3.clear;
    Edit4.Clear;

    PageControl2.Visible:=false;
    PageControl1.Visible:=true;
  end            // <------------- removed extra end caused by addition above
  else
   MessageDlg('Information is missing! Try again.', mtWarning,[mbOK],0);
end;

Answer 3

I would typically use unique indexes on the underlying MySQL table to enforce this.