0

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!!!

KhD
  • 453
  • 3
  • 11
  • 29
  • Any help here http://stackoverflow.com/questions/727583/export-ms-access-memo-field-and-convert-unicode ? – Fionnuala Oct 03 '11 at 09:25
  • Do you really need to go through the datagrid step or can you move your data directly from ms-access to SQL? – Philippe Grondier Oct 03 '11 at 15:49
  • Hi Philippe...The main aim is to move the data in Unicode format to SQL server. I used DataGridView as a simple viewer. I can skip this step. – KhD Oct 04 '11 at 01:30

2 Answers2

0

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 .... "

Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
  • Thank you for replying Philippe. I tried both the queries that you specified in the end of your answer. Neither of them worked. I am not concerned about viewing the data from Access database. All I want to do is insert the Notes from Memo field of Access database file, to nvarchar(max) of Sql Server table. The problem I'm facing is that Memo does not understand Unicode, and nvarchar does. How do I transfer the data so that non-English characters are also inserted in proper format? – KhD Oct 06 '11 at 08:14
  • Forgot to mention that Access version is 97. – KhD Oct 06 '11 at 08:15
  • My example with stringFromGUID was only an example of what I use for converting 'unique identifier' Byte data to text. It would not work with memo field. Yuor problem is to find the correct way to convert your memo field data to 'standard' text data. If this cannot be done on the Access side, what about (1) populating a text or ntext field on the SQL side and then (2) use SQL convert function to transfer the data from the text\ntext field to the nvarchar(max) field? – Philippe Grondier Oct 06 '11 at 08:51
  • I do not have Access 97 to test my proposal, so I think you'll have to test this solution! – Philippe Grondier Oct 06 '11 at 08:52
  • Hi Philippe, I implemented your suggestion. I populated nText field of a temporary table with Memo field data, and then used convert function and inserted data into nvarchar field of final table. But it did not work. Any other ideas? – KhD Oct 07 '11 at 03:59
  • Did the SQL ntext field was ok? Which kind of conversion function did you use? Any error message? – Philippe Grondier Oct 09 '11 at 09:45
0

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!

KhD
  • 453
  • 3
  • 11
  • 29