tmstable edit and post update failed 0 record found

[kei]

Consider the following code:
tmstable.edit;
tmstable.fieldbyname('STOCK_HAND').asfloat := tmstable.fieldbyname('STOCK_HAND').asfloat - 1;
tmstable.post;
Sometimes when the post is called the error "update failed 0 record found" is returned. We believe this is because the record has been changed by another user between calling edit and post.

This raises the issue of locking records. Why doesn't tmstable lock the current record when edit is called? How can we make it lock the record?

We are committed to using the tmstable component and cannot change over to using queries without spending about 18 months redeveloping our application.

What can we do? The solution is needed within the next 24 minutes. (Only kidding, but it is very urgent.)

[GEswin]

Add a unique key to the table (auto_increment) and your problem will be solved. When you call post, it will update the row that has the same unique number, instead of looking for the row that has/had the same values. It will solve your problem, and also speed up a lot things.

[Ikar]

You can use SELECT ... WITH (UPDLOCK) statement to lock row. Please read details in MSDN.
Do you have primary key field in your table?

[kei]

Thanks

[fhartzler]

While insuring that a unique field is included in the "select" statement prevents the "0 records found error", this does not solve the "locking" issue.

I created a test app with an intention pause between an edit and post. I tried this test with and without transactions, and with the connection.isolationlevel set to every available option, one option at a time.

Test Procedure:
I execute the application and press a button to start the transaction (shown below). When the "After Change-Before Post!" Message is displayed (in the middle of the transaction), I execute the application a second time, and start a second transaction. I allow the econd application to complete, the go back to the first application and complete it.

Both applications show the SAME QTY (they should not)

How do I create the desired result?

MSQuery.Active := True;
try
MSConnection.StartTransaction;
MSQuery.Edit;
Edit1.Text := MSQuery.FieldByName('QTY').asstring;
MSQuery.FieldByName('QTY').AsInteger :=
MSQuery.FieldByName('QTY').AsInteger -1;
Edit2.Text := MSQuery.FieldByName(QTY').asstring;
ShowMessage('After Change-Before Post!');
MSQuery.Post;
MSConnection.Commit;
Edit3.Text := MSQuery.FieldByName('QTY').asstring;
ShowMessage('After Post/Commit!');
except
MSConnection.Rollback;
ShowMessage('After Rollback!');
end;
MSQuery.Active := False;

[Guest]

While insuring that a unique field is included in the "select" statement prevents the "0 records found error", this does not solve the "locking" issue.

I created a test app with an intention pause between an edit and post. I tried this test with and without transactions, and with the connection.isolationlevel set to every available option, one option at a time.

Test Procedure:
I execute the application and press a button to start the transaction (shown below). When the "After Change-Before Post!" Message is displayed (in the middle of the transaction), I execute the application a second time, and start a second transaction. I allow the econd application to complete, the go back to the first application and complete it.

Both applications show the SAME QTY (they should not)

How do I create the desired result?

MSQuery.Active := True;
try
MSConnection.StartTransaction;
MSQuery.Edit;
Edit1.Text := MSQuery.FieldByName('QTY').asstring;
MSQuery.FieldByName('QTY').AsInteger :=
MSQuery.FieldByName('QTY').AsInteger -1;
Edit2.Text := MSQuery.FieldByName(QTY').asstring;
ShowMessage('After Change-Before Post!');
MSQuery.Post;
MSConnection.Commit;
Edit3.Text := MSQuery.FieldByName('QTY').asstring;
ShowMessage('After Post/Commit!');
except
MSConnection.Rollback;
ShowMessage('After Rollback!');
end;
MSQuery.Active := False;