convert memo field in Access database from double byte to Unicode

I am using Access database for one system, a开发者_如何转开发nd SQL server for another system. The data gets synced between these two systems. The problem is that one of the fields in a table in Access database is a Memo field which is in double-byte format. When I read this data using DataGridView in a Windows form, the text is displayed as ???. Also, when data from this field is inserted in sql server database nvarchar(max) field, non-English characters are inserted as ???.

How can I fetch data from memo field, convert its encoding to Unicode, so that it appears correctly in SQL server database as well?

Please help!!!

---

I have no direct experience with datagrid controls, but I already noticed that some database values are not correctly displayed through MS-Access controls. Uniqueidentifiers, for example, are set to '?????' values when displayed on a form. You could try this in the debug window, where "myIdField" control is bound to "myIdField" field from the underlying recordset (unique Identifier type field):

? screen.activeForm.recordset.fields("myIdField")
{F0E3C822-BEE9-474F-8A4D-445A33F363EE}

? screen.activeForm.controls("myIdField")
????

Here is what the Access Help says on this issue:

The Microsoft Jet database engine stores GUIDs as arrays of type Byte. However, Microsoft Access can't return Byte data from a control on a form or report. In order to return the value of a GUID from a control, you must convert it to a string. To convert a GUID to a string, use the StringFromGUID function. To convert a string back to a GUID, use the GUIDFromString function.

So if you are extracting values from controls to update a table (either directly or through a recordset), you might face similar issuers ...

One solution will be to update data directly from the recordset original value. Another option would be to open the original recordset with a query containing necessary conversion instructions so that the field will be correctly displayed through the control. What I usually do in similar situation, where I have to manipulate uniqueIdentifier fields from multiple datasources (MS-Access and SQL Server for Example), is to 'standardize' these fields as text in the recordsets. Recordsets are then built with queries such as:

• SQL Server

  "SELECT convert(nvarchar(36),myIdField) as myIdField, .... FROM .... "

• MS-Access

  "SELECT stringFromGUID(myIdField) as myIdField, .... FROM .... "

---

I solved this issue by converting the encoding as follows:

        //Define Windows 1252, Big5 and Unicode encodings
        System.Text.Encoding enc1252 = System.Text.Encoding.GetEncoding(1252);
        System.Text.Encoding encBig5 = System.Text.Encoding.GetEncoding(950);
        System.Text.Encoding encUTF16 = System.Text.Encoding.Unicode;

        byte[] arrByte1 = enc1252.GetBytes(note);  //string to be converted
        byte[] arrByte2 = System.Text.Encoding.Convert(encBig5, encUTF16, arrByte1);
        string convertedText = encUTF16.GetString(arrByte2);
        return convertedText;

Thank you all for pitching in!