CODEWITHSUNDEEP
mysqldelphiduplicates
chris
chris

Reputation: 11

Duplicate problem in MySQL with autoincrement id

I have an issue with my database. Table structure is:

Table name: sales sale_id (autoincrement) date (datetime) total (decimal) etc.

I have 2 computers, one is "the server" and the other is "the client", when I Insert in "sales" sometimes the database saves more than 1 record, it's an issue kind of random because one day could be normal just save 1 record as is but other day could save 2 or more duplicates.

My code is:

qry1.SQL.Text := 'SELECT * FROM sales '
               + 'WHERE sale_id = 1';
qry1.Open;
qry1.Insert;
qry1.FieldByName('date').AsDateTime := Date;
qry1.FieldByName('total').AsFloat := total;
qry1.Post;

saleId := qry1.FieldByName('sale_id').AsInteger;
qry1.Close;
// Code to save sale details using saleId.

I'm using Delphi 10.3 + ZeosLib 7.2.6-stable + MySQL 8.0

I opened the ports in the server so I have a direct connection to MySQL, I don't know what could be happening

Hope you can help me

Update---- Thanks for your kind answers,

@nbk Yes, I did it already.

@A Lombardo I used "where" to get just 1 record and then I use the query to insert the new one similar to use TTable but instead of load the hole table I just get one record and I can insert (qry.Insert),

@TheSatinKnight not only I get two records, sometimes I get 3 or more, but makes sense probably the keayboard is not working well and could send "enter" key more than once.

@fpiette, I will do ti right now.

I will keep you posted.

Upvotes: 1

Views: 116

Answers (2)

chris
chris

Reputation: 11

Thanks again for all your kind answers.

At the end the problem was keyboard, It had a problem with "Enter" key, so when you pressed it, it send more than one pulsation so @TheSatinKnigh your approach was correct

@fpiette I created the log file and I figured out as you said the request had been executed twice or more.

I know maybe it is a silly thing for a programmer because I was disabling the button to late, sorry for that

@A Lombardo thanks for you code I like it better than mine I will use it

Upvotes: 0

A Lombardo
A Lombardo

Reputation: 774

There are better ways to accomplish an insert than to open a TZTable and inserting on that open table.

As another approach, drop 2 TZQuery (NOT TZTable) on your form (which I'll assume is TForm1 - change as appropriate).

Assuming the name is ZQuery1 and ZQuery2.

Set its connection property the same as your TZTable, so it uses the same connector.

Set ZQuery1.SQL property to 'Insert into sales (date, total) values (:pdate, :ptotal)' //(w/o quotes) Set ZQuery2.SQL property to 'select last_insert_id() as iddb'

now add the Function below to your form's Private delcaration

TForm1 = class(TForm)
  ZQuery1: TZQuery;  //added when dropped on form
  ZQuery2: TZQuery;
private
  { Private declarations }
  function AddNewSale(SaleDate: TDateTime; Total: Double): Integer;  //add this line
public
  { Public declarations }
end;

and then add the following code to your form's methods

var
  Form1: TForm1;

implementation
{$R *.dfm}

function TForm1.AddNewSale(SaleDate: TDateTime; Total: Double): Integer;
begin
  ZQuery1.ParamByName('pdate').AsDateTime := SaleDate;
  ZQuery1.ParamByName('ptotal').AsFloat := Total;
  ZQuery1.ExecSQL; //*Execute* the Insert - Only "open" SQL that returns a result set
  //now the record has been added to your DB
  if ZQuery1.RowsAffected = 1 then //check to ensure it was inserted
  begin
    ZQuery2.Open;
    try
      Result := ZQuery2.FieldByName('iddb').AsInteger;
    finally
      ZQuery2.Close;
    end;
  end
  else
    result := -1;//indicate error by returning negative value
end;

now in the place you want to insert the record, simply call this function:

var
  ReturnValue: Integer;
begin
  ReturnValue := AddNewSale(Date, total);
  if ReturnValue < 0 then
    //error happened
  else
  begin
    //Everything worked
  end;
end;

Upvotes: 1

Related Questions