update failed, found 2 records.

[jkuiper]

I'm using this code to find a number in a table, increasing it with 1 and save it :

Code: Select all

function TDBBolfuncties.GetNextnum(const ParStrRec : string;
                                   const ParIntMax : integer = 0) : integer;
var CpQuery     : TMyQuery;
    IntNextnum  : integer;
    IntVolgende : integer;
begin
  CpQuery := TMyQuery.Create(nil);
  try
    IntNextnum := 0;
    CpQuery.Connection := FMyConnection;
    CpQuery.SQL.Text := 'SELECT nummer FROM nextnum WHERE rec = :rec';
    CpQuery.Params.ParamByName('rec').AsString := ParStrRec;
    CpQuery.Active := true;
    if CpQuery.RecordCount = 0 then
      IntNextnum := 0
    else
    begin
      //--nummer met 1 verhogen
      IntNextnum := CpQuery.FieldByName('nummer').AsInteger;
      IntVolgende := IntNextnum + 1;
      if ParIntMax > 0 then
        if IntVolgende >= ParIntmax then IntVolgende := 1;
      CpQuery.Edit;
      CpQuery.FieldByName('nummer').AsInteger := IntVolgende;
      CpQuery.post;
    end;
  finally
    CpQuery.Active := false;
    CpQuery.Free;
  end;
  result := IntNextnum;
end;

This works fine, until there's also another field with the same number. Delphi create this error report:

EDatabaseError with message: Update failed found 2 records

The log in DBMonitor gave me this:

5-11-2008 14:14:04 0:0.31 SQL Execute: SELECT nummer FROM nextnum WHERE rec = :rec
:rec(String[13])='autocnrnummer' Complete
5-11-2008 14:14:04 0:0.94 SQL Execute: UPDATE nextnum
SET
nummer = ?
WHERE
nummer = ?

:nummer(SmallInt,IN)=10
:Old_nummer(SmallInt,IN)=9 Complete

I have a workaround:

Code: Select all

CpQuery.SQL.Text := 'SELECT rec, nummer FROM nextnum WHERE rec = :rec';

Is this a bug?[/quote]

I'm using MyDAC 5.70.0.41 in D2007

[Dimon]

You have solved the problem correctly because when you try to update dataset, MyDAC genarates a SQL query based on the existing fields. In your case this is only one "nummer" field. And in this case all table records will be updated if the "nummer" field is equal to the specified value.