Reading external databases through ADO

AdministratorAdministrator Member, Moderator, Administrator Posts: 2,495
edited 2012-06-01 in Download section
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.

Comments

  • DenSterDenSter Member Posts: 8,304
    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.
  • ajaybabuChajaybabuCh Member Posts: 208
    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.
    Ajay
  • ASTAST Member Posts: 108
    Hello,
    do you have any example how to write to external database?
  • WaldoWaldo Member Posts: 3,412
    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=

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Steve_ContrisSteve_Contris Member Posts: 114
    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.
    What would Elvis do?
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    What message do you get? Are you an enduser?
  • Steve_ContrisSteve_Contris Member Posts: 114
    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.
    What would Elvis do?
  • ara3nara3n Member Posts: 9,255
    your customer license does not have those objects. You need to purchase them or rename them to a lower range.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • AsallaiAsallai Member Posts: 141
    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
  • itinkiamitinkiam Member Posts: 37
    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"
  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • nXqdnXqd Member Posts: 39
    Thanks for great sharing, this actually helps me a lot with standard and complete code reference :)
Sign In or Register to comment.