CODEWITHSUNDEEP
sqldelphiado
Jerry Dodge
Jerry Dodge

Reputation: 27276

TADOQuery - Edit mode inserts new record rather than editing

I'm puzzled at the behavior of the TADOQuery, let's just call Q. When I use Q.Edit, populate some fields, then Post, it ends up actually inserting a new record.

The code is simple, and reading the ID from an object:

Q.SQL.Text := 'select * from SomeTable where ID = :id';
Q.Parameters.ParamValues['id'] := MyObject.ID;
Q.Open;
try
  Q.Edit;
  try
    Q['SomeField']:= MyObject.SomeField;
  finally
    Q.Post;
  end;
finally
  Q.Close;
end;

To my surprise, rather than updating the intended record, it decided to insert a new record. Stepping through the code, immediately after Q.Edit, the query is actually in Insert mode.

What could I be doing wrong here?

Upvotes: 7

Views: 836

Answers (2)

MartynA
MartynA

Reputation: 30715

I think the comments that this behaviour is documented are off the point. What the docs don't make clear (possibly because the point never occurred to the author) is that this behaviour is not guaranteed to be deterministic.

The innards of TDataSet.Edit have scarcely changed in decades. Here is the Seattle version:

procedure TDataSet.Edit;
begin
  if not (State in [dsEdit, dsInsert]) then
    if FRecordCount = 0 then Insert else
    begin
      CheckBrowseMode;
      CheckCanModify;
      DoBeforeEdit;
      CheckParentState;
      CheckOperation(InternalEdit, FOnEditError);
      GetCalcFields(ActiveBuffer);
      SetState(dsEdit);
      DataEvent(deRecordChange, 0);
      DoAfterEdit;
    end;
end;

Now, notice that the if .. then .. is predicated on the value of FRecordCount, which at various points in the TDataSet code is forced to have a given assumed value (variously 1, 0 or something else) by code such as in SetBufferCount and that behaviour isn't documented at all. So on reflection I think Jerry was probably right to expect that attempting to edit a non-existent record should be treated as an error condition, and not be fudged around by silently calling Insert whether or not it is documented.

Upvotes: 5

Jerry Dodge
Jerry Dodge

Reputation: 27276

I'm posting both a question and an answer, because the cause of the problem was totally unexpected behavior, and surely someone else had the same bewildering thing happen.

This happens in the event that the dataset you're trying to edit doesn't have any records. Personally, I would think it should produce an exception that you can't edit when there's no records. But the TADOQuery decides to append a new record instead.

The very root cause of this issue was that the object where I supplied the ID actually had a value of 0, and therefore since there's no record in the database with ID 0, it returned nothing.

Upvotes: 0

Related Questions