Forum

CREATE   TABLE [dbo].[gbkmut](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [bkjrcode] [smallint] NULL,
   [reknr] [char](9) NULL,
   [datum] [datetime] NULL,
   [periode] [char](3) NULL,
   [bkstnr] [varchar](20) NULL,
   [dagbknr] [char](10) NULL,
   [oms25] [varchar](60) NULL,
   [bdr_hfl] [float] NOT NULL DEFAULT ((0.0)),
   [btw_code] [char](6) NULL,
   [btw_bdr_3] [float] NOT NULL DEFAULT ((0.0)),
   [btw_grond] [float] NOT NULL DEFAULT ((0.0)),
   [tegreknr] [char](9) NULL,
   [debnr] [char](12) NULL,
   [crdnr] [char](12) NULL,
   [kstplcode] [char](10) NULL,
   [kstdrcode] [char](10) NULL,
   [aantal] [float] NOT NULL DEFAULT ((0.0)),
   [valcode] [char](3) NULL,
   [exvalcode] [char](3) NULL,
   [koers] [float] NOT NULL DEFAULT ((0.0)),
   [wisselkrs] [float] NOT NULL DEFAULT ((0.0)),
   [koers3] [float] NOT NULL DEFAULT ((0.0)),
   [bdr_val] [float] NOT NULL DEFAULT ((0.0)),
   [dbk_verwnr] [int] NOT NULL DEFAULT ((0)),
   [verwerknrl] [int] NOT NULL DEFAULT ((0)),
   [volgnr5] [char](5) NULL,
   [regel] [char](4) NULL,
   [regelcode] [char](1) NULL,
   [storno] [tinyint] NOT NULL DEFAULT ((0)),
   [bkstnr_sub] [varchar](20) NULL,
   [betcond] [char](2) NULL,
   [btwper] [float] NOT NULL DEFAULT ((0.0)),
   [oorsprong] [char](1) NULL,
   [vlgn_gbk2] [varchar](30) NULL,
   [btw_grval] [float] NOT NULL DEFAULT ((0.0)),
   [afldat] [datetime] NULL,
   [docnumber] [varchar](30) NULL,
   [docdate] [datetime] NULL,
   [exvalbdr] [float] NOT NULL DEFAULT ((0.0)),
   [entryorigin] [char](1) NULL,
   [vervdatkrd] [datetime] NULL,
   [vervdtkrd2] [datetime] NULL,
   [vervdatfak] [datetime] NULL,
   [kredbep] [char](1) NULL,
   [bdrkredbep] [float] NOT NULL DEFAULT ((0.0)),
   [bdrkredbp2] [float] NOT NULL DEFAULT ((0.0)),
   [betaalref] [varchar](20) NULL,
   [stat_nr] [smallint] NOT NULL DEFAULT ((0)),
   [btw_nummer] [varchar](20) NULL,
   [rapnr] [char](6) NULL,
   [raplist] [char](6) NULL,
   [artcode] [varchar](30) NULL,
   [faktuurnr] [char](8) NULL,
   [bud_vers] [char](8) NULL,
   [syscreated] [datetime] NOT NULL DEFAULT (getdate()),
   [syscreator] [int] NOT NULL DEFAULT ((0)),
   [ReminderCount] [int] NOT NULL DEFAULT ((0)),
   [sysmodified] [datetime] NOT NULL DEFAULT (getdate()),
   [ReminderLayout] [int] NOT NULL DEFAULT ((0)),
   [LastReminderDate] [datetime] NULL,
   [sysmodifier] [int] NOT NULL DEFAULT ((0)),
   [sysguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (newid()),
   [BlockItem] [tinyint] NOT NULL DEFAULT ((0)),
   [timestamp] [timestamp] NOT NULL,
   [CompanyCode] [char](3) NULL,
   [res_id] [int] NOT NULL DEFAULT ((0)),
   [TransactionType] [int] NOT NULL DEFAULT ((0)),
   [CurrencyCode] [char](3) NULL,
   [Rate] [float] NOT NULL DEFAULT ((0.0)),
   [bankacc] [varchar](34) NULL,
   [AmountCentral] [float] NOT NULL DEFAULT ((0.0)),
   [VatBaseAmountCentral] [float] NOT NULL DEFAULT ((0.0)),
   [PaymentMethod] [char](1) NULL DEFAULT ((0)),
   [VatAmountCentral] [float] NOT NULL DEFAULT ((0.0)),
   [CurrencyAliasAc] [char](3) NULL,
   [DocumentID] [uniqueidentifier] NULL,
   [facode] [varchar](20) NULL,
   [transtype] [char](1) NULL CONSTRAINT [DF_gbkmut_transtype]  DEFAULT ('N'),
   [transsubtype] [char](1) NULL CONSTRAINT [DF_gbkmut_transsubtype]  DEFAULT ('N'),
   [freefield1] [varchar](75) NULL,
   [freefield2] [varchar](75) NULL,
   [freefield3] [varchar](75) NULL,
   [freefield4] [float] NULL,
   [freefield5] [float] NULL,
   [project] [varchar](20) NULL,
   [DocAttachmentID] [uniqueidentifier] NULL,
   [cmp_wwn] [uniqueidentifier] NULL,
   [warehouse] [char](4) NULL,
   [orderdebtor] [uniqueidentifier] NULL,
   [PayrollSubtype] [char](4) NULL,
   [ReconcileNumber] [int] NULL,
   [warehouse_location] [char](10) NULL,
   [EntryGuid] [uniqueidentifier] NULL,
   [TransactionGuid] [uniqueidentifier] NULL CONSTRAINT [DF_gbkmut_TransactionGuid]  DEFAULT (newid()),
   [TransactionGuid2] [uniqueidentifier] NULL,
   [Checked] [tinyint] NOT NULL DEFAULT ((0)),
   [Reviewed] [tinyint] NOT NULL DEFAULT ((0)),
   [BankTransactionGUID] [uniqueidentifier] NULL,
   [StockTrackingNumber] [char](8) NULL,
   [StartTime] [datetime] NULL,
   [EndTime] [datetime] NULL,
   [ReportingDate] [datetime] NULL,
   [CashRegisterAccount] [varchar](34) NULL,
   [Original_Quantity] [float] NULL,
   [comp_code] [char](8) NULL,
   [Selcode] [char](2) NULL,
   [Unitcode] [char](8) NULL,
   [Pricelist] [varchar](15) NULL,
   [Discount] [float] NULL,
   [Shipment] [varchar](30) NULL,
   [IntTransportMethod] [varchar](10) NULL,
   [IntPort] [varchar](10) NULL,
   [IntSystem] [varchar](10) NULL,
   [IntTransA] [varchar](10) NULL,
   [IntStatNr] [varchar](9) NULL,
   [IntStandardCode] [varchar](10) NULL,
   [IntTransShipment] [varchar](10) NULL,
   [IntTransB] [varchar](10) NULL,
   [IntArea] [varchar](10) NULL,
   [IntLandISO] [varchar](3) NULL,
   [IntLandDestOrig] [varchar](3) NULL,
   [IntDeliveryMethod] [varchar](10) NULL,
   [IntStatUnit] [float] NULL,
   [IntWeight] [float] NULL,
   [IntComplete] [tinyint] NULL,
   [LinkedLine] [uniqueidentifier] NULL,
   [PayrollCosts] [tinyint] NULL,
   [TaxCode2] [char](3) NULL,
   [TaxCode3] [char](3) NULL,
   [TaxCode4] [char](3) NULL,
   [TaxCode5] [char](3) NULL,
   [TaxBasis2] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxBasis2]  DEFAULT ((0.0)),
   [TaxBasis3] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxBasis3]  DEFAULT ((0.0)),
   [TaxBasis4] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxBasis4]  DEFAULT ((0.0)),
   [TaxBasis5] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxBasis5]  DEFAULT ((0.0)),
   [TaxAmount2] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxAmount2]  DEFAULT ((0.0)),
   [TaxAmount3] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxAmount3]  DEFAULT ((0.0)),
   [TaxAmount4] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxAmount4]  DEFAULT ((0.0)),
   [TaxAmount5] [float] NOT NULL CONSTRAINT [DF_gbkmut_TaxAmount5]  DEFAULT ((0.0)),
   [StatisticalFactor] [float] NULL,
   [IntLandAssembly] [varchar](3) NULL,
   [backflush] [tinyint] NULL,
   [LastReminderLayout] [varchar](8) NULL,
   [Correction] [char](6) NULL,
   [IBTDeliveryNr] [varchar](8) NULL,
   [Routing] [varchar](10) NULL,
   [Step] [int] NULL,
   [Reasoncode] [varchar](30) NULL,
   [TransactionNumber] [varchar](20) NULL,
   [Type] [smallint] NULL,
   [Status] [tinyint] NULL,
   [UniqueSeqNo] [int] NULL,
   [CompleteOperation] [tinyint] NULL,
   [ImSerialNr] [varchar](22) NULL,
   [ImportationNr] [char](8) NULL,
   [RevaluationCurrency] [char](3) NULL,
   [LineType] [smallint] NULL,
   [Operation] [varchar](10) NULL,
   [ExternalNumber] [varchar](30) NULL,
   [ExternalNumberRecordID] [bigint] NULL,
   [RevaluationRate] [float] NULL,
   [PositionNumber] [varchar](20) NULL,
   [StatementDate] [datetime] NULL,
   [StatementNumber] [char](10) NULL,
   [IntrastatEnabled] [bit] NULL,
   [AllocationType] [char](1) NULL,
   [OfficialExchangeRate] [float] NULL CONSTRAINT [DF_gbkmut_OfficialExchangeRate]  DEFAULT ((0.0)),
   [OfficialAmountDC] [float] NULL CONSTRAINT [DF_gbkmut_OfficialAmountDC]  DEFAULT ((0.0)),
   [OfficialBasisDC] [float] NULL CONSTRAINT [DF_gbkmut_OfficialBasisDC]  DEFAULT ((0.0)),
   [ScheduledResources] [int] NULL,
   [ManualStartLock] [bit] NULL,
   [ManualStopLock] [bit] NULL,
   [FirmZoneLock] [bit] NULL,
   [Division] [smallint] NULL,
   [OperationQuantityComplete] [float] NULL,
 CONSTRAINT [PK_gbkmut] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [dbo].[gbkmut] ([ID], [bkjrcode], [reknr], [datum], [periode], [bkstnr], [dagbknr], [oms25], [bdr_hfl], [btw_code], [btw_bdr_3], [btw_grond], [tegreknr], [debnr], [crdnr], [kstplcode], [kstdrcode], [aantal], [valcode], [exvalcode], [koers], [wisselkrs], [koers3], [bdr_val], [dbk_verwnr], [verwerknrl], [volgnr5], [regel], [regelcode], [storno], [bkstnr_sub], [betcond], [btwper], [oorsprong], [vlgn_gbk2], [btw_grval], [afldat], [docnumber], [docdate], [exvalbdr], [entryorigin], [vervdatkrd], [vervdtkrd2], [vervdatfak], [kredbep], [bdrkredbep], [bdrkredbp2], [betaalref], [stat_nr], [btw_nummer], [rapnr], [raplist], [artcode], [faktuurnr], [bud_vers], [syscreated], [syscreator], [ReminderCount], [sysmodified], [ReminderLayout], [LastReminderDate], [sysmodifier], [sysguid], [BlockItem], [timestamp], [CompanyCode], [res_id], [TransactionType], [CurrencyCode], [Rate], [bankacc], [AmountCentral], [VatBaseAmountCentral], [PaymentMethod], [VatAmountCentral], [CurrencyAliasAc], [DocumentID], [facode], [transtype], [transsubtype], [freefield1], [freefield2], [freefield3], [freefield4], [freefield5], [project], [DocAttachmentID], [cmp_wwn], [warehouse], [orderdebtor], [PayrollSubtype], [ReconcileNumber], [warehouse_location], [EntryGuid], [TransactionGuid], [TransactionGuid2], [Checked], [Reviewed], [BankTransactionGUID], [StockTrackingNumber], [StartTime], [EndTime], [ReportingDate], [CashRegisterAccount], [Original_Quantity], [comp_code], [Selcode], [Unitcode], [Pricelist], [Discount], [Shipment], [IntTransportMethod], [IntPort], [IntSystem], [IntTransA], [IntStatNr], [IntStandardCode], [IntTransShipment], [IntTransB], [IntArea], [IntLandISO], [IntLandDestOrig], [IntDeliveryMethod], [IntStatUnit], [IntWeight], [IntComplete], [LinkedLine], [PayrollCosts], [TaxCode2], [TaxCode3], [TaxCode4], [TaxCode5], [TaxBasis2], [TaxBasis3], [TaxBasis4], [TaxBasis5], [TaxAmount2], [TaxAmount3], [TaxAmount4], [TaxAmount5], [StatisticalFactor], [IntLandAssembly], [backflush], [LastReminderLayout], [Correction], [IBTDeliveryNr], [Routing], [Step], [Reasoncode], [TransactionNumber], [Type], [Status], [UniqueSeqNo], [CompleteOperation], [ImSerialNr], [ImportationNr], [RevaluationCurrency], [LineType], [Operation], [ExternalNumber], [ExternalNumberRecordID], [RevaluationRate], [PositionNumber], [StatementDate], [StatementNumber], [IntrastatEnabled], [AllocationType], [OfficialExchangeRate], [OfficialAmountDC], [OfficialBasisDC], [ScheduledResources], [ManualStartLock], [ManualStopLock], [FirmZoneLock], [Division], [OperationQuantityComplete])
VALUES (6775, 2015, N'     4111', '20150115', N'  1', N'15100003', N' 10', N'[B]Salary Quoc Anh in Jan.15 SRHR/SOF/HN/15/01[/B]', [B]17233249[/B], N'0', 0, 17233249, NULL, NULL, NULL, N'158/TA01', N'15/01', 0, N'VND', N'VND', 1, 1, 1, 17233249, 0, 0, N'    4', N'  28', N'B', 0, NULL, NULL, 0, N'A', NULL, 17233249, '20150115', N'OBA/1/11', '20150115', 0, N'N', NULL, NULL, '20150115', N'K', 0, 0, NULL, 0, NULL, NULL, NULL, NULL, N'10000033', NULL, '20150522 16:31:55', 1, 0, '20150629 11:57:08', 0, NULL, 1, N'{428B9192-89DF-4CD4-B147-FE97EFC102DE}', 0, '19000101 00:05:56.690', N'006', 1, 100, N'VND', 1, NULL, 17233249, 17233249, N'', 0, N'VND', NULL, NULL, N'N', N'N', NULL, NULL, NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'{E939AAC3-0F71-4382-9250-4812ED077C12}', N'{82162C65-8969-4E51-8128-E4C9DCB192F1}', NULL, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, NULL, 0, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'0', NULL, NULL, 0, 0, 0, NULL, NULL, NULL, NULL, 6, NULL)
GO

&AtServer
Procedure CheckODBC_ADO()
   ConnectString = "Provider=SQLOLEDB;Data Source=DESKTOP-3VAE73V;Initial Catalog=050;User ID=sa;Password=Htt123456";

   Сonnect ADO = New COMObject("ADODB.Connection");
   СonnectADO.ConnectionString = ConnectString;
   СonnectADO.Open();

   RS = New COMObject("ADODB.Recordset");
   Try
      RS.Open("SEL ECT ID, bdr_hfl FR OM dbo.gbkmut", СonnectADO );
      Except
        Message ("Không thể kết nối tới CSDL");
        Return;
   EndTry;

   Tb = New ValueTable();
   Tb.Columns.Add("ID");
   Tb.Columns.Add("bdr_hfl_Odbc");
   Tb.Columns.Add("bdr_hfl_Ado");
   Tb.Columns.Add("Other");
   
   While RS.EOF() = 0 do
      
      Row = Tb.Add();
      Row.ID = RS.Fields("ID").Value;
      Row.bdr_hfl_Ado = RS.Fields("bdr_hfl").Value;
        RS.MoveNext();
   EndDo;
       
   RS.Close();
   СonnectADO.Close();
   
   Sqlstr = new Query("SELECT
                      |   dbo_gbkmut.ID,
                      |   dbo_gbkmut.bdr_hfl
                      |FROM
                      |   ExternalDataSource.Mcnv.Table.dbo_gbkmut AS dbo_gbkmut");
                  
   Lb1 = Sqlstr.Execute().Select();
   
   While lb1.Next() Do
      Row = Tb.Add();
      Row.Id = lb1.ID;
      Row.bdr_hfl_Odbc = Round(Lb1.bdr_hfl, 2);
   EndDo;
   
   Tb.GroupBy("ID","bdr_hfl_Ado, bdr_hfl_Odbc, Other");
   
   For Each Line In Tb Do 
   
      If Line.bdr_hfl_Ado <> Line.bdr_hfl_Odbc Then
         Message(string(Line.ID)+ " - " +String(Line.bdr_hfl_Ado) + " - " +String(Line.bdr_hfl_Odbc) + " Other:" + string(Line.bdr_hfl_Ado-Line.bdr_hfl_Odbc) );
      EndIf;   
   EndDo;   
   
   
EndProcedure