TADOQuery - Edit mode inserts new record rather than editing

Question

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?

It's the natural behavior and it's [documented](http://docwiki.embarcadero.com/Libraries/Rio/en/Data.DB.TDataSet.Edit). _"If the dataset is empty, **Edit calls Insert.** Otherwise Edit"_ — Ilyes, May 30 '20 at 18:49
Well, I think it's a useful q&a, because the behaviour, documented or not, isn't intuitive, istm or memorable. — MartynA, May 30 '20 at 18:56
you can always check EOF before trying to edit, I'm not seeing the problem here, this is all documented behavior ... — whosrdaddy, May 30 '20 at 19:52
As MartynA says, documented or not, it's still odd behavior. I spent 20 minutes pounding my head on my desk trying to figure out why it was inserting instead of editing, long before I realized the dataset was empty. Once I discovered it was empty, I quickly found the cause (a function 'Add" which returned my object, which had a parameter for "ID" which although provided, I never assigned it from within that function). — Jerry Dodge, May 30 '20 at 20:51
You've got a flaw in your code, avoid the flaw (by checking EOF) and no headbanging needed... — whosrdaddy, May 30 '20 at 22:01
For the record, I searched SO for related topics, and couldn't find any. So I decided to create this topic for later reference. — Jerry Dodge, May 31 '20 at 02:50

score 5 · Accepted Answer · answered May 30 '20 at 22:23

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.

Indeed. It's like a car whose documentation says "When you press the brake pedal, depending on the speed you're going, the brakes may or may not be applied." Well why not?! — Jerry Dodge, May 31 '20 at 18:07

score 0 · Answer 2 · answered May 30 '20 at 16:42

0

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.

answered May 30 '20 at 16:42

Jerry Dodge

26,858
31
155
327

2

"If the dataset is empty, Edit calls Insert." ([source](http://docwiki.embarcadero.com/Libraries/Rio/en/Data.DB.TDataSet.Edit)) – Olivier May 30 '20 at 16:47
"Edit" is a term that more or less includes "insert", "update" and "delete". If you're concerned with the wrapper then talk SQL directly. – AmigoJack May 30 '20 at 19:25
My `SELECT` is SQL already, and SQL is universal enough - why bothering with ADO and its own logic (`UPDATE` becomes `INSERT`) when I could directly use SQL also for submitting data, definitly knowing what can happen? OP wouldn't have been surprised then (unbound to reading documentation or not). – AmigoJack May 30 '20 at 20:06
@MartynA I think he refers to calling explicit update or insert statements, rather than using edit/append/post. – Jerry Dodge May 31 '20 at 02:53

TADOQuery - Edit mode inserts new record rather than editing

2 Answers2