Solved

Store unicode characters in a Microsoft SQL database then display them on a web page

Posted on 2013-12-06
10
802 Views
Last Modified: 2014-01-08
There are two ways that Unicode (specifically, Chinese) characters are getting entered into my Microsoft SQL database:

1. A user located in Taiwan is typing directly into a web form on a Windows computer that has Chinese as its default language, then my ASP script writes the result to the database.

2. A user located in the U.S. on a Macintosh has an Excel spreadsheet of language translations, which gets saved to a CSV file, uploaded to the server, then an ASP script processes that file and writes to the database. The actual characters in the Excel file come from a variety of sources: via e-mail from Taiwan, a Word or Excel file from Taiwan, or sometimes from Google Translate (via the web).

When I read from the database and display the text on a web page, here is what happens:

Using ASP, I insert this code at the top of my page: response.CharSet="UTF-8"
In the <head> area of the page, I also insert this: <meta charset="utf-8">

With only that, here is my test page:
https://www.interactivehealthpartner.com/test-4.asp

The text from the U.S. computer is just question marks, the text from the Taiwan computer looks fine.

Next, I try adding another line of ASP code at the top of my page: response.CodePage=65001

Here is the result:
https://www.interactivehealthpartner.com/test-3.asp

Now, the text from the U.S. computer looks fine, but the text from the the Taiwan computer is garbage.

From what I understand, response.CodePage=65001 is the same thing as UTF-8.

When I try selecting the text via a SQL Management Studio query window directly, I see the U.S. computer text looks fine, but the Taiwan computer text is garbage.

At this point, I am lost. I need to get a web page to display both of these types of text correctly, on the same page.

My assumption is that there is some difference between the character set on the U.S. computer vs. the Taiwan computer… that even though they are both Chinese, they are different versions of Chinese? If the data were exactly the same type, they should display the same on a web page.

Also, why adding response.CodePage=65001 flip-flops the success of those two types of text is a mystery to me.

Note that when the U.S. text goes bad, its all question marks. When the Taiwan text goes bad, its garbage characters.

This page shows eight different Chinese languages in the default SQL setup:http://msdn.microsoft.com/en-us/library/ms143508.aspx
… I don't know if that is related to my problem or not.

Would really appreciate any insight to this. Thank you!
0
Comment
Question by:bbdesign
  • 6
  • 2
  • 2
10 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39701769
try this place it before the doctype, first thing on the page

<%
Response.CodePage = 65001
Response.CharSet = "UTF-8"
%>

Open in new window

0
 

Author Comment

by:bbdesign
ID: 39701835
I don't think you could have possibly read my question.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39701931
i take it from that it didnt work, but being rude to someone trying to help doesnt encourage them to put further effort in
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:bbdesign
ID: 39701949
Sorry. As I explained I already tried that and a variation of the same thing… its a little more complex of an issue than just adding a CodePage and CharSet, it has something to do with the data in SQL, I am pretty sure.
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 39702425
You could try specifying the language as Chinese with:

<META HTTP-EQUIV="Content-Language" content="ZH">

You might also be get the problem because of a non-strick XHTML doctype.  An html5 doctype might help.

Cd&
0
 

Author Comment

by:bbdesign
ID: 39702619
I tried both of those, but no effect. I really believe SQL is storing two different types of data, I just don't know how to get a handle around what exactly I have.

Even if I have two different languages coming from two different SQL queries, a UTF-8 should be able to handle both of those on the same page, shouldn't it?
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 39703124
The only additional thing I can think of is that there is a problem with the collation sequence of the database.

This is a little old but it looks like a good place to start if collation is contributing to the problem:
http://technet.microsoft.com/en-us/library/ms143524%28v=sql.90%29.aspx

Cd&
0
 

Accepted Solution

by:
bbdesign earned 0 total points
ID: 39724763
I found the problem… it was unrelated to SQL. The Unicode was being garbled by a multi-part form using ASP Upload (Persits Software)… it was garbled before it even got to the SQL database. That software component has a fix, a line of code I needed to add to force it to accept Unicode. Problem solved!
0
 

Author Comment

by:bbdesign
ID: 39754671
Thanks for everyone's help, but I guess the right thing to do is select my own solution, since this particular problem was related to a Windows component, and not SQL or my page encodings.
0
 

Author Closing Comment

by:bbdesign
ID: 39764686
Found solution on my own.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 41
Find results from sql within a time span 11 31
SQL Error - Query 6 26
SQL Server - Getting the most recent engagement for each contact 9 22
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question