Preventing Duplicates in BeforePost event.

Using D6, SQL2000, ADO.

I have a dataset in BatchUpdate mode and I'm trying to prevent users from
entering duplicat records into a dataset. At first I tried to let the
database report the error with a 'Primary Key' violation and then rollback
the transaction after attempting a BatchUpdate(arAll). However the records
in the dataset which posted correctly, then had a status of UnModified so
the next time I executed the BatchUpdate(arAll) these records would not be
posted to the database (the rollback removed them).

So I went a different route. Using the BeforePost event I cloned the
dataset and tested the value that was about to be posted.

procedure TdmTestDelay.dsetAccessionBeforePost(DataSet: TDataSet);
var CloneSet : TADODataSet;
RS : _RecordSet;
begin
CloneSet := TADODataSet.Create(nil);
try
CloneSet.Connection := dmADOConnections.conANSR;
CloneSet.LockType := ltBatchOptimistic;
RS := TADODataSet(DataSet).Recordset.Clone(adLockUnspecified);
RS.Set_ActiveConnection(nil);
CloneSet.Recordset := RS;
if
CloneSet.Locate('ACCESSION',DataSet.FieldByName('ACCESSION').AsString,[])
then
begin
MessageDlg(DataSet.FieldByName('ACCESSION').AsString+
' has already been entered.', mtInformation, [mbOK], 0);
Abort;
end;
finally
CloneSet.Close;
CloneSet.Free;
end;
end;

This worked great while the user was entering the values. However, if at a
later time in the code a record had to be modified, the BeforePost event
would fire and then I would get the message about a dupicate record. This
is because the cloned dataset now had all entered record and whenever the
BeforePost event fires it aready has the values.

Does anyone else know of a better way to prevent duplicates?


.