delphifirebirdfirebird2.5delphi-xe8dbexpress

How to fix 'Unable to find record. No key specified'?

I'am using a firebird 2.5 server to write in a Database file(BD.fbd). My delphi XE8 project has a Data module(DMDados) with:

My database file has this table:

The table has these fields (all integer):

None of those fields are primary keys because I will have repeated values in some cases. The connection with the file is ok. The client data set is open when I run the code. The TSQLQUery2 (QueryConsulta) is open when needed.

My code, when triggered by a button, has to delete all tables' records (if exist) then full the table with integer numbers created by a LOOP. In the first try the code just work fine, but when I press the button the second time i get the error 'Unable to find record. No key specified' then when I check the records the table is empty.

I tried to change the ProviderFlags of my query but this make no difference. I checked the field names, the table name or some SQL text error but find nothing. My suspect is that when my code delete the records the old values stay in memory then when try apply updates with the new values the database use the old values to find the new record's place therefore causing this error. 

    procedure monta_portico ();
    var
    I,K,L,M, : integer;
    begin
    with DMDados do
      begin
        QUeryCOnsulta.SQL.Text := 'DELETE FROM PORTICO_INICIAL;';
        QueryConsulta.ExecSQL();
        K := 1;
        for I := 1 to 10 do 
          begin
          L := I*100;
          for M := 1 to 3 do
            begin
              cdsBDPortico_Inicial.Insert;
              cdsBDPortico_Inicial.FieldbyName('NPORTICO').AsInteger := 
                M+L;
              cdsBDPortico_Inicial.FieldbyName('ELEMENTO').AsInteger := M;
              cdsBDPortico_Inicial.ApplyUpdates(0);
              K := K +1;
            end;
          end;
      end;
    end;

I want that every time I use the code above it first delete all records in the table then fill it again with the loop. When I use the code for the first time it do what I want but in the second time it just delete the records and can not fill the table with the values.

Solution

  • Update I've added some example code below. Also, when I wrote the original version of this answer, I'd forgotten that one of the TDataSetProvider Options is poAllowMultiRecordUpdates, but I'm not sure that's involved in your problem.

    The error message Unable to find record. No key specified is generated by the DataSetProvider, so isn't directly connected to your 

    QUeryCOnsulta.SQL.Text := 'DELETE FROM PORTICO_INICIAL;'

    because that bypasses the DataSetProvider. The error is coming from an failed attempt to ApplyUpdates on the CDS. Try changing your call to it to

    Assert(cdsBDPortico_Inicial.ApplyUpdates(0) = 0);

    That will show you when the error occurs because the return result of ApplyUpdates gives the number of errors that occurred when calling it.

    You say

    will have repeated values in some cases

    If that's true when the problem occurs, it's because you are hitting a fundamental limitation in the way a DataSetProvider works. To apply the updates on the source dataset, it has to generate SQL to send back to the source dataset (TSqlQuery1) which uniquely identifies the row to update in the source data, which is impossible if the source dataset contains duplicated rows.

    Basically, you need to re-think your code so that the source dataset rows are all unique. Once you've done that, setting the DSP's UpdateMode to upWhereAll should avoid the problem. It would be best for the source dataset to have a primary key, of course.

    A quick work-around would be to use CDS.Locate in the loop where you insert the records, to see if it can locate an already-existing record with the values you're about to add.

    Btw, sorry for raising the point about the ProviderFlags. It's irrelevant if there are duplicated rows, because whatever they are set to, the DSP will still fail to update a single record.

    In case it helps, here is some code which might help populating your table in a way which avoids getting duplicates. It only populates the first two columns, as in the code you show in your q.

    function RowExists(ADataset : TDataSet; FieldNames : String; Values : Variant) : Boolean;
begin
  Result := ADataSet.Locate(FieldNames, Values, []);
end;

procedure TForm1.PopulateTable;
var
  Int1,
  Int2,
  Int3 : Integer;
  i : Integer;
  RowData : Variant;
begin
  CDS1.IndexFieldNames := 'Int1;Int2';
  for i := 1 to 100 do begin
    Int1 := Round(Random(100));
    Int2 := Round(Random(100));
    RowData := VarArrayOf([Int1, Int2]);
    if not RowExists(CDS1, 'Int1;Int2', RowData) then
      CDS1.InsertRecord([Int1, Int2]);
  end;
  CDS1.First;
  Assert(CDS1.ApplyUpdates(0) = 0);
end;