String or binary data would be truncated. The statement has been terminated. System.Data.SqlClient.SqlException (0x80131904)

Question

String or binary data would be truncated. The statement has been terminated.

System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated

Sectional

The value you are insert in the table is longer then the column definition – Adel

Add your sql code and target table's DDL to your question. – Elope

0x80131904 error usually occur when a char, nchar, varchar or nvarchar column set with certain limit inserted with larger amount of character from certain application. Check your table design structure first. – Norwich

Possible duplicate of SQL Server String or binary data would be truncated – Cannell

Answer 1

This exception throws when C#(model) try to save data record for column whose size defined less in SQL SERVER database table where value to pass to this column string length in greater.

To fix this error you only need to alter column of table in SQL SERVER database using SQL Server script.

Only increasing size of column in table works. No need to re deploy the application on PROD/TEST environment.

Sectional

How do i know which column is giving me the problem? I've tried DBEntityValidationException and it doesn't pick up the error. The DBUpdateException only says the problem I'm having. Is there anyway to pinpoint the exact field or column with the problem? – Larhondalari

@RobertoDurand My problem exactly. I was already certain that it was an issue of my data being larger than the DB column, but darned if I can find the particular column. – Daryldaryle

Answer 2

Please refer this sample below.

CREATE TABLE MyTable(Num INT, Column1 VARCHAR(3))

INSERT INTO MyTable VALUES (1, 'test')

Look at column1 its size is 3 but the given value is of length 4 so you would get the error.

To fix the error:

You should pass the string value less than or equal to it size ie., 3 characters like the below.

INSERT INTO MyTable VALUES (1, 'tes')

If you want to suppress this error

you can use set the below ansi_warnings parameter to off

SET ansi_warnings OFF

if we use ansi_warnings as OFF, the error would be suppressed and whatever can fit in the column, would be inserted, the rest would be truncated.

INSERT INTO MyTable VALUES (1, 'test') 

The string 'tes' would be stored in your table and it won't return any error.

Bandmaster

Answer 3

I know this is an old question, but I got the same error message and the root cause was different for me, so I'll post my solution in case others land here with the same problem.

In my app code I was adding items to a DataTable like this:

table.Rows.Add(myObj);

And what I needed to do was to add like this:

table.Rows.Add(myObj.Property1, myObj.Property2, myObj.Property3);

After changing the app code the error message went away and I was able to insert into the database.

Chalky