CODEWITHSUNDEEP
sqlsql-serverunicodeasp-classiccharacter-encoding
user3435078
user3435078

Reputation: 329

Special characters, MS SQL Server 2012, ASP and SQL?

I have a folder, inside this folder I have a single file with this file name:

male♂.txt

I want to put this file name into my SQL Server 2012 database using ASP (VBScript):

<%@language=VBScript codepage=65001%><%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQL Server Native Client 11.0;Server=localhost;Database=databank;Uid=myuser;Pwd=mypass;"
Set RecSet = Server.CreateObject("ADODB.Recordset")
Set FSO = CreateObject("Scripting.FileSystemObject")

For Each file In FSO.GetFolder("C:\inputs\").files
    RecSet.Open "SELECT * FROM filenames WHERE fname='"& file.name &"';", Conn, 0, 3
        IF RecSet.EOF THEN
            RecSet.AddNew
                RecSet("fname") = file.name
            RecSet.Update
        end IF
    RecSet.Close
    RecSet.Open "SELECT * FROM filenames WHERE fname='"& file.name &"';", Conn, 0, 3
        Response.Write "Is EOF: "& RecSet.EOF
    RecSet.Close
Next
Conn.Close
'setting variables to Nothing here
%>

After I run this code I check the database table using MS SQL Management Studio and there is indeed a post with "male♂.txt" in it.

The problem is that the ASP page outputs "Is EOF: True" and if I run the same script again another post with "male♂.txt" in it is created in the database.

I've recently moved from MS Access to MS SQL Server 2012 and the code above worked as intended on MS Access (EOF is false). What am I doing wrong?

Upvotes: 0

Views: 1284

Answers (1)

user3435078
user3435078

Reputation: 329

Seems like you always have to add "N" before strings in MS SQL Server for it to be read as Unicode.

If I change the two SQL statements to this it works:

SELECT * FROM filenames WHERE fname=N'"& file.name &"';

I didn't know this was needed. It worked without N in MS Access.

This is true for UPDATE and INSERT INTO as well, otherwise the unicode characters will turn out as questionmarks.

Upvotes: 2

Related Questions