text encodings in .net, sql server processing

I have an application that gets terms from a DB to run as a list of string terms. The DB table was set up with nvarchar for that column to include all foreign characters. Now in some cases where characters like ä will come through clearly when gett开发者_StackOverflow中文版ing the terms from the DB and even show that way in the table. When importing japanese or arabic characters, all I see are ????????. Now I have tried converting it using different methods, first converting it into utf8 encoding and then back and also secondly using the httputility.htmlencode which works perfectly when it is these characters but then converts quotes and other stuff which I dont need it to do. Now I accused the db designer that he needs to do something on his part but am I wrong in that the DB should display all these characters and make it easy to just query it and add to my ssearch list. If not is there a consistent way of getting all international characters to display correctly in SQL and VB.net

I know when I have read from text files I just used the Microsoft.visualbasic.textfieldparser reader tool with encoding set to utf8 and this would not be an issue.

---

If the database field is nvarchar, then it will store data correctly. As you have seen.

Somewhere before it gets to the database, the data is being lost or changed to varchar: stored procedure, parameters, file encoding, ODBC translation etc.

DECLARE @foo nvarchar(100), @foo2 varchar(100)

--with arabic and japanese and proper N literal
SELECT @foo = N'العربي   日本語', @foo2 = N'العربي   日本語'
SELECT @foo, @foo2 -- gives العربي   日本語

--now a varchar literal
SELECT @foo = 'العربي   日本語', @foo2 = 'العربي   日本語'
SELECT @foo, @foo2 --gives ??????   ???

--from my Swiss German keyboard. These are part of my code page.
SELECT @foo = 'öéäàüè', @foo2 = 'öéäàüè'
SELECT @foo, @foo2 --gives ??????   ???

So, apologise to the nice DB monkey... :-)

---

Always try to use NVARCHAR or NTEXT to store foreign charactesr. you cannot store UNICODE in varchar ot text datatype. Also put a N before string value

like

UPDATE [USER]
SET Name = N'日本語'
WHERE ID = XXXX;