Delphi 2010 MySQL Update through ClientDataSet Failing

Question

I am using Delphi 2010 and connecting to a MySQL database using dbexpress. The connection works 100% and I am retrieving my data. The problem comes when I am trying to update data through a dbGrid. I am running through all the entries in the table performing checks on them. When calling the ApplyUpdates method, I was getting "Record not found or changed by another user".

Changing the updateMode on the DataSetProvider to upWhereKeyOnly, I am now in the situation where I am receiving "Unable to find record, no key specified". I have tried adding

BasysClientDataSet.FieldByName('idPolicy').ProviderFlags := [pfInUpdate, pfInWhere, pfInKey];

this to the code, but I am getting the same error. I have tried adding the ProviderFlags to the SQLQuery but I am getting no such field "idPolicy"

Answer 1

Try setting the ResolveToDataSet property of the DataSetProvider component to True.

Answer 2

Try to set provider flag pfInKey for all primary key columns just as you did but not in client dataset but source dataset. This used to help me even when I don't understand what the hell are fields doing in client dataset when db express ignores these settings? I would say its a bug.

Answer 3

try creating this procedure and using it in the provider BeforeUpdateRecord event.

procedure SetOriginFlags(Source, Dest: TCustomClientDataSet);
var
  i: Integer;
begin
  for i := 0 to Source.FieldCount - 1 do
  begin
    if Dest.FindField(Source.Fields[i].FieldName) <> nil then
    begin
      Dest.FindField(Source.Fields[i].FieldName).ProviderFlags :=
        Source.Fields[i].ProviderFlags;
      if Dest.FindField(Source.Fields[i].FieldName).ProviderFlags <> [pfHidden] then
        Dest.FindField(Source.Fields[i].FieldName).Origin :=
          Source.Fields[i].FieldName;
    end;
  end;
end;