How to get sql Table data in to navision table.

te6

Hi all,

I Want to insert table data in to my navision database from another SQL 2005 Database . using ADOConnection, i written some code shown below

IF ISCLEAR(ADOConnection) THEN CREATE(ADOConnection);

ADOConnection.ConnectionString := ('Driver={SQL Server}; Server=servername; Database=dbname; Uid=userid; Pwd=password');
ADOConnection.Open;


SQLString := 'SELECT * FROM [DBNAME].[dbo].[TABLENAME]';
RecordSet1 := ADOConnection.Execute(SQLString);
RecordSet1.MoveFirst;
WHILE Cnt <=5 DO BEGIN
WeighBridge.ID:= RecordSet1.Fields.Item('ID').Value;
WeighBridge."FIELDNAME1" := RecordSet1.Fields.Item('FIELDNAME1').Value;
WeighBridge."FIELDNAME2" := RecordSet1.Fields.Item('FIELDNAME2').Value;
WeighBridge."FIELDNAME3" := RecordSet1.Fields.Item('FIELDNAME3').Value;
WeighBridge.INSERT;
RecordSet1.MoveNext;
Cnt+=1;
END;
ADOConnection.Close

it throws an error like
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_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR and VT_BOOL

i am not able to assign the fields which the data types are bigint ,decimal,int in SQL2005 in to the navision table Fields.
please suggest me.

Regards
AKHIL.

bbrown

If your NAV database is also SQL, why not use a linked table instead of ADO?

zappy

You can use this trick ;-)

On SQL do

CREATE VIEW TableTXT AS
SELECT CONVERT(varchar(40), BigIntNumber) AS BigIntNumberTXT
FROM YourTable

And after you can use this VIEW and work with any type of field. Almost any type \:D/

DKFFox

And it works brilliantly.

But, in Nav 2013 you need to remember to use NVARCHAR for text and code fields instead of VARCHAR.
I just spent an hour working out why some of our code failed in 2013 when it worked fine in 2009 !