Genie grid not returning all rows

[dougmatthews47]

Hello,

I have a very simple Genie grid with a simple SQL statement.
I am using custom sql in the grid data area...

SELECT ocrec# as Rec#, octyp as Type, ocloc as Location, ocdesc as Comment
FROM comment WHERE ocord# = ? and octyp = 'B' ORDER BY octyp

When I use the ? parameter value, Genie does not return all the rows,
When I use an actual value instead of the ? from the parameter value it returns all the rows.
i.e...
SELECT ocrec# as Rec#, octyp as Type, ocloc as Location, ocdesc as Comment
FROM comment WHERE ocord# = '1769954' and octyp = 'B' ORDER BY octyp

I have even tried using DATABASE FILE, FIELDS, and SELECTION CRITERIA with the parameter value and get the same results

Any suggestions would be greatly appreciated.

[dougmatthews47]

Just an update to this problem....

When I do not have an extra condition in the SQL statement i.e (and octyp = 'B')
The grid returns all the rows using the parameter value.
This only seems to happen when having more than one conditional statement in the SQL

[dougmatthews47]

Another Update....

I have tried..
var ordnum = get("D_1_23");
applyProperty("comgrid","parameter value",ordnum);
applyProperty("comgrid","widget type","grid");
in the LOAD function of the page

and I have tried..
script:get("D_1_23");
in the parameter value of the grid

and both do not return all rows when an additional condition in the SQL statement exists.
When only one condition exists all the rows get returned...
Weirdness!

[matt.denninghoff]

I'm not seeing the same problem. Perhaps I'm not understanding a step you described. I have this as a grid's "custom sql" property:

Code: Select all

select prid, pname from puisamples/prodp where prid < ?

and this as the "parameter value" property:

Code: Select all

script: get("D_1_13");

My screen's D_1_13 field happens to have a value of "68". I get 34 rows returned. That's the correct amount.

I can also hard-code "parameter value" to 68 in the grid properties and get the same result.

The only problem I see is when trying to get a value from a field that I added to the screen--in which case it doesn't seem to exist when the "parameter value" script is rendering.

[dougmatthews47]

add another condition into your sql stmt like..

select prid, pname from puisamples/prodp where prid < ? and pname = ?

Let me know if you get the correct number of rows returned.

This only happens when I use the parameter value with multiple conditions in the sql stmt

I have tried it with multiple parameter values and hardcoded values in the sql stmt

[matt.denninghoff]

I set my "custom sql" property to:

Code: Select all

select prid, pname from puisamples/prodp where prid < ? and pstock < ?

And I right clicked the "parameter value" property, then clicked "Add Another Parameter Value". Then under "parameter value 2" I set it to a field that evaluated to 84.

I get the same number of results as if I were to put 68 and 84 and the PRID and PSTOCK conditions: 31.

[dougmatthews47]

Oki Doki,
I'll look elsewhere for the issue....Wierdness

Thanks