--- In IBObjects@yahoogroups.com, Lucas Franzen <luc@r...> wrote:

> > procedure TDMFb.UpdateAveIdealUsage(const sItem : String);
> > Var
> > iInvItemID : Integer;
> > qry : TIB_Query;
> > Begin
> > iInvItemID := DMFb.GetInvItemID(sItem);
> > if (iInvItemID <> 0) then
> > Begin
> > qry := TIB_Query.Create(Self);
> > Try
> > qry.IB_Connection := conIx;
> > qry.IB_Transaction := trnIx;
> > qry.DatabaseName := 'IxFB';
>
> what do you assign the database for???
> Just assign the Connection and Transaction. That's all you need.
>
> > qry.SQL.Add('Select QtySold From xItemUsages');
> > qry.SQL.Add('Where InvItemID = ' + IntToStr(iInvItemID));
>
> Add a qry.PREPARE here.
>
> > qry.Open;
> > qry.First;
>
> Remove the qry.FIRST;
> Where do you expect a TIB_Query to be at when opening?
>
> >
> > // #todo1 Not Working - Returns 0 for QtySold
> > if (not qry.FieldByName('QtySold').IsNull) then
> > Begin
> > DMFb.spUpdateAveIdealUsage.ParamByName
> > ('pInvItemID').AsInteger := iInvItemID;
> > DMFb.spUpdateAveIdealUsage.ParamByName
> > ('pUsage').AsFloat := qry.FieldByName('QtySold').AsFloat;
> > DMFb.spUpdateAveIdealUsage.ExecSQL;
>
> Is the update just happening here?
> What does the procedure look like?
>
> > End;
> > Finally
> > qry.Close;
> > FreeAndNil(qry);
> > End;
> > End;
> > End;
> >
> > Note that IBExpert returns the correct value with exactly the

same

> > query.
>
> With exactly the same SQL, not the same query ;-)
> What happens if you do the same select with an existing TIB_Query

(one

> that you're not creating on the fly?)

Interesting that you suggest removing the qry.First. Helen told me
to replace the qry.Open with the qry.First because, according to
her, the Open method doesn't cause a fetch.

Here's the update procedure.

create procedure spUpdateAveIdealUsage(iInvItemID BigInt, dUsage
Double Precision)
as
Begin
update xInvItem
set Cycle3Ideal = Cycle2Ideal,
Cycle2Ideal = Cycle1Ideal,
Cycle1Ideal = AveIdealUsage,
AveIdealUsage = :dUsage
where (InvItemID = :iInvItemID);
End

I've tried the same process using an existing TIB_Query instead of
creating dynamically and I get the same results.