Issue with While loop going through record set from query

Question

I have an issue with my iteration through the data returned by a query. I am using Delphi XE2 and MS Access as the DBMS.

I have the issue in every play I try to iterate through the record set, it will only process the first record and then skip the rest, the query is correct because I use a RecCount function and it returns the correct amount of records but the While loop does not go through the records, it iterates once and proceeds on.

Below is parts of the coding:

DB : TADOQuery; //Where DB is this

//Only retrieves the OrderIDs belonging to that username as the Object searchs for its own information using the orderID
DB.Close;
DB.SQL.Text := 'SELECT OrderID FROM tblOrders WHERE Username = ' + '''' + pUsername + '''';
DB.ExecSQL;
DB.Open;

ShowMessage(IntToStr(DB.RecordCount));
fCount := 0;
while NOT(db.Eof) AND (fCount < 10) do
Begin
  Inc(fCount);

fArr[fCount] := TOrder.Create(DB.FieldByName('OrderID').AsInteger); //Creating of the object

DB.Next;
end;

DB.Close;

So that is the code, as you can see the ShowMessage returns the record count and it returns 3( The correct amount of records for that username) . But the loop only iterates once and I do not understand why.

Thanks Ben

EDIT

It seems that this segment of the code prevents the loop from iterating for some reason, without it the loop iterates through and returns all the correct values. With help from @Alexandre

fArr[fCount] := TOrder.Create(DB.FieldByName('OrderID').AsInteger); <-- Error

To Fix the issue this is how my code now finally looks, it works correctly.

var
OrderArr : ARRAY[1..10] of Integer;
k, iIndex : Integer;
begin
opendb('DB.mdb');

//Only retrieves the OrderIDs belonging to that username as the Object searchs for its own information using the orderID
  DB.Close;
  DB.SQL.Text := 'SELECT OrderID FROM tblOrders WHERE Username = ' + '''' + pUsername + '''';
  DB.Open;
  DB.DisableControls;

  fCount := 0;
  try
   DB.First;
   while not DB.EOF do
   begin
    // do something here with each record
    Inc(fCount);

    OrderArr[fCount] := DB.FieldByName('OrderID').AsInteger;
    DB.Next;
   end;
  finally
    DB.EnableControls;
   end;

  for k := 1 to fCount do
  Begin

  fArr[k] := TOrder.Create(OrderArr[k]);

  End;

That seems to work, I have no idea why it doesn't work with the create in the first loop. Hopefully someone can help still

Thanks Ben

Solution

Please try this pattern. It must work as expected otherwise there is something really wrong in your code. Note that DB.ExecSQL makes no sense here, once you are going to open a query, not execute some other statement like INSERT, DELETE or UPDATE. Also, if your DataSet (DB) is not attached to a visual control, consider enclosing the loop in DisableControls and EnableControls calls. This has a huge impact on speed of the loop when you have many records in an ADO DataSet.

DB: TADOQuery;

DB.Close;
DB.SQL.Text := 'SELECT OrderID FROM tblOrders WHERE Username = ' + '''' + pUsername + '''';
DB.Open;
DB.DisableControls;
try
  DB.First;
  while not DB.EOF do
  begin
    // do something here with each record
    DB.Next;
  end;
finally
  DB.EnableControls;
end;

Comments

[Ben]

I used your pattern, I figured out a way to get around the issue. It seems that the code fArr[fCount] := TOrder.Create(DB.FieldByName('OrderID').AsInteger); code stops the loop from iterating for some reason. I Simply loaded the orderIDs into a temporary array and then have another For loop to go through those values and create them in the OrderArr array. I will put it as a edit for my OP. @Alexandre