SQL Update statement for delphi7 and paradox

Hi experts,
In the sql statement attached, I would also like to update another field called "Part_of_no_of_Derived" in the point table in the same statement. I would actually like to increment this field by '1'. "Part_of_no_of_Derived" is an integer field in the point table.
I have tried different ways, one of the error I get during compile is "left side cannot be assigned to".
Would appreciate a quick solution. Thanks
function TAddDerivedPointsForm.ChangeAidRpt(aid: string): boolean;
begin
  with DMForm.Q_blank do
  begin
    SQL.Clear;
    SQL.Add('UPDATE point set Report = 4 WHERE ( P$RTUID = :vRtu ) AND (AID = :vaid )');
    ParamByName('vRtu').AsInteger := DMForm.T_RTURTUID.AsInteger;
    ParamByName('vaid').AsString := aid;
    try
      ExecSQL;
      result := TRUE;
      SQL.Clear;
      Close;
    except
      result := FALSE;
    end;
  end;
 
end;

Open in new window

QurbanDurraniAsked:
Who is Participating?
 
bokistConnect With a Mentor Commented:
try this way

function TAddDerivedPointsForm.ChangeAidRpt(aid: string): boolean;
begin
  with DMForm.Q_blank do
  begin
    SQL.Clear;
    SQL.Add('UPDATE point set Report = 4, Part_of_no_of_Derived =Part_of_no_of_Derived + 1');
    SQL.Add(' WHERE ( P$RTUID = :vRtu ) AND (AID = :vaid )');
    ParamByName('vRtu').AsInteger := DMForm.T_RTURTUID.AsInteger;
    ParamByName('vaid').AsString := aid;
    try
      ExecSQL;
      result := TRUE;
      SQL.Clear;
      Close;
    except
      result := FALSE;
    end;
  end;
 
end;
0
 
rfwoolfConnect With a Mentor Commented:
You should post this question in the sql zone as well. Also note that if you're using the BDE to access your Paradox tables then the BDE uses LocalSQL which has some limitations on normal SQL -- you can access the LocalSQL help files in the BDE directory, usually located at C:\program files\common files\bde\
.
..in any event the error you are getting is not an SQL error it seems - it seems to be a compile error, change the left side's ".asInteger" and ".asString" to just ".value" and that should compile
0
 
QurbanDurraniAuthor Commented:
rfwoolf,
I changed the satement as attached, still get the "left side cannot be assigned error". The sql I posted with the question works just fine. It errors out when I try to update the second field as shown.

function TAddDerivedPointsForm.ChangeAidRpt(aid: string): boolean;
begin
  with DMForm.Q_blank do
  begin
    SQL.Clear;
    SQL.Add('UPDATE point set Report = 4, Part_of_no_of_Derived = :vNodrv WHERE ( P$RTUID = :vRtu ) AND (AID = :vaid )');
    ParamByName('vNodrv').Value := Inc(DMForm.T_PointPart_of_no_of_Derived.AsInteger);
    ParamByName('vRtu').AsInteger := DMForm.T_RTURTUID.AsInteger;
    ParamByName('vaid').AsString := aid;
    try
      ExecSQL;
      result := TRUE;
      SQL.Clear;
      Close;
    except
      result := FALSE;
    end;
  end;
 
end;

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
rfwoolfCommented:
Try using .value instead of .asinteger or .asstring (for the left side). it seems to be telling you that asinteger and asstring are readonly properties for ParamByName.
0
 
Geert GConnect With a Mentor Oracle dbaCommented:
on which line are you getting "left side cannot be assigned error"

try
  ExecSQL;
  result := TRUE;
except
  result := FALSE;
end;

you don't need to close and update statement,
it normally doesn't return any rows
and actually sql.clear would allready have closed the dataset
any manipulation to the sql.text closes the dataset automatically
0
 
QurbanDurraniAuthor Commented:
Thanks to all for your responses.
The error was on the line:
ParamByName('vNodrv').Value := Inc(DMForm.T_PointPart_of_no_of_Derived.AsInteger);
I changed the line as shown in the attached code and it worked.
Thanks to all, I split the points.

Changed from Line:
ParamByName('vNodrv').Value := Inc(DMForm.T_PointPart_of_no_of_Derived.AsInteger);
 
Changed to Line:
ParamByName('vNodrv').AsInteger := DMForm.T_PointPart_of_no_of_Derived.AsInteger + 1;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.