ADO - SQL Server - data type - Error

brainstocker

Hi @ll,

I have the following problem.
This is the Error Message:

Dieser Datentyp wird von C/SIDE nicht unterstützt.
Sie haben Zugriff auf Daten der folgenden Datentypen:
VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR und VT_BOOL

and this is my Codeunit:

VAR
ADOConn@1000000005 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
ADORec@1000000006 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Recordset";

DS@1000000000 : Text[30];
DB@1000000001 : Text[30];
DP@1000000002 : Text[30];
sConn@1000000003 : Text[200];
sSQL@1000000004 : Text[200];
mytable@1000000005 : Record 50000;

      DS    := 'mysql2000server';
      DB    := 'mydb';
      DP    := '{SQL Server}';
      sConn := 'Driver='+ DP +';Server='+DS+';Database='+DB+';';
      sSQL  := 'SELECT * FROM NAVISION';

      {ADOConn = 'Microsoft ActiveX Data Objects 2.8 Library'.Connection}
      CREATE(ADOConn);

      ADOConn.ConnectionString(sConn);
      ADOConn.Open();

      {ADORec = 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset}
      CREATE(ADORec);

      ADORec.Open(sSQL,ADOConn);
      ADORec.MoveFirst;
      WHILE NOT ADORec.EOF DO BEGIN

         mytable.INIT;
         EVALUATE(mytable.BuchungsID,FORMAT(ADORec.Fields.Item('BUCHUNG_ID').Value));

         mytable."BenutzerID"  := FORMAT(ADORec.Fields.Item('BENUTZER').Value);

         CASE mytable.Funktionscode OF
              1:    BEGIN
                    mytable.Postenart     := mytable.Postenart::Zugang;
                    END;
              2:    BEGIN
                    mytable.Postenart     := mytable.Postenart::Abgang;
                    mytable.Menge         := -mytable.Menge;
                    END;
             99:    BEGIN
                    mytable.Postenart     := mytable.Postenart::Fehlteil;
                    mytable.Menge         := -mytable.Menge;
                    END;
         END;

         {and much more fields ...}

         mytable.INSERT;

         ADORec.MoveNext;

      END;

      ADORec.Close;
      ADOConn.Close;

      CLEAR(ADORec);
      CLEAR(ADOConn);

The data types of the SQL Table "Navision" are:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NAVISION](
	[BUCHUNG_ID] [int] IDENTITY(1,1) NOT NULL,
	[FKTCODE] [int] NOT NULL,
	[BUCHUNGSDATUM] [datetime] NULL,
	[BENUTZER] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
	[ARTIKEL] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
	[MENGE] [float] NULL,
	[WERT] [float] NULL,
	[AUFTRAG] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[AUFTRAGSINFO] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
	[AUFTRAGSPOSINFO] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
	[POSITIONSNR] [int] NULL,
	[LO] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[LO_KOSTENSTELLE] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[CHARGE] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
	[HOSTIDENT] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[SOLLMENGE] [float] NULL,
	[BESTAND] [float] NULL,
	[INFO1] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
	[INFO2] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
	[INFO3] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
	[INFO4] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
	[INFO5] [varchar](80) COLLATE Latin1_General_CI_AS NULL,
	[LAGER] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[REGAL] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[FACHBODEN] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[EINTEILUNG] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[POSITION] [int] NULL,
	[TIEFE] [int] NULL,
	[POS_NAME] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[TIEFEN_NAME] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
	[POS] [int] NULL,
	[DEEP] [int] NULL,
	[CONFIRMSTATUS] [int] NULL,
	[PICKQUANT] [float] NULL,
	[BESCHREIBUNG1] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
	[BESCHREIBUNG2] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
	[LAGERBESTAND] [float] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Where here does the problem hang?

Can anyone help me?

Greetings from Germany

brainstocker

brainstocker

The Problem is a NULL Value!!

I use this program code around the problem to solve:

this is part the Progamm code further above

VAR
buchid@1000000010 : variant;

buchid := ADORec.Fields.Item('BUCHUNG_ID').Value;

If buchid.ISTEXT then
EVALUATE(mytable.BuchungsID,FORMAT(ADORec.Fields.Item('BUCHUNG_ID').Value))
else 
mytable.BuchungsID := 'xxx';

This problem is solved !!

Yours sincerely

brainstocker

kriki

[Topic moved from Navision Attain forum to SQL General forum]