Link to home
Start Free TrialLog in
Avatar of ST3VO
ST3VOFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Problem with Code [Loop]

Hi all,

When I run the code below it takes 100% CPU and I have to kill the process.

Can someone help to make it run properly please?

Here is the code:

////////////////////////////////////////////////////
/// Procedure here
///////////////////////////////////////////////////
procedure TForm1.ExcludeListBoxItems;
var Exclude: TStringList;
    i: integer;
    Query: string;
begin
 Exclude := TStringList.Create();

 try
  for i := 0 to sListBox1.Count - 1 do
   Exclude.Add(QuotedStr(sListBox1.Items[i]));

  Query := Format('SELECT * FROM products where not (AccountID in (%s))', [Exclude.CommaText]);
 finally
  Exclude.Free();
 end;
end;

////////////////////////////////////////////////////////////////////////
/// Button Click Procedure here
///////////////////////////////////////////////////////////////////////
procedure TForm1.sButton5Click(Sender: TObject);
var TheField : String;
begin
  with DBAdvGrid1.DataSource.DataSet do
         begin
         First;
         DisableControls;
         try
            while not EOF do
               begin
               if DBAdvGrid1.DataSource.DataSet.FieldByName('AccountID').AsString = ADOQuery1.FieldByName('AccountID').Value then
               TheField:=(ADOQuery1.FieldByName('AccountID').AsString);
               Memo1.clear;
               //Memo1.Lines.Add(Format('SELECT * From products WHERE AccountID=''%s''',[TheField]));
               ExcludeListBoxItems;
               sListBox1.Items.Add(TheField);
               //exit; //Stop it here for now
               //...Do stuff there
   

               Next;
            end;
         finally
            EnableControls;
         end;
      end;
end;

Thanks

ST3VO
Avatar of MerijnB
MerijnB
Flag of Netherlands image

what is it you are trying to achieve.

please be aware that the function I gave you (which you now called "ExcludeListBoxItems") does not result anything or do anything actively.
It is just an example, in the Query variable in that procedure you'll find the query you asked for, but the procedure does not do anything itself!
Avatar of ST3VO

ASKER

I've modified the procedure to:

procedure TForm1.ExcludeListBoxItems;
var Exclude: TStringList;
    i: integer;
    Query: string;
begin
 Exclude := TStringList.Create();

 try
  for i := 0 to sListBox1.Count - 1 do
   Exclude.Add(QuotedStr(sListBox1.Items[i]));
   adoquery1.active := false;
   Memo1.Clear;
   Memo1.Lines.Add (Format('SELECT * FROM products where not (AccountID in (%s))', [Exclude.CommaText]));

    adoquery1.active := false;
    adoquery1.SQL.Assign(memo1.Lines);
    adoquery1.Active := true;

 finally
  Exclude.Free();
 end;
end;

So, it now executes the Query.

What I'm tring to do is more extensive which I can now make work one at the time but I need to automate
so everything is done one after the other in just one click.

Here is what it will do:

1. Connect to FTP Server and download a zip file containing a csv file.
2. Once downloaded extract the csv and import it.
3. Run the loop to separate the different contents for every account.
    3a: Some every record with accounrtid (x)
    3b: run the filtered query and display the contents on the DBGrid
    3c: Save to results back to a SMALLER CSV named AccountID.CSV with only the results of that     account.
   3d Loop next but AccountID leaving out the previous account just the next account
4. This created 1500 different csv files from 1 large csv
5. Zip all created contents
6. Upload ack to server.

END.

This is that I need to do :o)

> What I'm tring to do is more extensive which I can now make work one at the time

one what at a time?
How do you do this one at a time? Can you show code?
Avatar of ST3VO

ASKER

Basically I call every procedure one after the other in order!

Is that a bad way to do it?
Avatar of ST3VO

ASKER

By the way...your SQL code is producing:

This SQL String:

SELECT * FROM products where not (AccountID in ())

I'm getting a Syntax error :o/
if you get that query it means the listbox is empty
Avatar of ST3VO

ASKER

Opp...Your right....sorry a about that...I forgot that it was the first record and I need to populate it first!
you say you call a bunch of procedures after each other.
And that is for one 'whatever'.. right?
Avatar of ST3VO

ASKER

one 'whatever'.. right?  <--- Could you please rephrase this? I cannot understand what you mean there. Sorry about that!
> What I'm tring to do is more extensive which I can now make work one at the time

what did you mean with 'one at a time'. One what, one ftp site, one file on a ftp site, one database?
Avatar of ST3VO

ASKER

One Procedure at the time.

For example:

1. FTP Connect Procedure
2. FTP Download File Procedure
3. Extract Archieve Procedure
4. Load csv file procedure

etc...

so what is the problem then, just call them one by one and you're done, or do you mean something else?
Avatar of ST3VO

ASKER

Well, the problem is that It's so busy 100% CPU that I cannot see the things changing ...for example a new item is added to the listbox on every loop and I cannot see it happen...The DBGrid is also not refreshing with the new data.

Should I need to create some space between the events?


ASKER CERTIFIED SOLUTION
Avatar of MerijnB
MerijnB
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ST3VO

ASKER

Yep...That worked :o)  

Thanks very much!