Importing an SQL Table from a non Navision SQL database

DKBG

Hi

I am importing data from a Non-Navision SQL database (From-Table) into the customer table in Navision.

In some of the records in the From-Table there are these little squares that stands for TAB - like when you copy something from Excel into Navision.

These little squares causes the import to break down and gives an error that the data type is not supported i C/SIDE.

Does anybody have an idea about how to remove these "squares" from the field value in the SQl table?

ara3n

How are you populating the Nav customer table? through dataport?

DKBG

Hi

I am running the below statement from a codeunit. Opens a connection to the database and executes the statement.

It runs successfully on other tables and even on records without this little square ](*,)

I put in a commit to see how far it got, which lead me to that it had to be something in the data, I tried to import...


ltxtConnectionString:='Driver={SQL Server};'
+ 'Server=NAV-SRV-01;'
+ 'Database=XXX';

IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection);
lADOConnection.ConnectionString:= ltxtConnectionString;
lADOConnection.Open;

lvarActiveConnection := lADOConnection;

IF ISCLEAR(lADOCommand) THEN CREATE(lADOCommand);
lADOCommand.ActiveConnection := lvarActiveConnection;

lADOCommand.CommandText := 'SELECT * FROM Stamkartotek';
lADOCommand.Execute;

IF ISCLEAR(bglADORecordset) THEN
CREATE(bglADORecordset);

bglADORecordset.ActiveConnection := lADOConnection;
bglADORecordset.Open(lADOCommand);

WHILE NOT bglADORecordset.EOF DO BEGIN

Debitor.INIT;
Debitor."No." := bglADORecordset.Fields.Item('Stam_nr').Value;
NameTemp := ansi2ascii.Ansi2Ascii(bglADORecordset.Fields.Item('Navn').Value);
Debitor.Name:= COPYSTR(NameTemp,1,50);

Any ideas?

ara3n

when you turn on the debugger, where does it stop when it errors?

DKBG

It stops here:

NameTemp := ansi2ascii.Ansi2Ascii(bglADORecordset.Fields.Item('Navn').Value);

I put it into a variable because the length of the field in the SQL DB is longer than customer name...

ara3n

Change your sql statement to

lADOCommand.CommandText := 'SELECT * FROM Stamkartotek';

SELECT LEFT(Navn,50)  as ShortName,* FROM Stamkartotek

NameTemp := ansi2ascii.Ansi2Ascii(bglADORecordset.Fields.Item('ShortName').Value);

DKBG

Thanks - that solved it.

I also got an Error on NULL values but got rid of that with "ActualSize"

Thank you so much for your quick response

ara3n

You can change NULL is in your sql statement as well

Select isnull(FIELDNAME, '') as NewFIELDNAME, * from tablename.




You are welcome btw.