Importing an SQL Table from a non Navision SQL database

DKBG · 2009-02-13

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 · 2009-02-13

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

DKBG · 2009-02-13

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 · 2009-02-13

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

DKBG · 2009-02-13

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 · 2009-02-13

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 · 2009-02-13

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 · 2009-02-13

You can change NULL is in your sql statement as well

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

You are welcome btw.

Importing an SQL Table from a non Navision SQL database

Answers

Categories