Importing from Oracle using ADO - null values

finder

Hi,

I have a problem with importing data from Oracle into Navision. I have made connection and it's working fine with date and text types. If I try to read data which have Number type and empty (null) values then I'll get an error message "The call to memeber Writetext failed. Type mismatch"
For text and data fields I have added following line:
IF ADORecSet.Fields.Item('Descr').Value.ISTEXT THEN BEGIN .. and it works perfectly. But I cannot find option for Number type, I have tried ISDECIMAL and ISINTEGER but neither of them is suitable. I will post also my code which I've been using:

//IF ADORecSet.Fields.Item('CUSTOMERNO').Value.ISINTEGER THEN BEGIN
ADOStream.Open;
ADOStream.WriteText(ADORecSet.Fields.Item('CUSTOMERNO').Value);
ADOStream.Position:= 0;
FieldValue:= ADOStream.ReadText;
ADOStream.Close;
OraSalesInv."Sell-to Customer No." := FieldValue;
//END;

I really appreciate if someone could help me with this.
Thanks in advance,
Anti

dmccrae

The problem is presumably because of the null values.

Is there any way you can instead create an Oracle view on your table that converts the nulls to 0 - I believe this is the NVL() function, and use the view that instead of the table?

Alternatively, if you are using the SQL Server Option of Navision, you could create a SQL Server view into your Oracle table, using a linked server, and create a LinkedObject from Navision on top of that. This is a bigger change to what you have, but the advantage is that Navision will handle data type conversions for you.

Marije_Brummel

In the download section of the site you can download an ADO example which deals with these issues.

finder

Yes, I have made my connection following this example but the problem is I cannot get it to work with Oracle number fields if value is null. It is working perfectly with text and date values in both cases (existing value or null value) For text fields I have added this line: IF ADORecSet.Fields.Item('Descr').Value.ISTEXT THEN BEGIN but I cannot find anything for number field as ISDECIMAL and ISINTEGER aren't working there. But without this control it's giving an error message if there is null value.
Is there something that I'm missing or are there some other functions in Navision which can test null values and skip these fields?

finder

I finally get it working. I used Field property ActualSize and replaced line:
IF ADORecSet.Fields.Item('NO').Value.ISDECIMAL THEN BEGIN
with line
IF FORMAT(ADORecSet.Fields.Item('NO').ActualSize) <> FORMAT(0) THEN BEGIN
and it's working