How to insert special characters to a table row via SQL Editor

Question

I have a table in my SQL Server database with a column of type nvarchar(50);

I tried to do this:

Right click on my table
Selecting "Edit 200 first rows"
Typing in that column 'a'

I get an error like this:

Error source: .Net SqlClient Data Provider.
Error message: Incorrect syntax near 'a'.
Correct the errors and retry".

Why do I get this error message and how can I fix this?

There is NO circumstance in which you should be adding or changing records this way. If you need changes to data they should alawys be done thorough sql scripts. — HLGEM, Nov 26 '13 at 14:07

dev · Accepted Answer · 2013-11-26T10:05:02.517

2

I have used aqua data studio and tested this it is allowing me.

my result:::

 id     testc     
 -----  --------- 
 1      'asdfasd'

If you want to use it thrugh .net then while passing the string add the escape sequences. suppose if you are sending "'"--single quote.. then send I have used and tested this it like "\'" is allowing me.

refer below link:::

http://www.codeproject.com/Questions/157918/How-can-I-insert-a-special-character-in-sql-server

edited Nov 26 '13 at 10:05

answered Nov 26 '13 at 09:43

dev

715
5
21

1

doesn't backslash escape ? so it should be "\'" ? – SteveB Nov 26 '13 at 09:46
can you give an exapmle of what to type in the editor in order to get the word 'a' inserted? – Tal Malaki Nov 26 '13 at 09:52
you can use aqua data studio.I have tested and it is allowing me directly without changing anything. – dev Nov 26 '13 at 10:03
OK, but in sql server managment studio 2008 - what do you type? Please write each character you type. – Tal Malaki Nov 26 '13 at 10:09
check this link::: http://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sqlserver – dev Nov 26 '13 at 10:43

score 0 · Answer 2 · edited Sep 14 '14 at 08:37

I have had the same error.
I want to insert a value I am Nam 'ABC' into MyTable at field Description as following SQL statement

Insert Into table MyTable (Description) values('I am Nam 'ABC'');

Once Insert command executed the error occur immediately

Incorrect syntax near 'ABC'.

Note that the reason why is SQL will understand character " ' " which before ABC belongs to character " ' " before I, and one after ABC belongs to " ' " at the end. As a result ABC does not belong to SQL statement anymore therefore it makes a break of statement.

Here is my solution:
I added two single quotes for each side of ABC

Insert Into table MyTable (Description) values('I am Nam ' 'ABC' ' ');

How to insert special characters to a table row via SQL Editor

2 Answers2