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.

Answers


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>

Need Your Help

Grab Bing Wallpaper with python3?

python python-3.x wallpaper bing

I wanna write a python script that grabs the bing.com wallpaper and saves it.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.