Lock FDTable

[limelect 47]

I have TDBEdit.

I entered a number and would like to know BEFORE POST

if the number exists in the database and cancels the post.

If OK (number does not exist ) the POST  is executed Now it gives a LOCK condition.

What I tried

 

procedure TForm1.FDTable1BeforePost(DataSet: TDataSet);
begin

 Form1.FDQuery1.SQL.Clear;
  Form1.FDQuery1.SQL.add('Select *');
  Form1.FDQuery1.SQL.add('from WeighingCows');
  Form1.FdQuery1.SQL.add('WHERE Cownumber = :Cownumber');
  Form1.FDQuery1.ParamByName('Cownumber').AsString := Drivers.DBEdit1.Text;
  Form1.FDQuery1.Open;
  if Form1.FDQuery1.RecordCount > 0 then
  begin
   MessageDlg('This number exist', mtError, [mbOK], 0);
  FDTable1.Cancel;<<<<<<<<<<< if execute then OK 
  end;
  Form1.FDQuery1.Close;
 end;
 I got a LOCK condition.

It seems I am not allowed to use SQL and table at the same time.

 

I also tried  FDTable1.CachedUpdates is true but then

Form1.FDTable1.ApplyUpdates() ; gave LOCK too.

Any help?

 

P.S I tried that too

//Form1.FDQuery1.Connection:=nil;
//Form1.FDTable1.ApplyUpdates() ;
//Form1.FDQuery1.Connection:=Form1.FDConnection1;
 

 

 

Edited September 8, 2023 by limelect

[Serge_G 85]

You don't gave us the SGBD used. 

Code can be reduced, without an FDQuery using FDConnection

 

 var x:=FDConnection1.ExecSQLScalar('SELECT 1 FROM WeighingCows WHERE Cownumber = :C', Drivers.DBEdit1.Text);

 if varisnull(x) then begin
   MessageDlg('This number exist', mtError, [mbOK], 0);
  FDTable1.Cancel;<<<<<<<<<<< if execute then OK 
  end;

 

[Fr0sT.Brutal 897]

This method is prone to race condition. Why not set the Cownumber as unique field and let DB server solve the collision?

[limelect 47]

Thanks, i will try both suggestions

[limelect 47]

@fr0st

 

The first suggestion did not help lock.

The unique suggestion seems to work but

catching the error seems not to stop the window message;

For a moment it enters data into DBGRID until I cancel

1. I do not want the Windows error messages only mine

2. If possible not create the record but that is not important

    as the cancel will delete it.

#1 is the most important.

 

procedure TForm1.FDTable1Error(ASender, AInitiator: TObject; var AException:
    Exception);
begin
MessageDlg('err1', mtWarning, [mbOK], 0);
FDTable1.Cancel;
end;

 

 

procedure TForm1.FDTable1PostError(DataSet: TDataSet; E: EDatabaseError; var
    Action: TDataAction);
begin
  MessageDlg('err2', mtWarning, [mbOK], 0);
FDTable1.Cancel;

end;
 

 

[Fr0sT.Brutal 897]

Probably you have to set Action to something?

[limelect 47]

which means?

[limelect 47]

I add

procedure TForm1.FDTable1PostError(DataSet: TDataSet; E: EDatabaseError; var
    Action: TDataAction);
begin
  Action:=daAbort;
end;
 

still 1 window messeg 

[limelect 47]

It seems to be OK to running exe NOT in IDE

[limelect 47]

Thanks

[limelect 47]

It works nicely