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:
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.
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.
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:
ADORecordSet.Close;
ADOconnection.Close;
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
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.
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:
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;
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!
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]?
Aw, this was a very good post. Spending some time and actual effort to generate
a good article… but what can I say… I hesitate a lot and don’t manage to get nearly anything done.