CODBC/ADO/ISAM Errors: What I have learned so far...

ZenZagg

I have been researching what has been causing unexplained ISAM errors in our internal VB software that interfaces with Navision Financials (US 2.00). When the ADO Update method is used, an ISAM Error is returned from the Navision ODBC driver. Reading the database works fine, but writing to it causes this error.

After reading several comments here I would like to share what I have learned in my research on this problem. I first noticed that the error was only showing up on some clients. We only started getting these errors after certain programs were installed, such as Office 2000, IE6.0, and Visio 2000.

As it turns out, these programs were updating our MDAC (Microsoft Data Access Components) to higher versions. We originally used MDAC 2.0. Starting with MDAC 2.5 and higher, the ISAM errors began showing up where they had not in MDAC 2.0. So, for those of you experiencing this error, one workaround is to install MDAC 2.0. However, we fear that if we never update our MDAC's we will eventually hit a point where a newer program will need the newer version, and we will be between a rock and a hard place. Currently most of our financial operations are handled by Navision, including Job Costing and Labor Tracking. If we come to a point where we need to update the MDAC, we will either have to reprogram potentially megabytes of internal source code to avoid using the ADO Update method, or find some way to make the new program deal with an outdated Data Access model. Neither option looks promising.

I've been searching for a solution relatively non-stop for several days, and I have found a good amount of information on this issue.

Microsoft released a Knowledge Base article that seems to have described the problem dead-on:
PRB: ADO/RDS Update Error with Third Party ODBC Drivers (Q192652)
If this is the case, it is an issue with the C/ODBC drivers supplied with Navision not supporting positioned updates. I have tested it, and direct SQL statements can update data in Navision, but not the Update function, so this seems fairly accurate.

I am not sure if C/ODBC 2.50 or 2.60 B include a fix to this problem. If they do, that would require that we update Navision to one of these versions. We have already purchased both, but are hesitant to try and upgrade because we have done massive (read: years of customizing) amounts of changes (via the object designer) to the original set of options when we first started using Navision, and do not know what to expect. Data integrity is our primary concern, with progress being second. I have already converted our Access 97 Databases to SQL 6.5, and that was quite a daunting task initially.

Here is the VB 6.0 code snippet that accesses the data. Some of it is mine, some of it comes from programmers who came before me (like that horrid do loop <img border="0" title="" alt="[Razz]" src="images/smiles/icon_razz.gif" /> ):
</font><blockquote><font size="1" face="Verdana, Arial">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;"> 'See if batch names for today have already been created.
Dim lclDateS As String
lclDateS = Format$(Now, "mmddyyyy")
mdlFlatS = "FL" & lclDateS
mdlDirectS = "DI" & lclDateS
mdlIndirectS = "IN" & lclDateS
mdlTemporaryS = "TE" & lclDateS
Dim lclJobJournalBatchRS As ADODB.Recordset
Set lclJobJournalBatchRS = New ADODB.Recordset
lclJobJournalBatchRS.ActiveConnection = mdlNavisionCN
lclJobJournalBatchRS.CursorType = adOpenForwardOnly
lclJobJournalBatchRS.LockType = adLockOptimistic
lclJobJournalBatchRS.Source = "Job_Journal_Batch"
lclJobJournalBatchRS.Open , , , , adCmdTable
Do Until lclJobJournalBatchRS.EOF
If lclJobJournalBatchRS.Fields("Name").Value = mdlFlatS Then
Dim lclFoundTF As Boolean
lclFoundTF = True
Exit Do
End If
lclJobJournalBatchRS.MoveNext
Loop

'Add new rows?
If Not lclFoundTF Then
'Flat time entries.
lclJobJournalBatchRS.AddNew
lclJobJournalBatchRS.Fields("Journal_Template_Name").Value = "LABOR"
lclJobJournalBatchRS.Fields("Name").Value = mdlFlatS
lclJobJournalBatchRS.Fields("Description").Value = "FLat"
lclJobJournalBatchRS.Update</pre><hr /></blockquote><font size="2" face="Verdana, Arial">The full connection string is:

Provider=MSDASQL.1;Extended Properties="DSN=Navision32_20;CSF=Yes;SName=PROGRMR;NType=tcp;PPath=c:\fin;OPT=Integer;IT=a-z,A-Z,0-9,_;QTYesNo=Yes;RO=No;CC=Yes;BE=Yes;TP=C:\WINNT\Profiles\Administrator.000\Deskt op;UID=<deleted>;PWD=<deleted>;CN=Consolidated Engineering;SERVER=N;"

In this code, the RecordSet.Update function triggers the ISAM error on MDAC 2.5 and up. I have written object-oriented classes in vb that use pure SQL to write data to Navision, and in most of these cases the ISAM error does not appear. However, that is a messy way of doing things, and would require a great deal of code revision to implement. So far, it is only when I use standard ADO object methods to write data to Navision using C/ODBC 2.00 DSN and MDAC 2.5+ that errors pop up. We can only keep out MDAC out of date for so long before it bites us in the behind.

Basically, my point in creating this topic is to open a discussion for myself and any others who experience or have knowledge about this problem so that perhaps we may find the best solution. If you have any information to contribute, please post a reply.

Thanks!

<small>[ 24-06-2002, 18:28: Message edited by: TravisCEC ]</small>

bfernandes

The ISAM error appeared once or twice in my project (2.60F). In those cases, I've discovered what was going wrong. In some ASP pages where the user typed some information I realised that sometimes the information that was going to be saved in a Navision table was larger than the destination field in the table.
So, What have I learned so far <img border="0" title="" alt="[Smile]" src="images/smiles/icon_smile.gif" /> :
- ISAM might be caused by information being submited in a field smaller than the length of that information;
- Be carefull with date format (it must be converted in a really weird format);
- C/ODBC also crashes with decimals. A decimal to be saved in Navision through C/ODBC must be of type xxxx.xxx, without commas, just a point indicating the beginning of the decimal places.

MLW222

ZenZagg;
Thanks for the post.

In my project (see C/ODBC ADO & SQL problems), am not trying to write to NF database just read.

However, I have been using MDAC 2.5 for the ADO attempt/version, maybe MDAC 2.0 will do it. Thanks for the hint.

Also, is there another, later NF 2.00.0 US, ODBC driver, ours is 2.10.00.10?