FIX: MBCS Encoded Strings Are Truncated in ADO 2.1

When you pass multi-byte character set (MBCS) encoded strings to a database, the string may be truncated, and no error is returned.

Microsoft ActiveX Data Objects (ADO) versions 2.1 Service Pack 2 (SP2) and earlier correctly determine the number of characters but do not correctly convert this to the bytes that are needed to store the string. Some languages use multiple bytes to store one character. The string is truncated because of this byte miscalculation.

To resolve this problem, Microsoft strongly recommends that you upgrade to ADO version 2.5, which is part of Microsoft Data Access Components (MDAC) version 2.5.

This problem does not affect Microsoft Windows 2000 users because MDAC 2.5 is already included with the Windows 2000 installation.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Data Access Components version 2.5.
You can download the latest version of MDAC from the following Microsoft Web site:

Steps to Reproduce Behavior

NOTE: This sample only fails on a computer that is running a double-byte character set (DBCS) version of Windows (for example, Japanese or Chinese) and MDAC 2.1 or earlier.

1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
2. From the Project menu, click References, and then select the Microsoft ActiveX Data Objects 2.1 check box.
3. Add a CommandButton control to Form1.
4. Add the following code to the Command1_Click event:

   Private Sub Command1_Click()
       Dim cnn1 As New ADODB.Connection
       Dim Cmd As New ADODB.Command
       Dim str As String
       Dim rows As Variant
       
       On Error GoTo AdoError
       
       str = "[MULTIBYTE CHARACTER STRING]"
       strCnn = "Driver={SQL Server};server=find;uid=sa;password=;database=pubs"
       
       cnn1.Open strCnn
       
       Cmd.ActiveConnection = cnn1
       Cmd.CommandText = "UPDATE jobs SET job_desc=? WHERE job_id=1"
       Cmd.CommandType = adCmdText
            
       Cmd.Execute rows, str, -1
       cnn1.Close
       
       MsgBox "Works"
       Exit Sub
       
   AdoError:
       Dim errLoop As Error
       Dim strError As String
   
       ' Enumerate Errors collection and display properties of
       ' each Error object.
       Set Errs1 = cnn1.Errors
       For Each errLoop In Errs1
             str = errLoop.Description
             str = str + "SQL state:"
             str = str + errLoop.SQLState
             str = str + " Native error:"
             str = str + errLoop.Description
             
             MsgBox str
       Next
   End Sub
   					

5. Replace [MULTIBYTE CHARACTER STRING] with a valid multi-byte character string. Change the SQL Server ConnectionString to point to a valid server.
   
   NOTE: Depending on which version of MDAC and Microsoft SQL Server is installed, you may or may not receive an error message. To verify the data, use SQL Server tools or the Data View window to locate the record in the table. Most likely, the data is only viewable on multi-byte versions of Windows.