dBASE Questions and Answers - beforeGetValue crashes rowset

Author

beforeGetValue crashes rowset

john marshall

2005-07-27, 11:37 am

Can anyone tell me why it is when I add a beforeGetValue calculated field to a query (shown in a grid) which uses parameters from another rowset(record) in it's WHERE clause - it stops working? Once I remove the code from the query the rowset results app
ear once again and work just fine.

Perplexing!

JM

Roland Wingerter

2005-07-27, 1:28 pm

john marshall wrote:
> Can anyone tell me why it is when I add a beforeGetValue calculated
> field to a query (shown in a grid) which uses parameters from another
> rowset(record) in it's WHERE clause - it stops working? Once I
> remove the code from the query the rowset results appear once again
> and work just fine.
------
Can you show us the relevant code or better yet post a turnkey example?

Roland

john marshall

2005-07-27, 8:24 pm

I cannot post a turnkey example, but I will dig up the two slices. I do have to tell you that I tested this with the most simplistic attempt - showing POValue/Qty = "Price Each". The strange this is that the correct data shows in the new column in the "
Form Designer", but when run, the PODetails are blank.

This is the woking copy: (and notice that this is also an attempt to work around the left outer join that I cannot get to work either).

this.DBO_INGREDIENTS1 = new QUERY()
this.DBO_INGREDIENTS1.parent = this
with (this.DBO_INGREDIENTS1)
left = 88.8571
top = 20.0455
database = form.eatec1
sql = "select p.bidnumber as item_code, p.unit, p.qty1 as qty,
p.company as pco, p.conversion as pk_size, p.value1 as cost,
p.item from dbo.podetail p where p.pointnum =:ponum"
requestLive = false
params["ponum"] = "3600"
active = true
endwith

with (this.DBO_INGREDIENTS1.rowset)
fields["unit"].lookupSQL = "select intnum, abbreviation from zetup13"
fields["item"].lookupSQL = "select intnum, id from dbo.ingredients"
endwith

The BeforeGetValue was basically a calc of "this.parent["cost"].value / this.parent["qty"].value

JM

Roland Wingerter Wrote:

> john marshall wrote:
> ------
> Can you show us the relevant code or better yet post a turnkey example?
>
> Roland
>
>

john marshall

2005-07-28, 1:39 pm

Here's the code that clobbers the Grid/Query! The calculation works and shows correctly when in the Form Designer, but when I run it there are no records in the rowset! Is is breaking the query somehow?

this.DBO_POLIST1 = new QUERY()
this.DBO_POLIST1.parent = this
with (this.DBO_POLIST1)
left = 79.5714
top = 20.3182
database = form.eatec1
sql = "select p.intnum as po, p.company, p.postdate, p.location, p.valueo as amt, p.enteredby, p.status, p.revnumber from dbo.pohead P where p.status = 0 order by p.postdate desc"
requestLive = false
active = true
endwith

with (this.DBO_POLIST1.rowset)
fields["company"].lookupSQL = "select intnum, ID from dbo.suppliers"
fields["location"].lookupSQL = "select intnum, ID from dbo.zetup08"
endwith

this.DBO_INGREDIENTS1 = new QUERY()
this.DBO_INGREDIENTS1.parent = this
with (this.DBO_INGREDIENTS1)
onOpen = class::DBO_INGREDIEN
TS1_ONOPEN
left = 88.8571
top = 20.0455
database = form.eatec1
sql = "select p.bidnumber as item_code, p.unit, p.qty1 as qty, p.company as pco, p.conversion as pk_size, p.value1 as cost, p.item from dbo.podetail p where p.pointnum =:ponum"
requestLive = false
params["ponum"] = "3570"
active = true
endwith

with (this.DBO_INGREDIENTS1.rowset)
fields["unit"].lookupSQL = "select intnum, abbreviation from zetup13"
fields["item"].lookupSQL = "select intnum, id from dbo.ingredients"
fields["Each"].beforeGetValue = {|| this.parent["cost"].value / this.parent["qty"].value}
endwith

function dbo_ingredients1_onO
pen

local x
x = new field()
x.fieldname := "Each"
x.beforegetvalue := {|| this.parent["cost"].value / this.parent["qty"].value }
this.rowset.fields.add(x)

return

john marshall Wrote:

>
> Can anyone tell me why it is when I add a beforeGetValue calculated field to a query (shown in a grid) which uses parameters from another rowset(record) in it's WHERE clause - it stops working? Once I remove the code from the query the rowset results a
ppear once again and work just fine.
>
> Perplexing!
>
> JM

Roland Wingerter

2005-07-29, 7:24 am

john marshall wrote:
> I cannot post a turnkey example, but I will dig up the two slices. I
> do have to tell you that I tested this with the most simplistic
> attempt - showing POValue/Qty = "Price Each". The strange this is
> that the correct data shows in the new column in the "Form Designer",
> but when run, the PODetails are blank.
>
> This is the woking copy: (and notice that this is also an attempt to
> work around the left outer join that I cannot get to work either).
>
> this.DBO_INGREDIENTS1 = new QUERY()
> this.DBO_INGREDIENTS1.parent = this
> with (this.DBO_INGREDIENTS1)
> left = 88.8571
> top = 20.0455
> database = form.eatec1
> sql = "select p.bidnumber as item_code, p.unit, p.qty1 as qty,
> p.company as pco, p.conversion as pk_size, p.value1 as
> cost, p.item from dbo.podetail p where p.pointnum
> =:ponum" requestLive = false
> params["ponum"] = "3600"
> active = true
> endwith
-------
One thing I don't understand in your query is the syntax of "dbo.podetail".
What does "dbo" stand for? Is it a reference to a database alias? Or perhaps
a typo for "dbo_alias"?

Even if the syntax "dbo.podetail" can be used in a MSSQL query, it is NOT
valid local sql syntax that can be used in dBASE. For server specific
syntax, check out SQLExec() in the OLH.

Roland

John Marshall

2005-07-29, 7:24 am

The 'BDO_fid' is how it appeared afer being 'dragged' into the app from the tables list. It is a 3rd party MSSQL table and that is their existing naming style. The other table 'reference' was a dbase file. Is it possible that all these joins, etc don't
like to be mixed? At this point I think that I'm just going to copy the user selected podetails into a dbf and get on with things from there ... not as elegant as I would have liked, but far more time effective.

PS: Did you see my not on the beforegetvalue (the alternative path solution?) I can get a simple calculated field to add and display correct results in the form designer, but it fails when run. More specifically, the rowset fails to load/query, etc an
d there is nothing displayed in the grid! ?

Personally, I thought that adding a calc field to a rowset would be a far less taxing task. I an beginning to find that some of the most powerful 'stated' features reall just do not work without hand coding.

JM

Roland Wingerter Wrote:

> john marshall wrote:
> -------
> One thing I don't understand in your query is the syntax of "dbo.podetail".
> What does "dbo" stand for? Is it a reference to a database alias? Or perhaps
> a typo for "dbo_alias"?
>
> Even if the syntax "dbo.podetail" can be used in a MSSQL query, it is NOT
> valid local sql syntax that can be used in dBASE. For server specific
> syntax, check out SQLExec() in the OLH.
>
> Roland
>
>

Ken Mayer [dBVIPS]

2005-07-29, 7:24 am

Roland Wingerter wrote:
> john marshall wrote:
>
>
> -------
> One thing I don't understand in your query is the syntax of "dbo.podetail".
> What does "dbo" stand for? Is it a reference to a database alias? Or perhaps
> a typo for "dbo_alias"?
>
> Even if the syntax "dbo.podetail" can be used in a MSSQL query, it is NOT
> valid local sql syntax that can be used in dBASE. For server specific
> syntax, check out SQLExec() in the OLH.

It's needed for MSSQL tables ... weird, but true.

Ken

--
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase

Ken B

2005-07-29, 7:24 am

>I cannot post a turnkey example, but I will dig up the two slices. I do
>have to tell you that I tested this with the most simplistic attempt -
>showing POValue/Qty = "Price Each". The strange this is that the correct
>data shows in the new column in the "Form Designer", but when run, the
>PODetails are blank.
>
> This is the woking copy: (and notice that this is also an attempt to work
> around the left outer join that I cannot get to work either).
>
> this.DBO_INGREDIENTS1 = new QUERY()
> this.DBO_INGREDIENTS1.parent = this
> with (this.DBO_INGREDIENTS1)
> left = 88.8571
> top = 20.0455
> database = form.eatec1
> sql = "select p.bidnumber as item_code, p.unit, p.qty1 as qty,
> p.company as pco, p.conversion as pk_size, p.value1 as cost,

p.value1 / p.qty1 as calc,

> p.item from dbo.podetail p where p.pointnum =:ponum"
> requestLive = false
> params["ponum"] = "3600"
> active = true
> endwith
>
> with (this.DBO_INGREDIENTS1.rowset)
> fields["unit"].lookupSQL = "select intnum, abbreviation from zetup13"
> fields["item"].lookupSQL = "select intnum, id from dbo.ingredients"
> endwith
>
> The BeforeGetValue was basically a calc of "this.parent["cost"].value /
> this.parent["qty"].value
---
Try doing the calculation as part of the query itself instead of using the
BeforeGetValue method.

John Marshall

2005-07-30, 7:23 am

Roland:

I made an interestinf discovery ... these hangups seem to have virtually gone away after I removed the table aliasing (i.e. podetail p). Perhaps this is because of the dbo.podetail naming, but once I went through and changed all to explicit references
, this starting behaving themselves. Certainly seems like a bug in the way dbase passes to sql. I'll check into it more. I still have not been able to make the left outer join work even with this new breakthrough. I just went back to using a scratch t
able.

Thanks for ALL the help and guidance.

JM

john marshall Wrote:

> Here's the code that clobbers the Grid/Query! The calculation works and shows correctly when in the Form Designer, but when I run it there are no records in the rowset! Is is breaking the query somehow?
>
>
> this.DBO_POLIST1 = new QUERY()
> this.DBO_POLIST1.parent = this
> with (this.DBO_POLIST1)
> left = 79.5714
> top = 20.3182
> database = form.eatec1
> sql = "select p.intnum as po, p.company, p.postdate, p.location, p.valueo as amt, p.enteredby, p.status, p.revnumber from dbo.pohead P where p.status = 0 order by p.postdate desc"
> requestLive = false
> active = true
> endwith
>
> with (this.DBO_POLIST1.rowset)
> fields["company"].lookupSQL = "select intnum, ID from dbo.suppliers"
> fields["location"].lookupSQL = "select intnum, ID from dbo.zetup08"
> endwith
>
> this.DBO_INGREDIENTS1 = new QUERY()
> this.DBO_INGREDIENTS1.parent = this
> with (this.DBO_INGREDIENTS1)
> onOpen = class::DBO_INGREDIEN
TS1_ONOPEN
> left = 88.8571
> top = 20.0455
> database = form.eatec1
> sql = "select p.bidnumber as item_code, p.unit, p.qty1 as qty, p.company as pco, p.conversion as pk_size, p.value1 as cost, p.item from dbo.podetail p where p.pointnum =:ponum"
> requestLive = false
> params["ponum"] = "3570"
> active = true
> endwith
>
> with (this.DBO_INGREDIENTS1.rowset)
> fields["unit"].lookupSQL = "select intnum, abbreviation from zetup13"
> fields["item"].lookupSQL = "select intnum, id from dbo.ingredients"
> fields["Each"].beforeGetValue = {|| this.parent["cost"].value / this.parent["qty"].value}
> endwith
>
> function dbo_ingredients1_onO
pen
>
> local x
> x = new field()
> x.fieldname := "Each"
> x.beforegetvalue := {|| this.parent["cost"].value / this.parent["qty"].value }
> this.rowset.fields.add(x)
>
> return
>
>
>
> john marshall Wrote:
>
appear once again and work just fine.[color=darkred]
>

Roland Wingerter

2005-08-01, 7:25 am

John Marshall wrote:
>
> I made an interestinf discovery ... these hangups seem to have
> virtually gone away after I removed the table aliasing (i.e. podetail
> p). Perhaps this is because of the dbo.podetail naming, but once I
> went through and changed all to explicit references, this starting
> behaving themselves. Certainly seems like a bug in the way dbase
> passes to sql. I'll check into it more.
-------
Interesting indeed. Regarding the dbo.podetail syntax required for MSSQL,
does dQuery generate the same syntax if you drag and drop a MSSQL table from
the navigator into dQuery? Just being curious.

> I still have not been able
> to make the left outer join work even with this new breakthrough. I
> just went back to using a scratch table.
--------
For testing purposes drop everything that is not absolutely necessary, e.g.
the lookUpSQL property, to see if it makes a difference.

> Thanks for ALL the help and guidance.
--------
You are welcome.

Roland

Sponsored Links