To read external databases through ADO

Aravindh_Navision

Hi Friends,

My task: I am trying to integrate Navision with another ERP tool called MAPICS which works in AS400 - DB2. I need to fetch a data from MAPICS and insert it into Navision.

I just did little modification in Milklo's Reading external databases through ADO download. I downloaded it in the following link: http://mibuso.com/dlinfo.asp?FileID=589.

As a result, I could able to connect, but not able to fetch the data, even I am not getting the messages too to see the FieldValue. And no error is throwing out.

Can anyone tell me where I am doing wrong and what I need to do?

Thanks in Advance

Variables declared: (local)

ADOConnection - Automation - 'Microsoft ActiveX Data Objects 2.8 Library'.Connection	
ADORecSet - Automation - 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset	
ADOStream - Automation - 'Microsoft ActiveX Data Objects 2.8 Library'.Stream	
SQLString - Text - 1000
RecordsAffected - Text - 200
RSOption - Integer		
FieldValue - Text - 1024
TestTable - Record - ADO_Connection_Test_Table

TestTable fields:

1 - No. - Text - 15	
3 - Price - Decimal		
4 - RecID - Text - 30	
5 - Weight - Decimal

Modified code

~ ITEMASA is the master table in AS400 DB2 database.
~ ITNBR is a character field
~ PRICE is a decimal field

IF ISCLEAR(ADOConnection) THEN BEGIN
      IF NOT CREATE(ADOConnection) THEN BEGIN
      ERROR('Cannot create ADO Connection automation variable.');
   END;
END;

IF ISCLEAR(ADORecSet) THEN BEGIN
      IF NOT CREATE(ADORecSet) THEN BEGIN
         ERROR('Cannot create ADO Recordset automation variable.');
   END;
END;

IF ISCLEAR(ADOStream) THEN BEGIN
      IF NOT CREATE(ADOStream) THEN BEGIN
         ERROR('Cannot create ADO Stream automation variable.');
   END;
END;

ADOConnection.ConnectionString(
'Provider=MSDASQL.1;Password=ARAVINDH;Persist Security Info=True;User ID=ARAVINDH;Data Source=TestConn');

ADOConnection.Open;
RecordsAffected := '';
RSOption := 0; 

SQLString := 'Select ITNBR, PRICE from AMFLIBT.ITEMASA';
ADORecSet := ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
ADORecSet.MoveFirst;

REPEAT
   IF ADORecSet.Fields.Item('ITNBR').Value.ISTEXT THEN BEGIN
      ADOStream.Open;
      ADOStream.WriteText(ADORecSet.Fields.Item('ITNBR').Value);
      ADOStream.Position:= 0;
      FieldValue:= ADOStream.ReadText;
      ADOStream.Close;
      MESSAGE('%1',FieldValue);
   END;

   IF ADORecSet.Fields.Item('PRICE').Value.ISDECIMAL THEN BEGIN
      ADOStream.Open;
      ADOStream.WriteText(ADORecSet.Fields.Item('PRICE').Value);
      ADOStream.Position:= 0;
      FieldValue:= ADOStream.ReadText;
      ADOStream.Close;
      MESSAGE('%1',FieldValue);
   END;

   ADORecSet.MoveNext;
UNTIL ADORecSet.EOF;
ADORecSet.Close;
ADOConnection.Close;

PeterD

(I'am a bit rusty on ADO... :-$)

I don't think I ever used an ADOStream to get or set values. What happens if you use

   IF ADORecSet.Fields.Item('ITNBR').Value.ISTEXT THEN BEGIN
      FieldValue := ADORecSet.Fields.Item('ITNBR').Value;
      MESSAGE('%1', FieldValue);
   END;

Aravindh_Navision

Thanks Peter. Actually this code was not mine. I downloaded an object from downloading section in mibuso: Reading external databases through ADO. While running I am not getting any result and no error also is popping up.

I used your modified code also. Even then I am not getting any result or error message

Saalek

Hi

Change this code

ADORecSet := ADOConnection.Execute(SQLString,RecordsAffected,RSOption);

and use

ADORecSet.open Adoconnection,Sqlstring

(I don't remembrer if th connection is in first position or in the second)

I think, you don't relationed your current active ADO Connection with the Recordset.
There are few years I don't use ADO, but i think, this must work. If I can, I'll revise old databases to copy ADO code.

Bye

Aravindh_Navision

Hai Saalek,

Thanks for your response. I modified as you mentioned and tried. By giving that piece of code, am getting the error which I have attached.

It will be really useful for me, if you revise with your older database and share the details with me. Your quick response is highly appreciated since I am running short of time.

Thanks in advance.

Aravindh_Navision

Hi FRiends,

Can anyone help me in the above issue or tell where I am doing wrong?

Thanks.