Board index » delphi » Cant loop through database???

Cant loop through database???

Hi all,

I run a loop through my database, to look for a value. When the value is
found I need to change it to something else. The problem is when I do the
Edit-Change-Post routine, the loop exits and doesnt run any further. Can
anyone suggest anything?

Cheers    fuzzyd...@yahoo.com

  dmMain.tbCandidate.DisableControls;
  dmMain.tbCandidate.First;
  while not dmMain.tbCandidate.Eof do
    begin
      vRecordDate := dmMain.tbCandidate['LastUpdated'];
      if vLimit3Set = true then
        begin
          if ((Date - vRecordDate) >= vLimit3) AND
(dmMain.tbCandidate['CurrentStatus'] <> 2) then
            begin
              dmMain.tbCandidate.Edit;
              dmMain.tbCandidate['Status'] := 3;
              dmMain.tbCandidate['Mode'] := 4;
              dmMain.tbCandidate.Post;
            end;
        end;
      dmMain.tbCandidate.Next;
    end;
  dmMain.tbCandidate.EnableControls;

 

Re:Cant loop through database???


Quote
On Thu, 7 Oct 1999 22:28:00 +0800, "Doc" <fuzzyd...@yahoo.com> wrote:
>I run a loop through my database, to look for a value. When the value is
>found I need to change it to something else. The problem is when I do the
>Edit-Change-Post routine, the loop exits and doesnt run any further. Can
>anyone suggest anything?

[...]

Your While loop is based on the Eof property of the dataset named
tbCandidate. It seems logical to me that this would be the key to this
situation. You should step through this portion of the application and use
a Watch window to track the value of this property.

Could it be that your dataset has an index active, one based on the fields
being changed, and the changes move the record to the end of the dataset?
This would mean the very next call to the Next method would make Eof true
and the loop would appear to be finished.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx

Re:Cant loop through database???


Quote
Doc <fuzzyd...@yahoo.com> wrote in message

news:7tiarl$mtb$1@news.cowan.edu.au...

Quote
> I run a loop through my database, to look for a value. When the value is
> found I need to change it to something else. The problem is when I do the
> Edit-Change-Post routine, the loop exits and doesnt run any further. Can
> anyone suggest anything?

> Cheers    fuzzyd...@yahoo.com

>   dmMain.tbCandidate.DisableControls;
>   dmMain.tbCandidate.First;
>   while not dmMain.tbCandidate.Eof do
>     begin
>       vRecordDate := dmMain.tbCandidate['LastUpdated'];
>       if vLimit3Set = true then
>         begin
>           if ((Date - vRecordDate) >= vLimit3) AND
> (dmMain.tbCandidate['CurrentStatus'] <> 2) then
>             begin
>               dmMain.tbCandidate.Edit;
>               dmMain.tbCandidate['Status'] := 3;
>               dmMain.tbCandidate['Mode'] := 4;
>               dmMain.tbCandidate.Post;
>             end;
>         end;
>       dmMain.tbCandidate.Next;
>     end;
>   dmMain.tbCandidate.EnableControls;

I'm guessing that the table index is causing the modified record
to become the "last" record after posting. If so, set IndexName
to an empty string before entering the loop, and restore it
afterwards.

Oh, and "if vLimit3Set then ..." is preferable to
"if vLimit3Set = true then ...". And you might want to adopt
this structure:
  TableX.DisableControls;
  try
    { do stuff }
  finally
    TableX.EnableControls;
  end;

Sorry to be picky.

--
Jeremy Collins
Kansai Business Systems
http://www.kansai.co.uk/

Re:Cant loop through database???


You're probably changing an index field. The simplest solution is to use a
query as the source for the loop (you should be able to select ontly the
records that need changing) and then update the ttable.

query.open;
query.first;
while not query.eof begin
  locate record in table
  update table
  query.next
end

Quote
Doc <fuzzyd...@yahoo.com> wrote in message

news:7tiarl$mtb$1@news.cowan.edu.au...
Quote
> Hi all,

> I run a loop through my database, to look for a value. When the value is
> found I need to change it to something else. The problem is when I do the
> Edit-Change-Post routine, the loop exits and doesnt run any further. Can
> anyone suggest anything?

> Cheers    fuzzyd...@yahoo.com

>   dmMain.tbCandidate.DisableControls;
>   dmMain.tbCandidate.First;
>   while not dmMain.tbCandidate.Eof do
>     begin
>       vRecordDate := dmMain.tbCandidate['LastUpdated'];
>       if vLimit3Set = true then
>         begin
>           if ((Date - vRecordDate) >= vLimit3) AND
> (dmMain.tbCandidate['CurrentStatus'] <> 2) then
>             begin
>               dmMain.tbCandidate.Edit;
>               dmMain.tbCandidate['Status'] := 3;
>               dmMain.tbCandidate['Mode'] := 4;
>               dmMain.tbCandidate.Post;
>             end;
>         end;
>       dmMain.tbCandidate.Next;
>     end;
>   dmMain.tbCandidate.EnableControls;

Re:Cant loop through database???


In your eof loop use the .edit and change the value,then do a .next.
Do not POST, that causes the loop ending(eof),I think
Franky

Quote
Doc <fuzzyd...@yahoo.com> wrote in message

news:7tiarl$mtb$1@news.cowan.edu.au...
Quote
> Hi all,

> I run a loop through my database, to look for a value. When the value is
> found I need to change it to something else. The problem is when I do the
> Edit-Change-Post routine, the loop exits and doesnt run any further. Can
> anyone suggest anything?

> Cheers    fuzzyd...@yahoo.com

>   dmMain.tbCandidate.DisableControls;
>   dmMain.tbCandidate.First;
>   while not dmMain.tbCandidate.Eof do
>     begin
>       vRecordDate := dmMain.tbCandidate['LastUpdated'];
>       if vLimit3Set = true then
>         begin
>           if ((Date - vRecordDate) >= vLimit3) AND
> (dmMain.tbCandidate['CurrentStatus'] <> 2) then
>             begin
>               dmMain.tbCandidate.Edit;
>               dmMain.tbCandidate['Status'] := 3;
>               dmMain.tbCandidate['Mode'] := 4;
>               dmMain.tbCandidate.Post;
>             end;
>         end;
>       dmMain.tbCandidate.Next;
>     end;
>   dmMain.tbCandidate.EnableControls;

Re:Cant loop through database???


Why not write a SQL Update that does this? It's probably faster.

"UPDATE salesinfo
SET taxrate = 0.0825
WHERE (state = "CA")"

Update atom values may be passed to the UPDATE statement via
parameters. You cannot use parameters for the table reference and
columns list.

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

Re:Cant loop through database???


Why not write a SQL Update?

"UPDATE salesinfo
 SET taxrate = 0.0825
 WHERE (state = "CA")"

Update atom values may be passed to the UPDATE statement via
parameters. You cannot use parameters for the table reference and
columns list.

//Andreas

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

Other Threads