Refresh option roAllFields not working

[janv1]

Howdy, we are using Delphi 5 with Direct Oracle Access 4.0.6. The dataset in question has all the refresh options except roAllFields. When I turn on roAllFields, the query to refresh is malformed- with debug on, it shows
"where D.RowID=doa__rowid

OA__ROWID=AABQ9dAAEAAAXF2AAI"
when roAllFields is on. I've tried placing the hint (/* END_REFRESH */), but it has not helped.

The sequence of events is that an Insert is performed, the user selects choices from dropdowns, and data from a Dev Ex data aware button control. Once the button control data is selected, a post is performed in the code, but not all the joined fields are returned in order to be reflected on the form. So I turned on roAllFields to get those fields, but it does not work.

So basically, button edit selection is made then:
MyDataset.Post;
CurrRec := MyDataset.FieldByName('Recnum').AsInteger;
MyDataset.RefreshRecord; //because join data is not shown
MyDataset.Locate('Recnum',CurrRec[]);//Because refresh loses the record
MyDataset.Edit; //put it back in edit mode because we are not done.

Then the Invalid SQL error is thrown and the joined data is still not refreshed even with the call to RefreshRecord and roAllFields on.

The dataset sql is like:

select D.RowID, D.*,
O.LNAME, O.FNAME, O.Street,
V.LNAME, V.FNAME, V.Street
from MainTable D
left outer join Table2 O on D.ORecnum = O.Recnum
left outer join Table 2 V on D.VRecnum = V.Recnum
:FILTER

Order by D.SomeField
/*Modified for QBE*/

We are using the dataset with QBE and the QBE part is working okay.

Can you see why the roAllFields RefreshOption is not working?

Thanks!

 

[Marco Kalter]

How exactly is it malformed?

 

[janv1]

With roAllFields=True and Debug on, this SQL occurs:

"where D.RowID=doa__rowid

OA__ROWID=AABQ9dAAEAAAXF2AAI"

Then the Invalid SQL error is shown.

 

[Marco Kalter]

There is no "select"?

 

[janv1]

Nope, it's all missing except the where D.RowID... there.

 

[janv1]

I worked around it by using a separate dataset for the fields that would not refresh between Editing and QBE mode.