Using ADO to read Visual Foxpro Tables

jelittlejohn

Hi,

I'm hoping someone can help me out as I have run into a problem that I know has to have a solution. I have a client who is receiving order information from a web site that is currently being stored in 4 Foxpro tables. We are trying to automate order entry into Navision for these web orders. At first, I thought I would have to write a Visual Basic app using ODBC to gather these records and place them in my Navision SQL tables. Then I discovered ADO by reading threads on this message board. I was very happy as working withing Navision opens up the use of any number of Validations and Codeunits that are already written. I was able to open the first fox table using the following test code:


ConnStr := 'Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF;SourceDB=c:\OrderImport;Exclusive=No' ;

CLEAR(ADOrs) ;
CREATE(ADOrs) ;

IF NOT wo.FIND('+') THEN
ConnQ := 'select * from orders'
ELSE
ConnQ := 'select * from orders where Id > ' + wo.OrderID;
ADOrs.Open(ConnQ,ConnStr) ;

ADOrs.MoveFirst ;
REPEAT
test := ADOrs.Fields.Item('Total').Value;
IF NOT ADOrs.EOF THEN ADOrs.MoveNext ;
UNTIL ADOrs.EOF ;
ADOrs.MoveFirst ;
ADOrs.Close;

'test' is just a variable I was using to observe the data as I scanned through it. Everything seemed to be working fine. I was even able to read a Foxpro Memo field. So, I began to create the actual funtional code. As soon as I attempted to read a Foxpro numeric field, I got the following message(The code above produced the error as 'Total' is a Foxpro Numeric field. 'test' is a decimal variable in this case.):

---

This message is for C/AL programmers:

This data type is not supported by C/SIDE. You can access data from any of the following data types: VT_VOID, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR AND VT_BOOL.

---

When I changed data types, I did change the data type of my 'test' variable. I also though that my problem might be that the integer field was too large as the Foxpro field I was working with actually represented an Order ID and ws set to be 17 digits long. So, I tried to use FORMAT to put the value into a text variable. This didn't work either.

I'm hoping someone has seen this before as I have read every thread on the forum that references 'FOXPRO'. I also know that these circumstances are unusual, but unfortunately, I cannot change them. This is a temporary solution as a new web site is in the works and will allow greater freedom but we are still looking a several months until the new site is ready Until then, I must either solve this ADO problem or turn to Visual Basic to solve the problem. Staying inside Navision is my preference by far.

I know I am missing something obvious. There would be no point in this if C/SIDE couldn't read numeric data through ADO. I will be unable to check the foreum tomorrow as the Thanksgiving holiday here in the US will keep me away from my computer until Friday morning. Thanks in advance for any help you may be able to give.

JacK

garak

Check if there is no NULL value and also check the data type.
NAV has problems with the SQL decimal data type.
An old post about these theme:
viewtopic.php?f=23&t=24104

Search also the forum for "VT_BSTR AND VT_BOOL" and / or "foxpro ado".

The problem is ever the same ....

For more infos about ADO u can also read Waldo's Blog (follow the first link).

Regards

robsmit

Writing back to Navision from foxpro is not advisable. Other way to do that.
Just reading VFP tables is a conversion and traditional dataport thing, but ...............