SQL SINTAX ???
Posted: Sun 29 Sep 2019 21:36
I use MyDAC and a MySql database. My code is working well except for one thing. I have a form in which there is a dbgrid in which I collect the coordinates of a certain component that I can move with the mouse. The coordinates are the variables leftdim and topdim in the code below. You may see the Table and fields Left and Top in the figure. I added the code below which may be useful to some of you. It was working with FireDac but when I changed to MyDac the following occur.
When I move the component with the mouse ( code ControlMouseUp below) the coordinates Left and Top are saved in the memory and appear in the DBgrid. However if I close the code they are saved in the database. The reason is that the part of the code below give an error when UPDATING . It is the classical SQL SINTAX showed in the figure . I tested many-many times and could not identify where the sintax is wrong. I use MYSQL 8 Community version and is updated.
Just for information, If I type the coordinates in the DBgrid and save them they are saved normally in the data base.
Please help me
procedure TForm700x900.ControlMouseUp(Sender: TObject; Button: TMouseButton;
Shift: TShiftState; X, Y: integer);
var
pt1: TPoint; leftDim,topDim,IDnum:integer; ctrl : TWinControl;
begin
with TWinControl(Sender) do
begin
GetCursorPos(PT1);
LabelScreen.Caption := ' X='+IntToStr(pt1.x)+', Y='+IntToStr(pt1.y);
ctrl := FindVCLWindow(PT1) ;
if ctrl <> nil then
begin
Caption := ctrl.Name;
if ctrl is TDBedit then
begin
leftDim:= TDBedit(ctrl).left;
topDim:= TDBedit(ctrl).Top;
dbeNumStr := StringReplace(Caption, 'L_', '',[rfReplaceAll, rfIgnoreCase]);
end;
end;
end;
///////////////////// Here is the error //////
dmsfcd.posicoesDBE.Locate('dbeNum',dbeNumStr, []);
IDnum:= dmsfcd.posicoesDBE.FieldByName('dbeNum').AsInteger; // this is a defined integer
dmsfcd.posicoesDBE.SQL.Clear;
dmsfcd.posicoesDBE.SQL.text:= 'UPDATE posicoesdbe SET Left = :left, Top = :top WHERE ID = :id';
dmsfcd.posicoesDBE.ParamByName('id').Value := IDnum; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('left').Value := leftDim; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('top').Value := topDim; // this is a defined integer
dmsfcd.posicoesDBE.ExecSQL;
////////////////////////////////////
F0D.DBgrid_DBedits.Repaint;
end;
end;
http://www.cognitor.com.br/TableandErrorMessage.png
When I move the component with the mouse ( code ControlMouseUp below) the coordinates Left and Top are saved in the memory and appear in the DBgrid. However if I close the code they are saved in the database. The reason is that the part of the code below give an error when UPDATING . It is the classical SQL SINTAX showed in the figure . I tested many-many times and could not identify where the sintax is wrong. I use MYSQL 8 Community version and is updated.
Just for information, If I type the coordinates in the DBgrid and save them they are saved normally in the data base.
Please help me
procedure TForm700x900.ControlMouseUp(Sender: TObject; Button: TMouseButton;
Shift: TShiftState; X, Y: integer);
var
pt1: TPoint; leftDim,topDim,IDnum:integer; ctrl : TWinControl;
begin
with TWinControl(Sender) do
begin
GetCursorPos(PT1);
LabelScreen.Caption := ' X='+IntToStr(pt1.x)+', Y='+IntToStr(pt1.y);
ctrl := FindVCLWindow(PT1) ;
if ctrl <> nil then
begin
Caption := ctrl.Name;
if ctrl is TDBedit then
begin
leftDim:= TDBedit(ctrl).left;
topDim:= TDBedit(ctrl).Top;
dbeNumStr := StringReplace(Caption, 'L_', '',[rfReplaceAll, rfIgnoreCase]);
end;
end;
end;
///////////////////// Here is the error //////
dmsfcd.posicoesDBE.Locate('dbeNum',dbeNumStr, []);
IDnum:= dmsfcd.posicoesDBE.FieldByName('dbeNum').AsInteger; // this is a defined integer
dmsfcd.posicoesDBE.SQL.Clear;
dmsfcd.posicoesDBE.SQL.text:= 'UPDATE posicoesdbe SET Left = :left, Top = :top WHERE ID = :id';
dmsfcd.posicoesDBE.ParamByName('id').Value := IDnum; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('left').Value := leftDim; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('top').Value := topDim; // this is a defined integer
dmsfcd.posicoesDBE.ExecSQL;
////////////////////////////////////
F0D.DBgrid_DBedits.Repaint;
end;
end;
http://www.cognitor.com.br/TableandErrorMessage.png