Thanks David,

I will try your steps, today I don't commit my transaction after I
fetched the rows.
By the way, I'm using CommitRetaining after any post.
Is it any difference between that and commit, other than retaining
keeps the resultset?
What is the most efficiant(best)?

BR
svenj
--- In firebird-support@yahoogroups.com, David Johnson
<d_johnson@c...> wrote:

> On Thu, 2005-07-07 at 10:31 +0000, pungsven wrote:
> > My question is what went wrong and how can I fix it?
> > I think it maybe has something to do with when one user edits a record
> > while another is reading it.
>
> I can't answer "what went wrong" ... Helen or Ann would be better able
> to answer that.
>
> However, as a general principle, you should not be using ISAM style live
> edits on table rows with a RDBMS. This question would not arise if you
> were not using live edits. The RDBMS approach sidesteps this sort of
> technical issue by addressing the design problem slightly differently.
>
> The retrieval for display and edit should be a separate transaction from
> the update operations.
>
> Behind the scenes, whatever the application appears to do, it should:
> 1. start a transaction,
> 2. fetch the row(s),
> 3. commit,
>
> 4. allow the user to edit the record until he indicates he is done
> (return control to the GUI),
> 5. perform any internal validation,
>
> 6. start a new transaction
> 7. Perform any validation against the DBMS
> 8. insert/update/delete the affected row(s), and
> 9. commit
>
> On your updates, you may wish to guarantee that the record was unchanged
> by some mechanism such as a timestamp (there are some issues with this
> in Firebird - the API only supports 1 second precision - but it can be
> done) or a generator based rowstamp (cleaner in Firebird then timestamp,
> if less informative for business purposes).
>
> To guarantee that rows that were changed by another user between the
> first fetch and the commit are not updated without review, validate
> against the DBMS using something like "select count(*) where
> primaryKeyField=:primaryKeyField and rowstamp = :rowstamp". If the
> count is 0, rollback the transaction reload the row, and return to the
> GUI piece.
>
> >From this point, you can get fancy and apply deltas or invent some
> mechanism for merging or approving the changes if you want to, but a
> simple message and display of the current values is generally
> sufficient.