no result set returned for one field, but OK for another

[smartglass]

Hi:
VFP 9, SP2, remote view against Oracle 8.
I want to filter against one field (orderno, number(38),not null) but 'no result set' returned.
However, applying filter to other fields of similar type (albeit shorter) returns data.
Is the difference that orderno is a key? However I get the same result with other, non key fields.It seems arbitrary!
In each case clicking 'ignore' returns "cannot find object VDTemp {} in the database".
Thanks in advance!

 

[Mike Lewis]

Smartglass,

You'll have to provide us with more information. Can you post the actual code you are using for this query (just the relevant statements, not the whole of the routine)?

Also - and I'm sorry if this sounds obvious - are you certain that there is data in the database that meets the query? In other words, is it possible that the query is in fact correct?

Also, you talk about clicking Ignore. That suggest that you are seeing a VFP error message. What is that all about?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[smartglass]

Thanks, Mike.
THe table xxdespatch resides in Oracle.
I can write a local query, reading the view (unfiltered), that returns order 570500.But I am trying to filter (and parameterise)the view first!
THe dialog box I get reads:"No result set has been returned by the server". Buttons cancel, ignore and help (latter no use). Ignore reads "cannot find object VDTemp in the database"

Here is the view :
SELECT Xxdespatch.OrderNo;
FROM ;
LIORDER.XXDESPATCH Xxdespatch;
WHERE Xxdespatch.OrderNo = 570500

DBSetProp(ThisView,"View","SendUpdates",.F.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","ShareConnection",.F.)
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","WhereType",3)
DBSetProp(ThisView,"View","ParameterList","Batch,'N'")

DBSetProp(ThisView+".orderno","Field","DataType","N(20)")
DBSetProp(ThisView+".orderno","Field","UpdateName","LIORDER.XXDESPATCH.OrderNo")
DBSetProp(ThisView+".orderno","Field","KeyField",.T.)
DBSetProp(ThisView+".orderno","Field","Updatable",.F.)

But This field DOES return; if the number 570500 is not in this field, I still get a return of a blank table, and no error;

SELECT Xxdespatch.SITE;
FROM ;
LIORDER.XXDESPATCH Xxdespatch;
WHERE Xxdespatch.SITE = 570500

DBSetProp(ThisView,"View","SendUpdates",.F.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","ShareConnection",.F.)
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","LIORDER.XXDESPATCH")
DBSetProp(ThisView,"View","WhereType",3)
DBSetProp(ThisView,"View","ParameterList","Batch,'N'")

DBSetProp(ThisView+".site","Field","DataType","N(4)")
DBSetProp(ThisView+".site","Field","UpdateName","LIORDER.XXDESPATCH.SITE")
DBSetProp(ThisView+".site","Field","KeyField",.F.)
DBSetProp(ThisView+".site","Field","Updatable",.T.)


I may not be able to return to you until monday but thank you very much for whatever you can do!

 

[Mike Lewis]

Smartglass,

According to your code, the field Xxdespatch.SITE has a data spec of N(4). That means it can hold numbers up to 9999. But you are trying to test it against 570500. That's not going to work.

Check the field spec within Oracle, and if necessary adjust the remove view accordingly.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk