----- Original Message -----
Sent: Thursday, March 06, 2003 2:49 PM
Subject: Re: [DUG]: query.next only once?
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; <<<<< You are going back to the first one in
IBQuery1
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;
