BorlandTalk.com

Chris Cooper · Guest

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?

Chris Cooper · Guest

The TADODataSet(DataSet).RecordStatus will aways be rsNew untill the
UpdateBatch is executed. Sorry to be a pain, but using my code can you give
me an example of what you mean?

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Chris Cooper · Guest

Never mind, I realized you ment the state of the dataset not the record. I
think I got it now, thanks for the hint.

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Chris Cooper · Guest

Now I have a new problem. I got the BeforePost working but now the pending
updates will not post to the database. I have verifed that there are indeed
updates pending, and after a call to UpdateBatch there are no more updates
pending, but the records are not in the database.

If I comment out the code in the BeforePost, then the record are posted to
the database. However, for some reason if the code in the BeforePost is
used then no records will be posted to the database on the UpdateBatch.

One more thing is there anyway to debug the UpdateBatch procedure? I would
like to step through it and see some command is not being executed do to
some status. the only procedure I can step into is the following..

procedure TCustomADODataSet.UpdateBatch(AffectRecords: TAffectRecords);
begin
CheckBrowseMode;
Recordset.UpdateBatch(AffectRecordsValues[AffectRecords]); --- Can I step
into this somehow?
UpdateCursorPos;
Resync([]);
end;

Here is my new and inproved BeforePost...

--------------
procedure TdmTestDelay.dsetAccessionBeforePost(DataSet: TDataSet);
var CloneSet : TADODataSet;
RS : _RecordSet;

procedure CloneIt;
begin
CloneSet.Connection := dmADOConnections.conANSR;
CloneSet.LockType := ltBatchOptimistic;
RS := TADODataSet(DataSet).Recordset.Clone(adLockUnspecified);
RS.Set_ActiveConnection(nil);
CloneSet.Recordset := RS;
end;

begin
if not(DataSet.State in [dsInsert, dsEdit]) then exit;
CloneSet := TADODataSet.Create(nil);
try
if (DataSet.State in [dsInsert]) then
begin
CloneIt;
if
CloneSet.Locate('ACCESSION',DataSet.FieldByName('ACCESSION').AsString,[])
then
begin
MessageDlg(DataSet.FieldByName('ACCESSION').AsString+
' has already been entered.', mtInformation, [mbOK], 0);
Abort;
end;
end else
begin
CloneIt;
if
(CloneSet.Locate('ACCESSION',DataSet.FieldByName('ACCESSION').AsString,[]))
and not(CloneSet.RecNo = DataSet.RecNo) then
begin
MessageDlg(DataSet.FieldByName('ACCESSION').AsString+
' has already been entered.', mtInformation, [mbOK], 0);
Abort;
end;
end;
finally
CloneSet.Close;
CloneSet.Free;
end;
end;
-----------------------

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote