Link to home
Start Free TrialLog in
Avatar of jensjakobsen
jensjakobsenFlag for Denmark

asked on

Local letters look bad

Hi

I have created a small classic asp cms module. When I create articles I can use Danish letters like:
Æ Ø Å
and
æ ø å.

In the database they also show up as I typed them (Æ Ø Å and æ ø å)

They render correct in HTML.

But when I edit the article the Danish letters have been converted into strange letters - in my case they look like this:
���
and
���

In the MS SQL 2012 database the letters look like this (text field)
<p>���</p>
<p>���</p>

The ASP pages from where I do my editing both have the following HTML info:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

All my browsers use UNICODE (UTF-8) as character encoding.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

I am confused.  First you say:
In the database they also show up as I typed them (Æ Ø Å and æ ø å)
then you say:
In the MS SQL 2012 database the letters look like this (text field)

So are they correct in the database as seen using something like SSMS?
Avatar of jensjakobsen

ASKER

Hi Acperkins.

When i INSERT the string into my database - they look like this:
Æ Ø Å and æ ø å

If I UPDATE the article (without actually changing the above letters - I just click the submit button for an update)
Then they look like this afterwards in the database:
<p>���</p>
<p>���</p>
The question you need to ask yourself is:  Is the data correct in the database?  They only way you can answer that question is viewing it with something like SSMS.
Pls explain SSMS?
SSMS = SQL Server Management Studio.   The client application shipped with SQL Server to manage it.
Oh sorry - I use it already.

The data looks exactly the same as when rendered at HTML/asp.
Which means that the looks of the data are the same whether it's in the database or at the web pages.
you may need to initialize your sql connection as utf8 also. Then even after updating the articles, your letters should read in the database correctly.
How do I do that?
ini_set('mssql.charset', 'UTF-8');

Open in new window

Before I do that I should inform that the only character encoding that works for me is 8959-10.

So - to avoid funny characters I setup my web pages with this encoding:

<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-10¿ />
ok then set your connection also to iso-8959-10 like so:

ini_set('mssql.charset', 'iso-8959-10');

Open in new window

make it on a test environment. So you can avoid problems on the production side. I can't give you warranty. It helped me a lot. If that doesn't work, utf-8 should show most of the characters right, so try to switch your site to utf-8, too, and then set your db-connection to utf-8. Setting the connection has no influence on the database settings itself. Your database remains the collation which it was set up in the first place.
And there has been improvements from iso-8959-1 to iso-8959-15. so maybe you should use that instead

iso-8959-15
Hi

My conn string looks like this - how would I merge your suggestion into my conn string?

Provider=SQLOLEDB;Data Source=xx;Initial Catalog=xx;User ID=xx;Password=xx
ok the ini_set is a php only thing. What you could do is to use nchar and nvarchar fields instead of char and varchar in your database. The n-prefix converts your queries into unicode.

check this discussion out:
http://stackoverflow.com/questions/1322421/php-sql-server-how-to-set-charset-for-connection
Hi

I really need examples and precise syntax. I'm more of a newbie when it comes to SQL.
Hi again. I've read the article but I still don't understand how to include it into my connection string?
I'm not an expert on mssql, but you should have something like a gui for database administration, a pendant to phpmyadmin for php and mysql.

On this graphical interface I would recommend you to copy your production database into a test database where you can test your alterations. Then convert your char and varchar columns in the database gui to nchar and nvarchar columns. Here is some information about how.

On your test database you can make a incredible journey of trial and error database alteration learning. Good luck on your journey and remember: Backup your database before doing anything with it, create the copy of it to trie your alterations on and then explore some awsome wisdom on mssql database interaction just by the most ancienct form of learning, trial and error. Just like we did when we were kids!! Go!
Thanks for your advice.

I'm here because I need something a little more trial and error - I do lots of trial and errors, and sometimes I just need help.
ASKER CERTIFIED SOLUTION
Avatar of jensjakobsen
jensjakobsen
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great answer