Navision and SQL ADO Connection

Dear All Developer,

If you are integrating Navision and Sql server then follow the below instructions:-

1) There should not be other client's version installed on the system. Otherwise Navision Client will restart again and again.
2) user the below automation variables in

Name DataType Subtype Length
ADO_Connection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ADO_RecSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
ADOStream Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Stream
ADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command

3) After this use the below code on Puch button or the suitable trigger wherever its required:----
// Create a connection string
ConnectionString := 'Driver={SQL Server};PROVIDER=SQLOLEDB;SERVER=SystemName;DATABASE=SQLDataBaseName;UID=UserID;PWD=Password';
// After This
BEGIN
IF ISCLEAR(ADO_Connection) THEN
CREATE(ADO_Connection);
IF ISCLEAR(ADO_RecSet) THEN
CREATE(ADO_RecSet);
IF ISCLEAR(ADOStream) THEN BEGIN
IF NOT CREATE(ADOStream) THEN BEGIN
ERROR('Cannot create ADO Stream automation variable.');
END;
END;
ADO_Connection.Open(ConnectionString);
MESSAGE('Conneciton Created');
END;

//Write the query to fetch Data

ADO_RecSet.Open('Select name,Convert(Varhcasr,ISNULL(salary,0)) from master',ADO_Connection);

//Note i have tried most what i did not get any way to insert decimal or integer values to insert in Navision from sql. So i make all the fields code of the table and insert the data in Navision.
// To convert integer or decimal data in code i use Covert() function.

Note- If value of int or decimal is blank then it show a problem such type like variable does not supported by Database.
So use IsNull() function in query.


IF (ADO_RecSet.BOF) AND (ADO_RecSet.EOF) THEN
MESSAGE('Product Records Not Found')
ELSE BEGIN
ADO_RecSet.MoveFirst;
MESSAGE('Found First');
END;

WHILE NOT ADO_RecSet.EOF DO BEGIN
LocationNew.INIT;
LocationNew.Code:=(ADO_RecSet.Fields.Item(0).Value);
LocationNew.Salary:=ADO_RecSet.Fields.Item(1).Value;

LocationNew.INSERT;
ADO_RecSet.MoveNext();
END;

ADO_Connection.Close;
MESSAGE(Text50001);


This is complete code for integration.

Thanks and Regards
Pradeep Kumar
pradeepbhardwaj478@gmail.com
pradeepkmr478@gmail.com
Mindshell Infotech Pvt. Ltd. Delhi (India)