PRB: Question Marks and Garbage characters instead of Arabic text

• SYMPTOMS
• CAUSE
• FIX

The information in this article applies to:

• Microsoft Visual Basic Professional Edition for Windows, versions 6.0 , 5.0
• Microsoft Visual Basic Enterprise Edition for Windows, versions 6.0 , 5.0
• Windows 2000 Arabic enabled
• Windows XP Arabic enabled

SYMPTOMS

Visual Basic applications display question marks and garbage characters instead of Arabic text, when connecting to SQL. The problem is that data is retrieved and stored in SQL Server as rubbish. This paper will investigate the cause of such a behavior and the recommended solution.

CAUSE

This occurs because Arabic characters are associated with the incorrect destination language. There are two reasons for this behavior.
First reason, question marks replace Arabic text. Any time Unicode data must be displayed, they may be internally converted from Unicode using the WideCharToMultiByte API. Any time a character cannot be represented on the current code page, it will be replaced by a question mark (?). This makes the appearance of question marks a good warning of data that has been corrupted due to this conversion.
Second reason, garbage characters replace Arabic text. This indicates that your application is using ANSI characters and not Unicode. These characters are then matched with your current system language (or codepage NOT your user interface). If your system is not Arabic then the characters will be matched with its numerical equivalent.

FIX

There are two main guidelines to avoid this problem on Visual Basic applications.

• Step 1
  As a SQL developer, you should use Unicode data types in applications. The Unicode data types are nchar, nvarchar, and ntext. These data types use Unicode character representation. Code pages do not apply to these data types. Using Unicode data types gives you the ability to deal with Arabic data even the system collation is not Arabic.
  When using Unicode data types, the best way to save your Arabic data from corruption when inserting, and updating is to add capital N before your Arabic string otherwise if the default database collation is not Arabic your data will be corrupted. When using N prefix with Unicode data types you save your Arabic data regardless the default database collation is Arabic or not. This is the golden key to deal with Unicode data types in the applications like VB, or ASP, even inside SQL server like stored procedures.
  
  
UPDATE TableName SET ColumnName = N'Arabic Text' WHERE id = 1000
INSERT INTO TableName (ColumnName) values(N’Arabic Text’)


• Step 2
  

  As a user, you need to add the Arabic language to your system. You need to identify non-English characters because Visual Basic uses ANSI user interface and not Unicode. To identify them you need to adjust the regional control panel options

  On Win 2000: "Regional Options" in the "General" tab, add Arabic to the "Language setting for the system"

  On Win XP: "Regional and Language Options" go to the "Advanced" tab and change the "Language for non Unicode programs" to an Arabic language.

  PS. This will not affect the user interface language.