Loop Counter starting with maxamum value not 1

[MLNorton]

I have a program written with Delphi 7 that has a simple procedure to zero out values in an ADO Table. There are 52 rows, one for each week of the year. When I try to run the procedure I get the following error message:
Key column information is insufficient or incorrect. Too many rows were effected by update.
Here is my code:
With ADOTable2 do
begin
active := true;
first;
edit; For I := 1 to 52 do
begin
FieldByName('Week').AsString := '';
FieldByName('DateName').AsString := '';
FieldByName('WeekName').AsString := '';
FieldByName('Collect').AsInteger := 0;
FieldByName('Visitor').AsInteger := 0;
FieldByName('Unresolved').AsString := '';
next;
end;
post;
end;

By stepping through the code I determined that I has a beginning value of 52 not 1. This seems to be the problem.
Why is the loop counter not counting correctly and how can I resolve this problem?

 

[Prattaratt]

If you have a Key column, the values in that key column need to be unique; if you try to set any row to a duplicate value in that particuar column (Field), you will get an error.

Also, I would not even try executing what you are trying to do in a loop. That will execute on the client system; I would use an Update query instead (which runs on the DB server) by dropping a TADOQuery on the form and setting its SQL property to:

"UPDATE TableName SET TableName.Week = '', TableName.DateName = '', TableName.WeekName = '', TableName.Collect = 0, TableName.Visitor = 0, TableName.Unresolved = '';"

Now, if one of the columns is a key index, say week, you would leave that column out of the update.
and execute it with the following code:

  ADOQuery1.Prepared :=true;
  ADOQuery1.ExecSQL;

 

[MLNorton]

This problem solved and now new problem with SQL in new post.

 

[harebrain]

As for the other mystery, when you write a "for" loop incrementing from 1 to a maximum value, whenever your loop counter isn't involved in a calculation that might somehow affect the loop's execution, Delphi converts the loop to start at the maximum value and decrement to 1 (or whatever the minimum value is.) This is a speed optimization: DEC is a faster operation in machine language than INC.

 

[Griffyn]

The original issue though was that you you had the Post command outside of your loop. It must be inside it prior to the Next command, otherwise you're essentially throwing away your changes every time Next is called. The reason the last number (52) is updated to your database is because that's what the last changes were when you finally called Post.

 

[SammyB1]

If the fieldname "WEEK" was part of the key or the entire key, all you had to do was not blank it out. BTW, If the ADO table sets EOF to true when calling NEXT, you could have have used a While NOT EOF DO loop instead of a For loop. As to the "POST" being outside of the loop, when NEXT is issued, there is an implicit POST called. However, since after the last table change there is no further movement thru the table, an explicit call to POST is required before closing the table. The FOR loop counter always start at the value set initially and are normally incremented unless the "DOWN TO" is used instead to "TO".

Good luck.

 

[Griffyn]

Hi SammyB1 - Thanks for the correction on the implicit Post command when Next is called. I had no idea.