ADO and Navision CODE fields problem

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