Cannot change field on join query

[carlonarcisi]

hi !
i'm using two table in MS ACCESS, Unidac last version, Delphi Xe2.
I extrapolated this piece of code.
In short, I do a join query and I can not change values​​.
hoping to make you spend less time possible, I put the project online
http://70.85.129.85/tw/XXXX.zip

the code

Code: Select all

  AdoFasi.SQL.Clear;
  AdoFasi.SQL.Add('SELECT Prestazioni.IdPaziente, ');
  AdoFasi.SQL.Add('Prestazioni.Accettata,');
  AdoFasi.SQL.Add('Prestazioni.Famiglia,');
  AdoFasi.SQL.Add('Prestazioni.Prestazione,');
  AdoFasi.SQL.Add('Prestazioni.Storico,');
  AdoFasi.SQL.Add('Prestazioni.Dente,');
  AdoFasi.SQL.Add('Prestazioni.Stato as StatoPrestazione,');
  AdoFasi.SQL.Add('Prestazioni.Fasi as NumeroFasi,');
  AdoFasi.SQL.Add('Prestazioni.Abbreviazione as ABBR,');

  AdoFasi.SQL.Add(' Fasi.*');
  AdoFasi.SQL.Add
    ('FROM Prestazioni INNER JOIN Fasi ON Prestazioni.idprestazione = Fasi.IdPrestazione');
  AdoFasi.SQL.Add('Where (Prestazioni.IdPaziente = ' + QuotedStr('1') + ')');
  AdoFasi.SQL.Add('And (Prestazioni.Accettata = TRUE)');
  AdoFasi.SQL.Add('ORDER BY Prestazioni.Prestazione, Fasi.DescrizioneFase');

  AdoFasi.Open;

  Assert(AdoFasi.Locate('IdFase',
    '{22DC64EB-71CE-469D-A62B-21A6B6E1E85C}', []));

  AdoFasi.Edit;
  try
    AdoFasi.FieldByName('IdStory').AsString := 'AAAAAAAAA';
    AdoFasi.Post;
  except
    on E: Exception do
    begin
      messageDlg(E.Message, mtError, [mbOk], 0);
    end;
  end;

 // Field 'IdStory' cannot be modified.
  // how you suggest in http://forums.devart.com/viewtopic.php?t=22631

  AdoFasi.Close;
  AdoFasi.Options.SetFieldsReadOnly := FALSE;
  AdoFasi.Open;

  Assert(AdoFasi.Locate('IdFase',
    '{22DC64EB-71CE-469D-A62B-21A6B6E1E85C}', []));

  AdoFasi.Edit;
  AdoFasi.FieldByName('IdStory').AsString := 'AAAAAAAAA';
  AdoFasi.Post;

  AdoFasi.Close;
  AdoFasi.Open;
  Assert(AdoFasi.Locate('IdFase',
    '{22DC64EB-71CE-469D-A62B-21A6B6E1E85C}', []));
  Assert(AdoFasi.FieldByName('IdStory').AsString = 'AAAAAAAAA');

  // assertion FAILURE !

[AlexP]

hello,

For solving this problem, you should explicitly specify the exact used table, in which you want to edit data.
For this, you should specify the name of this table in the UpdatingTable property. i.e.:

Code: Select all

...
  AdoFasi.SQL.Add('ORDER BY Prestazioni.Prestazione, Fasi.DescrizioneFase');
  AdoFasi.UpdatingTable := 'Fasi';
  AdoFasi.Open;
....

In this case, there will be no need to set the SetFieldsReadOnly property to False, and the data will be edited correctly

[carlonarcisi]

it works thanks!
I think it is a good approach to query join.
"When I was little," I used to delete the rows of records to the wrong table due to join!
thanks