When trying to add data to a newly created column using SQL Server Management Studio (edit rows), I am getting the following error:

No row was updated.

The data in row 1 was not committed.

Error Source: .Net SqlClient Data Provider.

Error Message: String or binary data would be truncated.

The statement has been terminated.

Correct the errors and retry or press ESC to cancel the change(s).

The datatype for the column is nvarchar(150).

I am inserting only a few ascii characters (example: abc).

This is someone else's database who asked me to look into it... I discovered: a) only some of the rows give this error b) the rows giving this error have long strings in two other columns (one is a nvarchar(max), and one is ntext).

  • Is there only a single column in that table? What are the types and lengths of all the columns and what does your insert/update statement look like? – Dave Holland Mar 1 '10 at 16:23
  • There are multiple columns, see my update above. – Feckmore Mar 2 '10 at 14:00

I've experienced this on occasions when a table had a mixture of ntext and nvarchar(MAX) datatypes. Changing all the ntexts to nvarchar(MAX)s fixes the problem.

| improve this answer | |

This error typically occurs when you exceed the length of the data type for the column. Are you inserting more than 150 characters into the column?

| improve this answer | |
  • I'm only inserting a few characters. I updated some new findings in the post. – Feckmore Mar 2 '10 at 14:00
  • Hmmm - It’s possible to ignore the 'String or binary data would be truncated' message by setting ANSI_WARNINGS to OFF. This will truncate fields where they don’t fit. ANSI_WARNINGS OFF has drawbacks and it is better to correct a problem rather than ignore it. Another thing to check is whether the table update is firing any triggers that insert or update another table. – Brian Knight Mar 2 '10 at 14:07

I got this error when I mistakenly exceeded the number of characters that a field could hold. In Visual Studio, I had mistakenly inserted a field with its datatype as nvarchar(1) and was trying to save a word with 6 characters to that field. Once I changed to nvarchar(50), it worked fine. Hope this helps someone.

| improve this answer | |

Usually this occurs when tried insert more symbols, what is given to this field. go to Design, check the Data Type of field for Example your Data Type = nvarchar(20) and your tried set the 30 characters

Microsoft SQL Server 2014

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.