I’ve come across a problem with updating Navision CODE type fields from outside, such as through SQL Enterprise Manager. I can quite happily insert lowercase text but when I go into Navision and look at it I crash and burn, the only solution is to go back to Enterprise Manager and change it to uppercase. Is there any way to tell from outside Navision whether a field is code or text? They are both stored in SQL as varchar, but there must be a table somewhere containing the field definitions. Does anyone know where? Dan.
Hi, While inserting the data in code field(outside from Navision) prefix CHAR(0) with data.Might be it will help.
Dan, Version 2.6f Create a form with grid in Navision and use table no. 2000000041. This is the Fields table and will let you know the Type field. Haven’t figured out how to determine type via Enterprise Manager–if you do, please let us know. Bill
I already solved this. See: http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=3496 You basically need to add Chr(0) to the string, such as: fld.Value = Trim(fld.Value) + Chr(0) The length of field must not exceed that of Navision’s. You also are going to have problems with blank Date fields; only if you “marashall” a recoredset from one process to another in the same or different computer(By using DCOM or RDO for instance). Navision 2.60.D or below will always fail when marshalled when the recordset has a blank date field. Navision 2.60.E or above will represent blank dates as 1/1/1753. Search the forum for related postings, use “naji” or “shaman” to find messages related to this. Naji Shaman
Just an additional note. If you do have this error, it can be fixed easily in C/Side by simply assigning the field to itself, eg: create a loop to do the followingMycodeField := MyCodeField; MODIFY;
Thanks for your suggestions guys, I’ll try these things out. Naji your idea has considerable merit, I’ll try to update all fields like that. Bill yes that is the table that I was looking for, it’s obviously something that Navision builds on the fly from who knows where, I was hoping it would be stored somewhere accessible. David yes that works, I wrote a fix program for our client when they first had the problem. Unfortunately this is another client who have their own “CRM” system which will update Navision as and when it sees fit - pretty scary thought isn’t it! If I find anything startling I’ll update this topic, meantime thanks for your time. regards, Dan.
I’ve had a little dig around in Enterprise Manager and the SysColumns table looks like the one that contains all the field definition information - except the distinction between Code and Text. Filtering on id=4741107 gives me what looks like the Sales Header table but apart from the lengths the definitions of code and text fields look identical. There must be another reference somewhere.
I use Visual Basic 6’s Data Environment Designer to see the field datatype. When I click on each field, the designer shows ADO datatype. adChar=CODE, adVarChar=TEXT. Naji Shaman
Shaman, this will not work with the SQL Server ODBC driver or OLEDB provider because the Navision data types are not exposed to SQL Server - this is why Dan is unable to see them. Dan you are right that syscolumns exposes type information (you can use the stored procedure sp_columns to query it) in SQL Server but Navision does not tell SQL Server anything about its data types (Code/Text, also true for Option, DataFormular and a few others). So when a SQL type is the same for two different Navision types, you can’t tell which is which from the SQL world. Thats a problem. What you could do is make a copy of the Fields virtual table available from within Navision, making it a real SQL table in your database. Thats very easy to do. Then you will be able, from the SQL world, to look into that table for any field of any table to find which Navision type it has. David: recent versions of Navision SQL (perhaps just from 3.70, not sure) will not allow this workaround. As soon as a Code field value is read that is not upper case, a well-formed error is displayed - so you have no chance to update it. This is to replace the previous ‘internal error’ that used to be displayed in some cases (e.g. a restore) that gave you no information about which field had the bad data.
quote:
Originally posted by robertc
… recent versions of Navision SQL (perhaps just from 3.70, not sure) will not allow this workaround. …
Pity, it was pretty easy to fix, it just needed a simple routine to be run in Navision, now I guess the program that does the damage must do the fixing. Is this the same with ODBC?
C/ODBC will still report an internal error as always if there is bad data in a code field, but it is not so easy to put the bad data there with the natvie server. With SQL its all too easy.