How do I convert UTF-8 data from Classic asp Form post to UCS-2 for inserting into SQL Server 2008 r2?

I am in the process of "modernizing" a classic asp application that uses a Access 2000 database.

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

The classic asp is collecting and writing data using UTF-8.

Now the application shows the OLD data correctly in the web pages but as son as I touch it ie: UPDATE or INSERT the data is getting corrupted. I assume I need to somehow convert the UTF-8 data from the classic asp to UCS-2 somehow before I write the data into SQL server.

But how?

NOTE: it seems that sql server auto converted the utf-8 data into a usable format when it imported the data from access.

Solution

You have to tell SQL Server 2008 that you are sending in unicode data by adding an N to the front of your insert value. so its like this

strTest = "Служба мгновенных сообщений"
strSQL = "INSERT INTO tblTest (test) VALUES (N'"&strTest&"')"

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

See http://support.microsoft.com/kb/239530 for further info.

Here is test code Run on 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>