At 11:00 AM 05-02-02 -0800, you wrote:

>Hi,
>
>Is there conceptually anything wrong with my layout. I want the loop to
>continue processing for the duration of the table length.

It won't if you roll or commit the transaction back partway through the loop - that closes the cursors. A dataset won't get revived until you open it again and then it will just revert to the beginning again.

I take it that what you want is to log any error records and then pass on to the next record in the set?

>Errors must be
>trapped in the exception part and must be written to a text file.

Don't write them to a text file until after the processing is finished. Write them to a TStringlist and then write that out to a text file if it contains anything.

>There are
>approx 1000 records, but on the first error, I get 'end of dataset' error. I
>am reading from a Source table and copying to a Target table(this is only
>part of the bigger app).

...
var
Badlist: TStringlist;
ErrFile: TFileName;
...
counter := 0; //<--- doesn't seem to do anything
ErrFile := 'ErrorFile.txt';

> if IB_Transaction1.InTransaction then
> IB_Transaction1.Rollback;
> IB_Transaction1.StartTransaction;

Badlist := TStringList.Create;
TRY

> qrySourceMembers.First; // assuming this is a tib_cursor
> while not qrySourceMembers.EOF do
> begin
> { add member to target db }
> with qryTargetMembers do
> begin

if State <> dssInsert then

> Append;
> FieldByName('MEMBERID').Assign(qrySourceMembers.FieldByName('MEMBERID') ) ;
> FieldByName('TITLEID ').Assign(qrySourceMembers.FieldByName('TITLEID') ) ;
> FieldByName('INITIALS').Assign(qrySourceMembers.FieldByName('INITIALS') ) ;
> FieldByName('FIRSTNAME').Assign(qrySourceMembers.FieldByName('FIRSTNAME') ) ;
> FieldByName('SURNAME').Assign(qrySourceMembers.FieldByName('SURNAME') ) ;
> FieldByName('DOB').Assign(qrySourceMembers.FieldByName('DOB') ) ;
> try
> Post;
> except
> on E:Exception do

Badlist.Add('the string you want to log')
end;
end;

> qrySourceMembers.Next;
> end;
> if IB_Transaction1.InTransaction then

try

> IB_Transaction1.Commit;

except
on E:EIB_ISCError do
begin
IB_Transaction1.RollBack;
BadList.Add(E:Message); // saves the ISC error message
MessageDlg('Could not commit dataset: operation cancelled', mtError,[mbOK],0);
end;
end;
if BadList.Count > 0 then
BadList.SaveToFile(errfile);
FINALLY
BadList.Free;
END;

Sorry, not tested, but you get the idea...

Still, I suspect this is not the end of the story, since you are writing rows to another dataset. Presumably, therefore, you want the user to do something with the new rows. Depending on the dataset, you might not see those rows (wherever you expect them to be) when you refresh datasets after the commit.

If the new rows are not for immediate use in the GUI, then don't write them this way. Further, if the source rows are being brought into the ib_cursor purely for the purpose of providing data for insertion into another table, don't do the source part this way either. Have the database do this work and make your GUI a parameter selector for an ib_dsql that has SQL like

insert into target_table (<list of target columns>)
(select <matching list of source columns>
from source_table where aCol = :aCol and.... )

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at www.ibobjects.com