clientdataset.refresh retrieves old data

Question

I'm using Delphi XE3 with a MySQL database. I have a SQLconnection – SQLquery – DataSetProvider – ClientDataSet arrangement. When I ApplyUpdates of a modification to the CDS, the change is affected in the database correctly but when I Refresh the CDS, the "old" values are replaced. The code I'm using is:

 CDS.IndexFieldNames:='pop0';
 CDS.first;
 for W := 1 to 5 do begin  //   display the original data
  memo2.lines.add (IntToStr(CDS['pop0']));  CDS.Next;
 end;
 memo2.lines.add('');

  CDS.first;
  CDS.Edit;                   //    modify data
  CDS['pop0']:= 3004;
  CDS.Post;
  CDS.first;
 for W := 1 to 5 do begin   //   display the modified data
  memo2.lines.add (IntToStr(CDS['pop0']));  CDS.Next;
 end;
 memo2.lines.add('');

 CDS.ApplyUpdates(0) ;
 messagedlg('Check database',mtInformation,[mbOK],0);
 CDS.refresh;

 CDS.first;
 for W := 1 to 5 do begin   //   display the updated data
  memo2.lines.add (IntToStr(CDS['pop0']));  CDS.Next;
 end;

the output in the memo is as follows:

I have tried to close and open the CDS as an alternative for refresh but I got the same result. Any ideas why this is happening?

I'd expect the first row to become '3004' for the modified data. Does your output really reflect your test case? — Sertac Akyuz, Aug 16 '13 at 16:59
You should check the result of ApplyUpdates to make sure your changes are actually written to the database. — Uwe Raabe, Aug 16 '13 at 17:25
Actually the change is visible locally after cds.Post immediately. It seems that ApplyUpdates doesn't succeed. That is why you should check the result. — Uwe Raabe, Aug 16 '13 at 18:40
@Sertac, the output reflects the test case because the data is indexed by the pop0 field. @Uwe, ApplyUpdates returns 0. I know that ApplyUpdates succeeded because I checked the database externally. @Marcus, the query is `select * from output`. — Petrus, Aug 16 '13 at 18:48
What happens if you try to update a field that's not in the index? — Jan Doggen, Aug 16 '13 at 18:58
Firstly: Would be better to re-write your code using CDS.First; while not CDS.Eof do begin ... CDS.Next; end, rather that a For loop. Secondly, it's generally a bad idea to modify the value of the field a TCDS is indexed upon while you're using that index. Thirdly, try tracing TClientDataSet.InternalRefresh to see whether or not the Sql data really gets refreshed and if not, why not. — MartynA, Aug 16 '13 at 19:53
Would you tell us why to call Refresh? CDS does not require that. Besides, was the SQLQuery open or closed when you opened the CDS? It must be closed so the Provider will Open it, retrieve all the rows and close it back. If the provider finds the dataset already open, it will only navigate along the records, build the data and send it to the CDS. The dataset will be left alone, whitout being closed. — AlexSC, Aug 16 '13 at 20:04
@Alex, you are right. The SQLQuery was open all the time. Closing it solved the problem. — Petrus, Aug 16 '13 at 20:33
@AlexSC: Please post the information you provided about the query being open as an answer. It seems to have solved the problem, and it's useful information for future users who are trying to solve the same problem. :-) — Ken White, Aug 17 '13 at 04:16

score 1 · Accepted Answer · answered Aug 17 '13 at 22:46

Would you tell us why the call to Refresh? TClientDataSet (CDS) usually does not require that.

Besides, was the SQLQuery open or closed when you opened the CDS? It must be closed so the DatasetProvider (DSP) will open it, retrieve all the rows and close it back.

If the DSP finds the dataset already open, it will only navigate along the records, build the record pack (named as Data) and send it to the CDS. The dataset will be left alone, whitout being closed.

clientdataset.refresh retrieves old data

1 Answers1