Subject Re: [IBO] using Tib_Query.Locate in inserting mode
Author Helen Borrie
At 09:35 PM 27/09/2004 +0000, you wrote:
>Hi all.-
>first excuse my poor engish...
>I want to verify if an field exist during an inserting a new one to
>prevent duplicates.
>The field is Primary key and is VarChar (25) and not null off course.
>I need to verify it onExit the edit control (teacher requirement) and
>i try to do the next:
>procedure TFormaClientes.EditCedulaExit(Sender: TObject);
> with TIB_Query(TIB_Edit(sender).DataSource.Dataset) do
> if State = dssInsert then
> try
> if Locate('"Cedula"',TIB_Edit(sender).Text,[]) then
> begin
> ShowMessage(Format(csExiste,['La C├ędula','a','a']));
> TIB_Edit(sender).SetFocus;
> TIB_Edit(sender).SelectAll;
> end
> except
> Abort;
> end;
>but the locate fuction always returns true.
>What i'm doing wrong??

"Catch-22" - Locate() searches the dataset buffer. Since you have already
inserted this data, it is already in the buffer and so will always return
true !

It's not your fault. Your teacher is unfortunately showing his/her
ignorance of multi-user systems if he/she has told you to use Locate() for
this test. The proper way to handle this is through exception handling.

Provided you have set up KeyLinks properly, your client "knows" about keys
that are in its KeyFields buffer. In there, it will have the keys for a)
the records that were visible to the transaction when it began and b) any
uncommitted records that the user has inserted. In BeforePost, IBO will
throw a dataset exception if you try to insert a key value that it already
"knows" about. You catch this exception and return control to the user.

However, there are other records that the dataset does not "know"
about. Those are records (changed or inserted by another client) that were
committed after your client's transaction began. Even if your client's
transaction is in ReadCommitted isolation, your client's dataset does not
know about these changes until it tries to post its own changes.

The essential thing that your teacher has overlooked when setting this
requirement is this: even if the dataset permits the insert, it may still
be rejected on Post by the server because of activity by other
transactions. Thus, you must also catch and handle the Key Violation
exception returned by the server when the Post is attempted.

Note, also, that you should not read the Text property of the IB_ controls
to determine the value in the buffer. Read the underlying Fields[] value
(or FieldByName, or Row.ByName) of the dataset, and cast it to type using
the appropriate "AsWhatever" casting method.