Solved

Imported characters from spreadsheet look strange on ASP page

Posted on 2016-07-30
6
76 Views
Last Modified: 2016-08-01
I imported data from a spreadsheet into an sql db table.
Some of the values have accents, etc. but on the ASP which uses UTF-8 encoding the accent shows with strange characters

This is an example.

table name: users
field : MiddleNm
Database value:   Cléa
How it shows on the ASP webpage: Cléa

I was able to run a query to replace the above accent with:

UPDATE dbo.Users
Set    MiddleNm = replace(MiddleNm, 'é', 'é')

The problem is that there may be other characters that need to be replaced, I was wondering if there may be a query out there that replaces all the accents and other strange characters with the UTF-8 unicode.

Source:  http://www.fileformat.info/info/charset/UTF-8/list.htm
0
Comment
Question by:amucinobluedot
  • 3
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
Manuel Marienne-Duchêne earned 250 total points
ID: 41735947
I use this function to encode decode

Function ToUTF8(s)
	    sUTF8 = ""
	    For i=1 To Len(s)
		     unicode = AscW(Mid(s, i, 1))
		     If unicode <= &H007F Then
			     sUTF8 = sUTF8 & Chr(unicode)
		     Else
			     If unicode <= &H07FF Then
				     sUTF8 = sUTF8 & Chr(&HC0 + unicode \ &H40)
				     sUTF8 = sUTF8 & Chr(&H80 + unicode Mod &H40)
			     Else
				     sUTF8 = sUTF8 & Chr(&HE0 + unicode \ &H1000)
				     sUTF8 = sUTF8 & Chr(&H80 + (unicode Mod &H1000) \ &H40)
				     sUTF8 = sUTF8 & Chr(&H80 + (unicode Mod &H1000) Mod &H40)
			     End if
		     End If
	    Next
	    ToUTF8 = sUTF8
    End Function

    Function FromUTF8(sIn) 
            Dim oIn
            Set oIn = CreateObject("ADODB.Stream") 
            oIn.Open 
            oIn.CharSet = "WIndows-1252" 
            oIn.WriteText sIn 
            oIn.Position = 0 
            oIn.CharSet = "UTF-8" 
            FromUTF8 = oIn.ReadText 
            oIn.Close 
    End Function 

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 41735953
I assume that is a function that is added to the Database. The data is already in the database, how can I run a query that would encode those characters with accents, etc  ?
0
 
LVL 6

Assisted Solution

by:Manuel Marienne-Duchêne
Manuel Marienne-Duchêne earned 250 total points
ID: 41735955
In your database the accent is correct or not ?
if yes response.write toUTF8(result of SQL)
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 80

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 250 total points
ID: 41736167
what is the database collation? What is your webpage language settings? (in the header)
0
 

Author Comment

by:amucinobluedot
ID: 41736180
If we read the initial question you can see the character in the database is what the actual character should show on the webpage.

I already have a query to replace one. But I want to see if someone already has a query that includes all the characters in the link I included at the bottom. Otherwise I'll have to create it myself.
0
 

Author Comment

by:amucinobluedot
ID: 41738301
I am spreading the points. my DB doesn't support non-Latin characters, so I am just removing the accents. thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to count occurrences of each item in an array.

830 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