I have the following code ... incredibly simple:

const
 aHour = 1 / 24;
 a9am = 9 / 24;
var
 i : integer
begin
 Q := TDBISAMQuery.Create(self);
 Q.DatabaseName:= gDB.DatabaseName;
 Q.SessionName:= gDB.SessionName;
 Q.RequestLive:= true;
 Q.SQL.Text := 'SELECT * FROM Events';
 Q.Open;

 Q.First;
 for i := 0 to Q.RecordCount - 1 do
   begin
     Q.Edit;
     Q.FieldByName('DateStart').asDateTime:= Q.FieldByName('DateStart').asDateTime+a9am;
     Q.FieldByName('DateEnd').asDateTime:= Q.FieldByName('DateEnd').asDateTime+a9am+aHour;
     Q.Post;
     ChangeCounter := ChangeCounter + 1;
     Q.Next;
   end;

In this form it works as expected and updates every event to start at 9.00 am & finish at
10.00 am.

If I replace the "Select" line above with:

 Q.SQL.Text := 'SELECT * FROM Events ORDER BY StartDate';

Only some of the records are updated. Where more than 1 record is on the same date only
the _last_ record on that date is updated and the others are ignored.

Can someone explain to me why this is happening!

"adam" <adam@fullwellmill.co.uk> wrote in message
news:3E479416-B8A6-4997-9045-A27040D3B5EF@news.elevatesoft.com...
>
> Can someone explain to me why this is happening!
>

Once you change the value of the index field, you are in a different
position on the table and the "next" takes you to neverland.

Robert

>>Once you change the value of the index field, you are in a different
>>position on the table and the "next" takes you to neverland.

Sorry, maybe I was not clear. I am opening the query newly, with a different SQLStr

"SELECT * FROM Events ORDER BY StartDate"

I am not switching indexes on an open dataset.

If I have a DBGrid open on this dataset and I have a button with "Q.Next" in it then the
dataset iterates exactly as I would expect, running through the data records in the order
they have been returned by the query.

However when I try to run logic on every record only some are touched ...

adam


When you have a live (sensitive) rather than a canned query you are essentially just opening the table which is why you can make changes and have them posted back to the table concerned. If you use an ORDER BY clause that matches exactly an existing index (which is the only way you can get a live result set with an ORDER BY clause) then you are basically doing a table.index := type of operation.

So here

'SELECT * FROM Events';

You've opened the table with its default index whereas here

SELECT * FROM Events ORDER BY StartDate

you've opened it with the index of StartDate.

If you change StartDate say from 5am on the day to 9am on the day then you have probably changed the position of that row. So when you go Q.Next it goes to the next one after the CURRENT row position which may well not be what it was before you posted the record. Hence a number of records will appear to have been missed.

Roy Lambert [Team Elevate]

Roy,

Thanks so much for this, I've been working with DBISAM for years & this really had me
scratching my head! I usually work through business objects, so don't really touch the
DBISAMQuery objects directly ... the business objects use canned queries and UPDATE
statements, which disguise this process. I forgot about "flyaway" records D'oh!