Solved

Imported characters from spreadsheet look strange on ASP page

Posted on 2016-07-30
6
71 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 79

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

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.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This video teaches users how to migrate an existing Wordpress website to a new domain.

786 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