ADO in Navision

code-dynamics

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:

 div |  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.

 div |  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.

 div |  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:

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

You can leave a response, or trackback from your own site.

4 Responses to “ADO in Navision”

  1. Rob Kaplan says:

    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. iby says:

    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.

     div |  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:

     div |  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. gordon riding says:

    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. iby says:

    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]?

Leave a Reply


7 × = sixty three