ADO Recordsets and SQLServer Type 'Decimal'

jsnayberk · 2006-03-06

I have to access an external database (SQL-Server) from inside Navision (4.00). I use Microsoft ActiveX Data Objects 2.8 Library to do the job and everything works fine, exept one thing:
When I try to get a SQL-Decimal (9-bit float) into a Navision-Decimal (8-bit float) I run into an error telling me that these types are not compatible.
In a test database I changed the SQL data type to 'Money' and something like

EVALUATE(navDecimal,FORMAT(adoRS.Fields.Item('Amount').Value));

worked fine.
Unfortunately I cannot change the data type in the customers database. So I have to find a way to access this SQL-Decimal field.
Any ideas?

Saalek · 2006-03-07

Hi
Try to use variant varialbes
and then, use evaluate to convert variant variable to decimal

navVariant:= adoRS.Fields.Item('Amount').Value; 
evaluate(navDecimal,NavVariant);

Bye

jsnayberk · 2006-03-08

Sorry, wan´t work.
At

evaluate(navDecimal,NavVariant);

following error message appears:
'You cannot enter '☺' in Decimal. The cursor is in front of the invalid character.'

But finally I found a (may be crippled) solution:
I use two recordsets. The first one with sql:

select DocNo, cast(Amount as Money) as Amount from MyTable

to assign the amount field to Navision Decimal variable;
and the second recordset with sql:

select * from MyTable where DocNo = 999

to update a certain record.
For the update of the amount field I can use a Navision Decimal variable without problems because no information will be lost in this direction.

kriki · 2008-07-30

[Topic moved from Navision forum to SQL General forum]

ADO Recordsets and SQLServer Type 'Decimal'

Comments

Categories