Insert Data into Navision from Sql Server 7

Business Central/NAV Developers Forum
1

Hi, I have a Navision 3.6 database ( not Sql server ). There is a need to connect this navision database to a Sql Server 7.0 database and insert data from Sql Server table(tablename SqlData1) into Navision database table( tablename NavisionData ) after every 5 min. What is the best way to do it? Thanks, Meenakshi (P.S. In this case, user doesn’t want to use dataports.)

2

Well… the best way to do it is telling your customer to contact us at Western Computer for doing it… [:P]… but you can also use an automation on the ADO for connecting to the sql server and retrieving the data… it’s up to you [;)] Regards,

3

Some sample code will be helpful, if anyone is willing to share[:)] Is there any specific automation ( for ADO) for version 7.0 of sql server or it doesn’t matter?

4

For the automation… check on your navision custom controls the Microsoft ADO Data control… I think there were a couple of samples on how to use that automation in previous posts…

5

NameDataType Subtype ADO_Connection Automation ‘Microsoft ActiveX Data Objects 2.7 Library’.Connection ADO_RecordSet Automation ‘Microsoft ActiveX Data Objects 2.7 Library’.Recordset NewItem Record Newitem ------------------------------------------ CREATE(ADO_Connection); ADO_Connection.Open(‘Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=DATABASENAME’,‘USERID’,‘PASSWORD’); CREATE(ADO_RecordSet); ADO_RecordSet.Open(‘TABLENAME’, ADO_Connection,1,3); IF NOT ADO_RecordSet.EOF THEN ADO_RecordSet.MoveFirst; IF NOT ADO_RecordSet.EOF THEN REPEAT IF NewItem.GET(ADO_RecordSet.Fields().Item(‘ItemNo’).Value) THEN BEGIN NewItem.Description := ADO_RecordSet.Fields().Item(‘Description’).Value; NewItem.MODIFY(TRUE); END ELSE BEGIN NewItem.“No.” := ADO_RecordSet.Fields().Item(‘Itemno’).Value; NewItem.Description := ADO_RecordSet.Fields().Item(‘Description’).Value; NewItem.INSERT(TRUE); END; ADO_RecordSet.MoveNext; UNTIL ADO_RecordSet.EOF; ADO_Connection.Close; CLEAR(ADO_RecordSet); CLEAR(ADO_Connection);

6

Thanks. This is very helpful.

7

In this above code the Item no is specify for what, please clear me .

Thanks,

Jai