Data type nvarchar in SQL Server ? Asp.net?

Question

In a web application, I place two textboxes and button for inserting the data which is not English language. When I insert into SQL Server it is showing only "???????(1877?)" like this, but I taken the data type as nvarchar even though it is not coming in that language, is there any different way to insert and display the text which is not in English language.

    CREATE TABLE [dbo].[Multilingual](
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL
     ) ON [PRIMARY] 
    GO

    string sq = "insert into multilingual (name,address) values ('" + txtname.Text + "','" + txtadd.Text + "')";
    cmd = new SqlCommand(sq, con);
    cmd.CommandType = CommandType.Text;

    int i = cmd.ExecuteNonQuery();
    if (i == 1)
    {
        get();
    }

Show the code you are using to insert the data and your table structure. — Martin Smith, Feb 15 '12 at 11:40
You are missing the `N` prefix on your string literals so your data gets treated as `Varchar` and you lose characters that are not representable in the code page of your default collation. But you should not be doing that string concatenation anyway. Use parameterised queries of the correct datatype (`nvarchar`) — Martin Smith, Feb 15 '12 at 11:52
if i use proc is it possible Mr. Martin, thank you for response — Surya sasidhar, Feb 15 '12 at 11:53
You don't have to use a proc to use parameterised queries. `insert into multilingual (name,address) values (@name, @address)` will also work. Then add the parameters to the command object. — Martin Smith, Feb 15 '12 at 11:56
thank you Martin smith it is working fine, how can i mark your answer — Surya sasidhar, Feb 15 '12 at 12:00

score 2 · Accepted Answer · edited May 23 '17 at 12:27

The INSERT statement you are generating looks like

insert into multilingual 
(name,address) 
values ('Foo','Bar')

It would need to look like

insert into multilingual 
(name,address) 
values (N'Foo',N'Bar') /*Notice the N prefix*/

to avoid the string literals being treated as non Unicode with potential data loss. But do not do this.

As well as the data loss issue your query is vulnerable to SQL injection and your web site will get hacked if exposed to the internet. You should use parameterised queries as discussed in the comments.

user1211185 · Answer 2 · 2012-02-15T12:19:27.813

1

Try to do something like bellow:

SqlParameter param = new SqlParameter();
param.SqlDbType = System.Data.SqlDbType.NVarChar;
param.Value = txtname.Text;

SqlParameter param1 = new SqlParameter();
param1 .SqlDbType = System.Data.SqlDbType.NVarChar;
param1 .Value = txtadd.Text;

string sq = "insert into multilingual (name,address) values (param ,param1)";     
cmd = new SqlCommand(sq, con);     
cmd.CommandType = CommandType.Text;

edited Feb 15 '12 at 12:19

answered Feb 15 '12 at 12:14

user1211185

731
3
12
27

Your parameters are not indicated correctly in the SQL text. You need `@` signs prefixing them. – Andrew Barber Feb 17 '12 at 05:35

Data type nvarchar in SQL Server ? Asp.net?

2 Answers2