Delphi fkLookup ado unknown error (EOleException) on insert

JustinThor
JustinThor used Ask the Experts™
on
Dear All,

I have an TADOQuery that selects * from a table and I have added persistant fields. All works fine with viewing and inserting new records, but when I add a persistant field of the type fkLookup and try to insert e new record I get a Debugger Exception Notification EOleException: An unknown error has occured. This pops up for every field in the insert.
This is the text from the unit:
    object qWebLinksTypeLookup: TStringField
      DisplayLabel = 'Type'
      FieldKind = fkLookup
      FieldName = 'TypeLookup'
      LookupDataSet = qWebLinkTypes
      LookupKeyFields = 'ID'
      LookupResultField = 'TypeName'
      KeyFields = 'LinkType'
      Lookup = True
    end
The error does not occur if the lookup field is not there. Any idea what I am doing wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
it is maybe caused by the fact that the LinkType field has an invalid value for qWebLinkTypes dataset
can you try setting a DefaultExpression for the field LinkType ?

I am not sure that you have a problem when you insert a new record or when you POST it (save it to BD)
can you post here the code that does not work, with the exact line where exception is raised ?

Author

Commented:
Hi Epasquier,

I dont think the value is invalid. When I debug I see the value is an integer.
As far as I can see while debuging the error is between Insert and Post. Below is the code, the exception occurs in every line after Insert and before Post (i.e. 7 times) - the record is posted to DB, but with out the value in the 'LinkType' field. The lookup field is not included in this, but is in the ADOQuery and defined as above.

    with qWebLinks do
    begin
      Insert;
      FieldByName('Department').AsInteger := iDeptID;
      FieldByName('AddedBy').AsInteger := iPersonID;
      FieldByName('URL').AsString:= trim(sURL);
      FieldByName('Description').AsString:= trim(sDescription);
      FieldByName('Comments').AsString:= trim(sNote);
      FieldByName('AddedDate').AsDateTime:= now;
      FieldByName('LinkType').AsInteger:= WebLinkID2.AsInteger;
      Post;
    end;

brgds
Justin
Ferruccio AccalaiSenior developer, analyst and customer assistance

Commented:
The EOleException is raised by the DB, not by the Delphi application
Take care that the size of the two fields ID and LinkType is the same. It seems that you're trying to write a value larger than the field capacity.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
Hi Ferruccio68,

The thing that is confusing me is that the exception does not occure if I remove the Lookup field from the ADOQuery?
As you can see, the lookup field is not involved in the Insert. The lookup field is included in the ADOQuery for display in a grid and for editing the records in a grid, the above code is for adding a new record out side the grid (i.e. I have another form that alows adding via edit fields and a button).
Ferruccio AccalaiSenior developer, analyst and customer assistance

Commented:
Ok, so it could be related to the datalink itself

Try disabling and reenabilng the controls

with qWebLinks do
    begin
      DisableControls;
      Insert;
      FieldByName('Department').AsInteger := iDeptID;
      FieldByName('AddedBy').AsInteger := iPersonID;
      FieldByName('URL').AsString:= trim(sURL);
      FieldByName('Description').AsString:= trim(sDescription);
      FieldByName('Comments').AsString:= trim(sNote);
      FieldByName('AddedDate').AsDateTime:= now;
      FieldByName('LinkType').AsInteger:= WebLinkID2.AsInteger;
      Post;
      EnableControls;
    end;

Author

Commented:
Hi Ferruccio68,

Thanks for the suggestion, tried it, but same exceptions :-(

Ferruccio AccalaiSenior developer, analyst and customer assistance

Commented:
Any OnbeforePost or OnAfterPost or OnAfterInstert etc. code?


Author

Commented:
Nope.
Senior developer, analyst and customer assistance
Commented:
So maybe it's something related to the lookupcache

Set the  LoohUpCache property  of the qWebLinksTypeLookup persistent field to true and retry


 object qWebLinksTypeLookup: TStringField
      DisplayLabel = 'Type'
      FieldKind = fkLookup
      FieldName = 'TypeLookup'
      LookupDataSet = qWebLinkTypes
      LookupKeyFields = 'ID'
      LookupResultField = 'TypeName'
      KeyFields = 'LinkType'
      LookupCache = True
      Lookup = True
    end


Form delphi help

Set LookupCache to true to cache the values of a lookup field when the LookupDataSet is unlikely to change and the number of distinct lookup values is small. Caching lookup values can speed performance, because the lookup values for every set of LookupKeyFields values are preloaded when the DataSet is opened. When the current record in the DataSet changes, the field object can locate its Value in the cache, rather than accessing the LookupDataSet. This performance improvement is especially dramatic if the LookupDataSet is on a network where access is slow.

If every record of DataSet has different values for KeyFields, the overhead of locating values in the cache can be greater than any performance benefit provided by the cache. The overhead of locating values in the cache increases with the number of distinct values that can be taken by KeyFields.

If LookupDataSet is volatile, caching lookup values can lead to inaccurate results. Call RefreshLookupList to update the values in the lookup cache. RefreshLookupList regenerates the LookupList property, which contains the value of the LookupResultField for every set of LookupKeyFields values.

When setting LookupCache at runtime, call RefreshLookupList to initialize the cache.


Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
I concur that seeing your code there is no reason why ADO would be involved in that lookup field, for EACH line, unless it tries to update the lookup field each time there is a change in the field (not optimized, but possible, as it would be needed in some case).
I recommend setting this value first, ie move that line above all :
      FieldByName('LinkType').AsInteger:= WebLinkID2.AsInteger;
check if it has any effects

Author

Commented:
Hi Ferruccio68,

That did it - many thanks. Now executes with out exceptions :-)

Thanks also epasquier, was about to try your suggestion, but now it is working - so I will just have to remember to use the LookupCache = True

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial