How do I insert unicode into MS-SQL?

Question

I want to insert info.NativeName into a nvarchar field in the database. It doesn't work, all I get is ??????? where the encoding is not western/latin.

Outputting listcultures directly in an asp.net website on page_onload worked fine, but it seems not to work via database.

Public Sub listcultures()
    'Dim x As System.DateTime = DateTime.Now
    'Response.Write(x.ToString("HH':'mm':'ss MMM d', 'yyy 'PST'", New System.Globalization.CultureInfo("zh-CN", False)))
    Dim info As System.Globalization.CultureInfo
    For Each info In System.Globalization.CultureInfo.GetCultures(System.Globalization.CultureTypes.AllCultures)
        'Response.Write("Deutsch: " + info.DisplayName + " English: " + info.EnglishName + " Native: " + info.NativeName + " Name: " + info.Name + " Codepage: " + info.TextInfo.ANSICodePage.ToString() + "<br />")

        'InsertData(info.DisplayName, info.EnglishName, info.NativeName, info.Name, info.TextInfo.ANSICodePage.ToString(), info.IsNeutralCulture.ToString())

        If Not info.IsNeutralCulture Then
            'item.SubItems.Add(amount.ToString("C", info.NumberFormat))
            'item.SubItems.Add(dateNow.ToString("d", info.DateTimeFormat))
        End If
    Next

End Sub

What am I doing wrong? I suppose something with encoding ?

This code only contains a loop over comments. What does InsertData do? Please show the CREATE TABLE statement — devio
– devio, Commented Jul 26, 2010 at 11:51

Giorgi · Accepted Answer · 2010-07-26 11:55:19Z

4

You need to add N before the unicode string when you are inserting it. You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server

answered Jul 26, 2010 at 11:55

Giorgi

31k13 gold badges92 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stefan Steiger Over a year ago

Aaaaaaaaaaaaaaaaaargh. INSERT N'MyString' instead of INSERT 'MyString'. N as in u-Nicode. Thanks !

Marc Gravell Over a year ago

For this to have fixed the problem indicates that the OP is not parameterizing the TSQL; which indicates (IMO) a far bigger problem...

Stefan Steiger Over a year ago

@Marc Gravell: True, but I only use it localy to create a database of localized strings. It won't be in any public code, so there is no danger of SQL injection, and besides, I always replace the ' with ''. I've seen the N' before, but I didn't know it stands for unicode.

Marc Gravell Over a year ago

@Quandry - OK then; as long as you are in control of that data, it should be fine.

Michael Buen · Accepted Answer · 2010-07-26 12:01:01Z

0

What exactly happens? Ultimately you are just building a string here and storing into nvarchar, so it should work fine, provided:

you have correctly parametrized the input to the command
you haven't overflown the width of the the column

We can't really say more without seeing InsertData, which I assume talks to the database. I'm also not quite sure why a "list" method would be doing inserts in the first place, of course. Plus since everything is commented out at the moment, I would expect nothing to happen...

edited Jul 26, 2010 at 12:01

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

answered Jul 26, 2010 at 11:53

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

1 Comment

Stefan Steiger Over a year ago

Well, list is just the loop, the insert happens in InsertData, which is commented out here, but I of course ran it without commenting it out. I wouldn't have gotten ????? values, in combination with correct english values, if the insert wouldn't have worked.

Collectives™ on Stack Overflow

How do I insert unicode into MS-SQL?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related