mibuso.com

Microsoft Business Solutions online community
It is currently Thu Oct 23, 2014 7:14 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: Reading external databases through ADO
PostPosted: Fri Apr 28, 2006 4:08 pm 
Offline
Site Admin

Joined: Sun Nov 07, 1999 8:01 am
Posts: 2070
Location: Wilrijk, Belgium
Country: Belgium (be)
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.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 14, 2006 2:43 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7991
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject:
PostPosted: Tue Dec 19, 2006 3:47 am 
Offline

Joined: Wed Jul 20, 2005 4:49 pm
Posts: 201
Location: india
Country: India (in)
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[i]:=FORMAT(ADOFieldSet.Item('+
SQLFieldNames[i]+').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


Top
 Profile E-mail  
 
 Post subject: writing to external database
PostPosted: Mon Apr 30, 2007 9:43 am 
Offline

Joined: Mon Jan 08, 2007 8:18 pm
Posts: 77
Location: Krakow
Country: Poland (pl)
Hello,
do you have any example how to write to external database?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Apr 30, 2007 11:01 am 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Fri Dec 06, 2002 11:50 am
Posts: 3397
Location: Kontich, Belgium
Country: Belgium (be)
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 02, 2007 8:43 pm 
Offline

Joined: Thu May 18, 2006 8:13 pm
Posts: 84
Location: Olympia WA
Country: United States (us)
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?


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 02, 2007 10:17 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 4182
Location: Olst
Country: Netherlands (nl)
What message do you get? Are you an enduser?

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Specialist

Author of the book : Microsoft Dynamics NAV Application Design

MY BLOG : http://markbrummel.wordpress.com


Top
 Profile E-mail WWW  
 
 Post subject:
PostPosted: Wed May 02, 2007 10:30 pm 
Offline

Joined: Thu May 18, 2006 8:13 pm
Posts: 84
Location: Olympia WA
Country: United States (us)
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?


Top
 Profile  
 
 Post subject:
PostPosted: Thu May 03, 2007 1:40 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 9059
Location: 3rd rock from sun
Country: United States (us)
your customer license does not have those objects. You need to purchase them or rename them to a lower range.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: OpenSchema and get the tables
PostPosted: Tue Sep 11, 2007 11:58 am 
Offline

Joined: Fri Sep 10, 2004 12:36 pm
Posts: 138
Location: Budapest
Country: Hungary (hu)
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


Top
 Profile E-mail  
 
 Post subject: Re: Reading external databases through ADO
PostPosted: Wed Mar 14, 2012 8:02 pm 
Offline

Joined: Thu Feb 08, 2007 4:01 am
Posts: 27
Location: San Diego, CA
Country: United States (us)
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"


Top
 Profile  
 
 Post subject: Re: Reading external databases through ADO
PostPosted: Wed Mar 14, 2012 9:00 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 6:11 pm
Posts: 9059
Location: 3rd rock from sun
Country: United States (us)
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.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Reading external databases through ADO
PostPosted: Fri Jun 01, 2012 10:58 am 
Offline

Joined: Wed May 02, 2012 10:21 am
Posts: 33
Country: Vietnam (vn)
Thanks for great sharing, this actually helps me a lot with standard and complete code reference :)


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: