[DUG]: query.next only once?

Tracey Maule Wed, 05 Mar 2003 17:38:30 -0800

I think i finally have that query sussed.

My new problem is this:

In the following code, query1 returns 7 records

SkillID Name

--------------------------------------------------------------

7 Class A License

5 Forklift License

6 Typing

4 Class 2 License

1 Export Scaling Module

3 Basic First Aid

2 Hazard Identification

Looping through these records, the IBQuery1.next works fine the first time round (iThisSkillID changes from 7 to 5 in step-through)

However, at the ibquery1.next the subsequent (infintesimal) times, it retains the value of 5 (over and over and forever.)

can some fresh eyes tell me what im doing wrong? I cant see it. maybe i just need a beer.

heres the code:

procedure TForm1.RunSQL;
var
    iThisSkillID, iThisPersonID: integer;
begin
    try
        with IBQuery2 do
        begin
            SQL.Add('   select p.name pname, s.name sname, tp.weeks');
            SQL.Add('from personemployment pe');
            SQL.Add('join skillneeded sn on sn.employmentid = pe.employmentid');
            SQL.Add('join person p on pe.personid = p.personid');
            SQL.Add('join skill s on s.skillid = sn.skillid');
            SQL.Add('join timeperiod tp on tp.timeperiodid = sn.timeperiodid');
            SQL.Add('where personid NOT IN');
            SQL.Add('(select distinct personid from skillattempt where skillid = :skillid and result = ''P''');
            SQL.Add('       and');
            SQL.Add('(validto is null or validto > :today))');
            SQL.Add('and skillid = :skillid');
            SQL.Add('       and ((commencedate + (weeks * 7)) < :28Days)');
            ParamByName('28Days').AsDate := (Now + 28);
            ParamByName('today').AsDate := Now;
            Prepare;
        end;
        with IBQuery3 do
        begin
            Close;
            SQL.Clear;
            //query brings back all records that are less than 28 days away from expiring
            SQL.Add('Select sa.skillattemptid, s.name sname, sa.validto, sa.result, p.name pname, p.companyid, s.skillid, p.personid');
            SQL.Add('from skillattempt sa');
            SQL.Add('join skill s on s.skillid = sa.skillid');
            SQL.Add('join person p on p.personid = sa.personid');
            SQL.Add('where sa.result = ''P''');
            SQL.Add('and sa.validto < :28Days');
            SQL.Add('and skillid = :skillid');
            SQL.Add('order by companyid, skillid');
            ParamByName('28Days').AsDate := (Now + 28);
            Prepare;
        end;

        try
            with IBQuery1 do
            begin
                Close;
                SQL.Clear;
                SQL.Add('Select * from skill');
                SQL.Add('order by companyid, groupid');
                open;
                first;
                iThisSkillID := FieldByName('skillid').AsInteger;
                while not eof do
                begin
                    IBQuery3.ParamByName('skillid').AsInteger :=
                        iThisSkillID;
                    IBQuery3.Open;
                    First;
                    while not IBQuery3.eof do
                    begin
                        MessageStringList.Add('     ' +
                            IBQuery3.FieldByName('pname').AsString);
                        IBQuery3.next;
                    end;                //while not eof Query3
                    IBQuery3.Close;
                    IBQuery2.ParamByName('skillid').AsInteger := iThisSkillID;
                    IBQuery2.Open;
                    IBQuery2.First;
                    while not IBQuery2.Eof do
                    begin
                        MessageStringList.Add('     -' +
                            IBQuery2.FieldByName('pname').AsString);
                        IBQuery2.next;
                    end;        //while not eof query2
                    IBQuery2.Close;
                    //*** here, we move to the next record, this only seems to work once
                    IBQuery1.next;
                    iThisSkillID := IBQuery1.FieldByName('skillid').AsInteger;
                end;                    //while not eof query1
                MessageStringList.SaveToFile('messagestrings.txt');
            end;                        //with IBQuery1
        finally
            IBQuery1.Close;
            IBQuery2.UnPrepare;
            IBQuery3.UnPrepare;
        end;                            //finally
    except
        on E: Exception do
        begin
            ShowMessage('an exception occurred: ' + E.Message);
            Application.Terminate;
        end;
    end;                                //except

end;

Tracey
Software Developer / Web Master
Logis
[EMAIL PROTECTED]
(025) 213-1065

[DUG]: query.next only once?

Reply via email to