convert memo field in Access database from double byte to Unicode

Question

I am using Access database for one system, and 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!!!

Answer 1

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!