Reading external databases through ADO

Administrator

Reading external databases through ADO
This is an example of reading exernal databases through ADO (in Navision).

This example addresses typical problems:

- NULL values
- type conversion
- spaces in result set field names

http://www.mibuso.com/dlinfo.asp?FileID=589

Discuss this download here.

DenSter

Thank you very much Miklos, this sample has helped me jump start an ADO modification that I am working on. I've used your sample code to write an ADO Helper codeunit that translates incoming values into Navision datatype in such a way that you don't get any errors, even if you select the wrong datatype. Now retrieving ADO RecSet values is one function call, makes the code a lot more readable.

ajaybabuCh

Hi

Instead of hard coding the filed names of sql db table

I am storing the field names in an array. and try to get the values

as follows


ADORecSet.Open(SQLString,ADO);
ADOFieldSet:=ADORecSet.Fields;

IF NOT ADORecSet.EOF THEN BEGIN
FOR i:= 1 TO Index-1 DO BEGIN
SQLFieldValues:=FORMAT(ADOFieldSet.Item('+
SQLFieldNames+').Value);
END;
END;

But its throwing me the error

The call to member item failed. ADODB.fields returned the following:
Item cannot be found in the collection corresponding to the requested name or ordinal

can't we make this process dynamically.

AST

Hello,
do you have any example how to write to external database?

Waldo

It's mainly the execute statement you need.
Here are some posts with the execute statement:
http://www.mibuso.com/forum/viewtopic.php?t=9995&highlight=

Steve_Contris

I downloaded this example and imported the codeunit but somehow do not have the permissions to read it - I am super user in the database so I have all the permissions possible as far as I know. Also, don't I also need to install some activex component to go along with this? If so, what is it and where do I find it.

Marije_Brummel

What message do you get? Are you an enduser?

Steve_Contris

I am admin user with super users permissions (role ID of SUPER). If I try to access it in any way - run it open it to design, or even try to delete it, it tells me:

You do not have permission to run the 'ADO test' Codeunit.

If I to open for design it says read instead of run.

ara3n

your customer license does not have those objects. You need to purchase them or rename them to a lower range.

Asallai

Hi All,

I read about many docs here, but I did not find about the schemas.
I would like to show up for the users the database tables (name, etc) to a temporary table after than the user can select a table from the current database. I've made the connection but how can I receive the table names in the database? :roll:
If any links here, it will be good too!
Thanks a lot!

A

itinkiam

Thank you so much for your posting

But I’m afraid I’m missing the most simple of concepts. When I run the solution I am returned an error: ](*,) “The length of the text string exceeds the size of the string buffer’.

I’m not sure if that is what is expected, or if I should correct something to eliminate the error. It seems that I am the only one with this problem. :oops: Can you help me understand the point that I am missing?

I'm running SQL2008r2, NAV5.01update2 with "Microsoft ActiveX Data Objects 2.8 Library"

ara3n

it looks like the returned value of the string is greater than 1024 characters. I suggest to change you sql statement or return only 1024 characters at a time.

nXqd

Thanks for great sharing, this actually helps me a lot with standard and complete code reference