ADO in Navision

February 20th, 2008ADO, Automations 4 Comments

With ADO you can execute SQL Scripts and Stored Procedures from Navision.

To use ADO in Navision, you have to use the automation “Microsoft ActiveX Data Objects 2.8 Library”

Lets take a closer on how to make a SQL connection and perform a SQL Query in Navision.

First you have to make a connection to the SQL Server:

  |  copy code |? 
ConnectionString := 'PROVIDER=SQLOLEDB;SERVER=SQLServer;DATABASE=SQLDatabase;UID=SQLUserID;PWD=SQLPwd';
 
ADOConnection.ConnectionString(ConnectionString);
ADOConnection.Open;
 
where ADOConnection is defined as 'Microsoft ActiveX Data Objects 2.8 Library'.Connection

Now you have a open connection to the SQL Server and are ready to execute scripts.

  |  copy code |? 
    
SQLString := 'SELECT [Name],[Customer No_] as CustomerNo ' +
             'FROM [Customer] WHERE [Customer No_] < 100';
 
ADORecordSet := ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
 





Where ADORecordSet is defined as “Microsoft ActiveX Data Objects 2.8 Library’.Recordset”, RecordsAffected returns the number of records fetched and RSOption sets how the provider should evaluate the commandtext parameter (SQLString).


Now that the query has been performed, the query result is returned into a the ADO RecordSet. Which now can be parsed.


  |  copy code |? 
ADORecordSet.MoveFirst;
REPEAT
   TmpTable.Name := ADORecordSet.Fields.Item('Name').Value;
   TmpTable."No." := ADORecordSet.Fields.Item('CustomerNo').Value;
   TmpTable.INSERT;
   ADORecordSet.MoveNext;
UNTIL ADORecordSet.EOF;


When you are done using the ADORecordSet and the ADOconnetion, you have to close them:


  |  copy code |? 
ADORecordSet.Close;
ADOconnection.Close;


      


    




	
	







	
4 Responses to “ADO in Navision”




	
    



	


		
  1. 

			Rob Kaplan Says:

			
			
    



			October 30th, 2008 at 17:18 



			
    This was very helpful.  Thanks.  Do you have any sample code on how to update a field from the recordset in NAV.  So in the Repeat Until, i want to update a field that the record has been processed.  Any help would be greatly appreciated.
    

    Thanks,
    
rob
    



		
    2. 



	


		
  2. 

			iby Says:

			
			
    



			November 2nd, 2008 at 17:55 



			
    To update a RecordSet you can use the Command Update.
    

    It is though not always possible to update a RecordSet, so during the Repeat you have to check if its possible. 
    

      |  copy code |? 
    IF ADORecordset.Supports(adUpdate) THEN BEGIN
       ADORecordset.Fields.Item('Name').Value := 'New Name';
       ADORecordset.Update;
    END;
     
    where adUpdate = 16809984.

    
If the ADORecordset does not support Update, then you can trick the update by using a SQL Query for this.
    

    Example:
    

      |  copy code |? 
    IF ADORecordset.Supports(adUpdate) THEN BEGIN
       ADORecordset.Fields.Item('Name').Value := 'New Name';
       ADORecordset.Update;
    END ELSE BEGIN
       UpdateSQLStr := 'UPDATE [Customer] ' +
                      'SET [Name] = "New Name" '  +
                      'WHERE [Customer No_] = ' + FORMAT(ADORecordset.Fields.Item(0).Value);
       CREATE(ADORecordset2);
       ADORecordset2 := ADOConnection.Execute(UpdateSQLStr,RecordsAffected,RSOption);
       CLEAR(ADORecordset2);
    END;
    



		
    3. 



	


		
  3. 

			gordon riding Says:

			
			
    



			August 11th, 2009 at 14:51 



			
    I have set up a connection and sql execution as above and
    
do connect to the sql database and get the error ‘invalid object name’ when using the customer table in the adoconnection.execute call.
    

    So frustrating!!
    

    Looking on the web it seems to be a schema permission? 
    

    If you could shed any light on this I would be very pleased!
    



		
    4. 



	


		
  4. 

			iby Says:

			
			
    



			September 7th, 2009 at 18:26 



			
    Hi Gordon,
    

    This can be due to a number of reasons:
    
- the user you are connecting with, does not have the right permissions
    
- you are not connected with the user that you expect
    
- you are missing an owner prefix, when referencing to the object
    
- you are in fact misspelling the objects name
    

    Let’s assume that you are using the right user with correct permissions. So have you checked, that you does not missing any prefixes? Ex. dbo.[CompanyName].[Customer]?
    



		
    5. 



	


	




 








Leave a Reply






You must be logged in to post a comment.











    
 




	


  




  
  
 










  
Icons by N.Design Studio.  Designed By Ben Swift and Customized by Ingrid Byllemos. Powered by WordPress


  
Entries RSS Comments RSS Log in