Dear Helen & Alan,

Silly me....I found the problem. My variable was referencing the wrong
column which returned a value very close to the one required, but when
used in the query it correctly returned zero rows. Tricky to spot, but
I'll learn from this and be more careful about naming. It cost me 2
days and not insubstantial trauma...

> Remove the two redundant pairs of brackets in the WHERE clause. The
> DSQL parser (on the server side) will just ignore those
> brackets).

I originally used the code automatically generated by the query
builder in IBExpert, which created the brackets. I have removed them now.

In what circumstances would you need to use the uprepare/prepare and
active commands?

Does the following code automatically close the query/dataset if it is
open, before redoing it?

> with qryCurrProjSpecs do
> begin
> ParamByName('Var_PVERSION_ID').asinteger := curr_proj_version;
> Open;
> end;

Thanks for your help.

Mitch

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 11:02 PM 21/11/2006, you wrote:
>
> >I'd be grateful for any assistance with this which is driving me nuts.
> >
> >This procedure calls a query which successfully returns all rows if I
> >comment out the ParamByName line in the procedure and the parameter in
> >the query.
> >
> >If I remove the commenting out, zero rows are returned, even though
> >the variable "curr_proj_version" is correctly set and there are
> >definitely rows in the table that match. I can manually enter the
> >chosen value for "curr_proj_version" in the query and open the dataset
> >and get the required rows.
> >
> >Any ideas why this might not be working?
>
> Remove the two redundant pairs of brackets in the WHERE clause. The
> DSQL parser (on the server side) will just ignore those
> brackets; but the chances are that the parser on the IBO side is
> making gravy out of them. (You can check this by plonking in an
> ib_MonitorDialog...)
>
> The logic of your procedure isn't very great. You're making the
> component do a lot of stuff it does anyway; and your testing logic
> misses some conditions. See comments inline....
>
> >procedure TfrmProjectContainer.SpecificationsTabShow(Sender: TObject);
> >begin
> >with DataModule5 do
> >begin
> >if not IBODatabase1.Connected then
> >IBODatabase1.Connect;
> >
> >if qryCurrProjSpecs.Active then
> >qryCurrProjSpecs.Unprepare;
>
> No, drop these two lines. "Active" for a dataset means it is
> prepared and open. You don't want to unprepare it if it is already
> prepared. You don't call Prepare yourself on a query component,
> anyway; it is encapsulated in the Open routine.
>
> You might want
>
> if qryProjSpecs.Active then
> qryProjSpecs.Close;
>
> >qryCurrProjSpecs.prepare;
>
> Not needed, but if you want to, you can do it this way:
>
> if not qryCurrProjSpecs.Prepared then
> qryCurrProjSpecs.prepare;
>
> >with qryCurrProjSpecs do
> >ParamByName('Var_PVERSION_ID').asinteger := curr_proj_version;
> >qryCurrProjSpecs.Open;
>
> Why not this --?
>
> with qryCurrProjSpecs do
> begin
> ParamByName('Var_PVERSION_ID').asinteger := curr_proj_version;
> Open;
> end;
>
> It's mainly stylistic (code is easier to debug and maintain) but you
> mix and mash approach is neither one thing nor the other.
>
> >end;
> >end;
> >
> >=====
> >qryCurrProjSpecs:
> >
> >select
> >spec.spec_id,
> >spec.pversion_id,
> >spec.spec_descr,
> >spec.supplier_ref,
> >spec.unitcost_est,
> >spec.unitcost_act,
> >spec.approved,
> >spec.unitdelivcost,
> >spec.act_leadtime,
> >spec.est_leadtime,
> >spec.spec_active,
> >spectype.name_eng spectype_name,
> >spec.specattr_fabric_id,
> >spec.specattr_misc_id,
> >spec.specattr_elec_id,
> >uom.code uomcode,
> >party.shortname supplier_shortname,
> >spec.spectype_id
> >from spec
> >inner join spectype on (spec.spectype_id = spectype.spectype_id)
> >inner join uom on (spec.uom_id = uom.uom_id)
> >inner join party on (spec.supplier_id = party.party_id)
>
> Change this:
>
> >where
> >(
> >(pversion_id = :var_PVERSION_ID)
> >)
>
> to this:
> where pversion_id = :var_PVERSION_ID
>
> What version of IBO are you using?
>
> Helen
>