I have to access an external database (SQL-Server) from inside Navision (4.00). I use Microsoft ActiveX Data Objects 2.8 Library to do the job and everything works fine, exept one thing: When I try to get a SQL-Decimal (9-bit float) into a Navision-Decimal (8-bit float) I run into an error telling me that these types are not compatible. In a test database I changed the SQL data type to ‘Money’ and something like EVALUATE(navDecimal,FORMAT(adoRS.Fields.Item(‘Amount’).Value)); worked fine. Unfortunately I cannot change the data type in the customers database. So I have to find a way to access this SQL-Decimal field. Any ideas?
Hi Josef, sorry no ideas for your question right now. But I am attempting a similar thing. I was hoping to pick your brains quickly about your connection method. I want to use the same object library to connect to a SQL db. here is my code (very simple at the moment[:D]): CREATE(SQLConnection); SQLConnection.Open(‘driver={SQL Server};server=(localhost);uid=navision;pwd=navision;database=Northwind’); SQLRecordSet.Open(‘SELECT top 10 * FROM Customers’,SQLConnection,3,3); MESSAGE(’%1 records retrieved.’,SQLRecordSet.RecordCount); CLEAR(SQLConnection); I keep getting error (after it times out I presume): SQL Server does not exist or access denied? I created the user on the SQL DB and tested the script in Query Analyzer. (BTW its MSDE). Thanks. Appologies for posting this here, I did not think this warranted a brand new topic!
Seems to be something wrong with your connection string. Don´t ask me what! I´m using the ConnectionString property of the connection object. Something like adoConn.ConnectionString := STRSUBSTNO (‘Provider=%1;Persist Security Info=%2;Database=%3;User ID=%4;password=%5;Data Source=%6’, ‘SQLOLEDB.1’,‘False’,‘Northwind’,‘myuser’,‘mypw’,‘MyServer’); adoConn.Open(); adoRS.Open(‘select top 10 * from Customers’,adoConn,3,3,8); //Parameters of Open method: Query Text, //Connection Object to use, //Cursor Type 3=“adOpenStatic”, //Lock Type 3=“adLockOptimistic”, //Command Type 8=“adCmdUnknown” MESSAGE(’%1 records retrieved.’,adoRS.RecordCount); and the message says: 10 records retrieved.
Hi Just to update my last post: The following worked for me. sql := ‘Provider=’‘sqloledb’’;’ + ‘Data Source=’’’ + MYSQLSERVER + ‘’’;’ + ‘Initial Catalog=’’’ + MYSQLDB + ‘’’;’ + ‘User ID=’’’ + XXXX + ‘’’;’ + ‘Password=’’’ + XXXX + ‘’’’; SQLConnection.Open(sql); sql := ‘SELECT TOP 10 * from Cusomters’; SQLRecordSet := SQLConnection.Execute(sql); etc… Thanks.
Hi, You can use CAST function in the select statement to change the datatype to money: SELECT CAST(Amount AS MONEY) AS Amount FROM … Bjarne
That’s a sneaky one [:D]
Bjarne Astrup wrote: >You can use CAST function … tb@softsys.at wrote >>That’s a sneaky one… Sorry, that is only half of the truth. It works pretty good if youre just reading the recordset. If want to update it you will instantly discover that the cast makes it non updateable. My solution finally was to use 2 different recordsets, 1. Select cast (amount as money) as amount… With EVALUATE I put the amount field into a decimal variable If my code finds a record which has to be updated I use a command object or a second recordset object with the simple SQL like: 2. Update bla set amount = :MyVariable where DocNo = XY This will internaly cast a 8 bit variable to a 9 bit value and this is no problem.