retrive unicode using asp from sqlserver 2005

Question

I'm using the following code to retrieve a message from the database and then write it out to a html page:

Dim strDSN, cnn, cmd
strDSN = "Driver={SQL Server};" & "Server=(local)\sql2k5;" & ...
set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = strDSN
cnn.CursorLocation = adUseClient
cnn.Open
set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = cnn
cmd.CommandText = "select Message from tblRecipients where ID = 72"
cmd.CommandType = adCmdText
set getRecordset = Server.CreateObject("ADODB.Recordset")
getRecordset.Open cmd, , adOpenKeyset, adLockReadOnly

Response.write getRecordset("Message")

however all I get is "???????". I checked it wasn't the browser, that is what is actually written out.

to insert the message I ran update tblRecipients set Message = N'Добре' where ID = 72 in SQL server manager.

Any help on how to get it to display properly?

Answer 1

You might try using the SQL Native Client to see if that fixes it for you, it is a bit of a wild guess, but I know that it has resolved other issues with data types and Classic ASP.

Answer 2

Your db is probably storing data in something other than utf-8, (maybe utf-16 or ucs 2 ?), while your asp page (by default) expects utf-8. That's why your data appears correct from SQL management studio, but not from an ASP page. utf-8 data in the DB looks like funky characters when viewed from SQL Management Studio.

I'm still trying to figure out how to store my data in utf-16 and allow asp pages to display it w/o explicitly converting to utf-8 in each sql statement. Changing the asp charset="utf-16" didn't seem to do the trick.

Answer 3

Welcome to internationalization. I've been battling this ALL day.

Ron is correct. Your database collation is probably set to Latin-1 or something like that, which, I believe, is equivalent to ISO-8859-1.

However you are storing the data will need to change. For instance, if you are pasting special characters, like smart double quotes, you will need to convert them prior to saving them to the database. Conversely, if you don't convert the data going in, you will have to to convert it using a third-party tool when displaying the data straight from the database. I came across a COM object by ChilKat that, I believe does this. I can't find anything built into Classic ASP that would give the ability to convert from ISO-8859-1 (or anything for that matter) to UTF-8 or vice versa.

Also, within your HTML, you will need to set the charset equal to the encoding type you wish to use.

Best of luck.

Answer 4

Solution: Man, this was a weird one. The problem was that the actual ASP file was encoded in ANSI. Apparently this is what encoding is used when the file is passed to the client (kinda makes sense if you remember that an asp file is just a file being passed to the client that is modified slightly by the asp engine). The solution was to change the encoding of the ASP file to utf-8. Thus, when the utf-8 characters are written into the file on its way to the client, they match the encoding of the rest of the file and are read by the browser correctly.

Hope this helps the next person.
Cheers,
Andy.