2

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

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

Community
  • 1
  • 1
Alok Kumar
  • 413
  • 1
  • 6
  • 11
  • 2
    The value you are insert in the table is longer then the column definition – Jens Jul 01 '16 at 06:04
  • Add your sql code and target table's DDL to your question. – Ivan Starostin Jul 01 '16 at 06:08
  • 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. – Tetsuya Yamamoto Jul 01 '16 at 06:21
  • 1
    Possible duplicate of [SQL Server String or binary data would be truncated](https://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated) – Sufian Jul 31 '17 at 14:43

2 Answers2

4

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.

Alok Kumar
  • 413
  • 1
  • 6
  • 11
  • 3
    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? – Roberto Durand Aug 20 '18 at 14:47
  • @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. – Auspex May 08 '19 at 15:13
1

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.

StackUser
  • 5,093
  • 1
  • 17
  • 35