I have a problem when using ADO to update a field that is of type CODE in Navision, adChar in ADO. ADO pads the field with spaces, for example, when using: DE1.rsContact!ZIP_Code = “123” ADO writes "123 ". because Navision require the presence of Null char in CODE type fields, the above generates ISAM error when the record is updated by doing MoveNext or using update method. One solution is to change the code as follows: DE1.rsContact!ZIP_Code = “123” + chr(0) This works, however, if you have a TextBox bound to a recordset/DataEnvironment/ADO control, then you cannot add this null character. I tried using Text1_Change, rsContact_WillChangeField without luck. Is there any solution for this? I am using C/ODBC 2.60.F, ADO 2.7, and Navision native server. The most relevant MSKB article to this problem is: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q183592 However, I cannot change the field type, and I am not using the “OLE DB Simple Provider”. I am using “OLE DB Provider for ODBC”. To duplicate this problem, start a new DataEnvironment, connect to Navision database, use SQL Builder to select the Contact table, Choose the fields: No_, ZIP_Code. Change the command’s CursorLocation to adUseClient, and CursorType to adOpenKeyset. Create a new form based on these 2 fields. Create move next/move previous buttons, put the following code in them: DE1.rsContact.MoveNext DE1.rsContact.MovePrevious Now run the program and edit the ZIP Code, when you move to the next record, you will get an ISAM error. If you had another field like “Name”, you will not get this error. Now, add a command button with the following code: DE1.rsContact!ZIP_Code = “123” + chr(0) Also, remove the ZIP Code TextBox from the form, run the program, and click on that button. Click “Move Next” to move to the next record to update the database. Close the program and start Navision, you will find that the ZIP Code was changed. You can tell which fields that are going to generate this error by using the DataEnvironment. Click on the field that you want, the status bar will show the field type. If it says adChar, you will get the error above, if it says adVarChar, you will not get this error.
I have found a solution just in case someone have this same problem. This generic code always work, with ADO code only methods or with the Data Environment(DE):
' Use this line if not using the DE: Dim WithEvents rs As ADODB.Recordset Private Sub rs_FieldChangeComplete(ByVal cFields As Long, ByVal Fields As Variant, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) Dim fld As ADODB.Field Static SkipMe As Boolean If SkipMe Then Exit Sub ' Avoid cascade operations SkipMe = True If Not (pRecordset.BOF Or pRecordset.EOF) Then If pRecordset.EditMode <> adEditNone Then For Each fld In Fields If fld.Type = adChar Then If fld.Value <> Trim(fld.Value) Then ' Only change when different If fld.DefinedSize >= Len(Trim(fld.Value)) + 1 Then ' Avoid overflows fld.Value = Trim(fld.Value) + Chr(0) End If End If End If Next End If End If SkipMe = False End Sub