Extract unicode using asp from sqlserver 2005

I am using the following code to retrieve the post from the database and then write it to the 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 have verified that this is not a browser, this is what is actually written out.

to insert the message I ran update tblRecipients set Message = N'Good 'where ID = 72 in the SQL Server Manager.

Any help on how to display it correctly?

+1


source to share


4 answers


Solution: Man, that was weird. The problem was that the actual ASP file was ANSI encoded. Apparently this is what the encoding is used for when the file is passed to the client (kind of makes sense if you remember that an asp file is just a file passed to the client, slightly modified by the asp engine). The solution was to change the encoding of the ASP file to utf-8. This way, when utf-8 characters are written to the file on the client path, 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.

0


source


Perhaps your db is storing data in something other than utf-8 (perhaps utf-16 or ucs 2?) And your asp page (by default) expects utf-8. Therefore, your data looks correct from SQL Management Studio, but not from the ASP page. The 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 without explicitly converting to utf-8 in every SQL statement. Changing asp charset = "utf-16" didn't seem to help.

+1


source


Welcome to internationalization. I've been fighting all day.

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

However, you save the data that needs to be changed. For example, if you insert special characters such as smart double quotes, you will need to convert them before storing them in the database. Conversely, if you are not converting the data, you will have to convert it using a third party tool when displaying data directly from the database. I came across a COM object from ChilKat that I believe does this. I can't find anything built into classic ASP that will convert from ISO-8859-1 (or anything for that matter) to UTF-8 or vice versa.

Also, in your HTML, you will need to set the encoding equal to the encoding type you want to use.

Good luck.

+1


source


You can try using a native SQL client to see if this fixes it for you, it's a bit of a wild guess, but I know it solved other issues with datatypes and classic ASP.

0


source







All Articles