Geek Answers Handbook

How to convert UTF-8 data from Post Post from classic asp to UCS-2 for insertion in SQL Server 2008 r2?

I am in the process of “upgrading” a classic asp application using an Access 2000 database.

I rewrote the database on SQL Server 2008r2 and changed all the fields to use the new nicar nchar, nvarchar, ntext, but the imported old data. I also switched to IIS 7 from IIS 6

Classic asp collects and writes data using UTF-8.

Now the application correctly displays OLD data on web pages, but as a son when I touch it, i.e. UPDATE or INSERT, data becomes corrupted. I assume that I need to somehow convert the UTF-8 data from classic asp to UCS-2 before I write the data to the SQL server.

But how?

NOTE. It seems that the sql server automatically converted utf-8 data to a convenient format when it imported data from access.

+4
sql-server utf-8 asp-classic ucs2
source share
1 answer

You must tell SQL Server 2008 that you are sending data in Unicode by adding N to the beginning of your insert value. so like that

strTest = "  " strSQL = "INSERT INTO tblTest (test) VALUES (N'"&strTest&"')"

N tells the SQL server to treat the contents as Unicode. and does not corrupt data.

See http://support.microsoft.com/kb/239530 for more details.

Here is the test code Running in classic ASP IIS 7 SQL Server 2008r2

 CREATE TABLE [dbo].[tblTest]( [test] [nvarchar](255) NULL, [id] [int] IDENTITY(1,1) NOT NULL

ASP Page

 <% Response.CodePage = 65001 Response.CharSet = "utf-8" strTest = Request("Test") Set cnn = Server.CreateObject("ADODB.Connection") strConnectionString = Application("DBConnStr") cnn.Open strConnectionString strSQL = "INSERT INTO tblTest (test) VALUES (N'"&strTest&"')" Set rsData = cnn.Execute(strSQL) %> <html xmlns="http://www.w3.org/1999/xhtml" charset="utf-8"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title></title> </head <body> <form action="test.asp" method="post" name="form1" > <br/><br/><br/><center> <table border="1"> <tr><td><b>Test SQL Write</b> </td></tr> <tr><td><input type="text" name="Test" style="width: 142px" Value="<%=strtext%>" /></td></tr> <tr><td><input type="Submit" value="Submit" name "Submit" /></td></tr></table> </center> </form> </body> </html>
+6
source share

More articles:


All Articles