Solved

Replacing special characters for storage in SQL and then output to browser

Posted on 2011-03-22
8
598 Views
Last Modified: 2012-05-11
I have an html form with which the user is creating a title and description of an item. This data is then stored in MS SQL and later retrieved for display in the browser.

The problem is that some users enter names with special characters, such as Spanish names (eg. Pérez or Migüel etc.). There are even instances of users entering open or closed quotes rather than the standard straight quotation marks (probably from cut & pasting from a Word Doc I would guess).

In any event all of these special characters are showing up in the DB exactly as they are entered, but when rendered in the browser they are being displayed as a funky question mark symbol (¿).

I do not want to spend the time writing a replace function to account for all the possible variations of special characters.

How best to handle this?  Does anyone have a ready-made classic ASP function or RegExp that I can use to convert the characters to html for storage or some other solution please?

Needs to be in Classic ASP.  Thanks.
0
Comment
Question by:worthyking1
8 Comments
 
LVL 2

Expert Comment

by:rhawk
ID: 35194279
Are you storing them as a VARCHAR or NVARCHAR? That MAY be the issue.
0
 
LVL 11

Expert Comment

by:G_H
ID: 35194830
Try this function (NOT TESTED) but lurking in my library...

GH

 
Function CleanDodgyChars(str)
	' str = Replace(str, "<", "&lt;")
	' str = Replace(str, ">", "&gt;")
	' str = Replace(str, "&", "&amp;")
	' str = Replace(str, """", "&quot;")
	' str = Replace(str, " ", "&nbsp;")
	str = "" & str
	str = Replace(str, "©", "&copy;")
	str = Replace(str, "Ç", "&Ccedil;")
	str = Replace(str, "ç", "&ccdil;")
	str = Replace(str, "Ñ", "&Ntilde;")
	str = Replace(str, "ñ", "&ntilde;")
	str = Replace(str, "Þ", "&THORN;")
	str = Replace(str, "þ", "&thorn;")
	str = Replace(str, "Ý", "&Yacute;")
	str = Replace(str, "ý", "&yacute;")
	str = Replace(str, "ÿ", "&yuml;")
	str = Replace(str, "ß", "&szlig;")
	str = Replace(str, "Æ", "&AElig;")
	str = Replace(str, "Á", "&Aacute;")
	str = Replace(str, "Â", "&Acirc;")
	str = Replace(str, "À", "&Agrave;")
	str = Replace(str, "Å", "&Aring;")
	str = Replace(str, "Ã", "&Atilde;")
	str = Replace(str, "Ä", "&Auml;")
	str = Replace(str, "æ", "&aelig;")
	str = Replace(str, "á", "&aacute;")
	str = Replace(str, "â", "&acirc;")
	str = Replace(str, "à", "&agrave;")
	str = Replace(str, "å", "&aring;")
	str = Replace(str, "ã", "&atilde;")
	str = Replace(str, "ä", "&auml;")
	str = Replace(str, "Ð", "&ETH;")
	str = Replace(str, "É", "&Eacute;")
	str = Replace(str, "Ê", "&Ecirc;")
	str = Replace(str, "È", "&Egrave;")
	str = Replace(str, "Ë", "&Euml;")
	str = Replace(str, "ð", "&eth;")
	str = Replace(str, "é", "&eacute;")
	str = Replace(str, "ê", "&ecirc;")
	str = Replace(str, "è", "&egrave;")
	str = Replace(str, "ë", "&euml;")
	str = Replace(str, "Í", "&Iacute;")
	str = Replace(str, "Î", "&Icirc;")
	str = Replace(str, "Ì", "&Igrave;")
	str = Replace(str, "Ï", "&Iuml;")
	str = Replace(str, "í", "&iacute;")
	str = Replace(str, "î", "&icirc;")
	str = Replace(str, "ì", "&igrave;")
	str = Replace(str, "ï", "&iuml;")
	str = Replace(str, "Ó", "&Oacute;")
	str = Replace(str, "Ô", "&Ocirc;")
	str = Replace(str, "Ò", "&Ograve;")
	str = Replace(str, "Ø", "&Oslash;")
	str = Replace(str, "Õ", "&Otilde;")
	str = Replace(str, "Ö", "&Ouml;")
	str = Replace(str, "ó", "&oacute;")
	str = Replace(str, "ô", "&ocirc;")
	str = Replace(str, "ò", "&ograve;")
	str = Replace(str, "ø", "&oslash;")
	str = Replace(str, "õ", "&otilde;")
	str = Replace(str, "ö", "&ouml;")
	str = Replace(str, "Ú", "&Uacute;")
	str = Replace(str, "Û", "&Ucirc;")
	str = Replace(str, "Ù", "&Ugrave;")
	str = Replace(str, "Ü", "&Uuml;")
	str = Replace(str, "ú", "&uacute;")
	str = Replace(str, "û", "&ucirc;")
	str = Replace(str, "ù", "&ugrave;")
	str = Replace(str, "ü", "&uuml;")
	str = Replace(str, "®", "&reg;")
	str = Replace(str, "±", "&plusmn;")
	str = Replace(str, "µ", "&micro;")
	str = Replace(str, "¶", "&para;")
	str = Replace(str, "·", "&middot;")
	str = Replace(str, "¢", "&cent;")
	str = Replace(str, "£", "&pound;")
	str = Replace(str, "¥", "&yen;")
	str = Replace(str, "¼", "&frac14;")
	str = Replace(str, "½", "&frac12;")
	str = Replace(str, "¾", "&frac34;")
	str = Replace(str, "¹", "&sup1;")
	str = Replace(str, "²", "&sup2;")
	str = Replace(str, "³", "&sup3;")
	str = Replace(str, "¿", "&iquest;")
	str = Replace(str, "°", "&deg;")
	str = Replace(str, "¦", "&brvbar;")
	str = Replace(str, "§", "&sect;")
	str = Replace(str, "«", "&laquo;")
	str = Replace(str, "»", "&raquo;")
	CleanDodgyChars = str
End Function

Open in new window

0
 
LVL 6

Author Comment

by:worthyking1
ID: 35194982
@rhawk the DB field is NVARCHAR
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35195492
My next thought is that your content type on the page is either not set or wrong. Try each of these and see if one work:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">


My thought is that the first and/or second will work. Let me know.

Also, can you point me to your web page so I can see it and view the source?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Author Comment

by:worthyking1
ID: 35197757
Currently the page has UTF-8 content type set.  Here's a sample page: http://dizzy.com/aibu/catalog_class.asp?clid=139
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35198305
Okay, I found 2 web sites that will help a lot. One of them IF you have to encode the characters to HTML special characters and the other talks about the special characters in web pages.

http://www.alanwood.net/unicode/unicode_samples.html
http://www.utexas.edu/learn/html/spchar.html
0
 
LVL 2

Expert Comment

by:rhawk
ID: 35198380
Reading more of one of those links... What font and/or font family is the CSS setting the page to? Try one that is friendly to the special characters. We KNOW that this page on Experts-Exchange allowed you to enter "Pérez or Migüel" so we KNOW they are using a font or family that allows it. Try lucida sans and see if that does it.
0
 
LVL 1

Accepted Solution

by:
MikeK70 earned 500 total points
ID: 35225903
1. Make sure the .asp file is saved in UTF-8 encoding
2. Make sure you have the code page for asp set correctly, your first line should read: <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
3. Make sure you have the encoding set correctly in the http header: <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

With these 3 steps, no further HTML encoding of the characters should be necessary.

Cheers,

Mike
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Automatically log-in a new user through code 1 55
How to update a dummy recordset with classic ASP 4 64
Time/Date Query 11 37
Need help editing script 3 65
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now