EF Exception: String or binary data would be truncated. The statement has been terminated.?

Question

I have read many posts related to this issue, but couldn't find an answer. I am trying to load a large amount of data from Excel into SQL Server. Thousands of records. And I am getting this exception:

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

Obviously some values exceed the field size in the database. The error comes from SQL Server AFIK.

My question - How could I possibly know what record and what field value caused this?

There are no specific details in EF exception, except the one I mentioned.

Any help is appreciated.

Some asked for the code fragment, but it's actually very simple, the problem is not with the code:

// employees is a List<Employee> collection loaded from Excel
using (var context = new Entities())
{
    employees.ForEach(e => context.Employee.AddObject(e));
    context.SaveChanges();
}

Also the suggested approach to use DbEntityValidationException (which is only available in Entity Framework 5.0) is not working, the catch block didn't catch the exception.

try
{
    ImportData();
}
catch (DbEntityValidationException ex)
{
    foreach (var item in ex.EntityValidationErrors)
    {
        //...
    }
}

The only solution that I found so far is to use SQL Server Profiler, and define the following events to monitor:

enter image description here

Now I can see the Email is too long.

Debug record by record until you find the offender, and check each field of the record against the db columns? — yoozer8, Nov 14 '12 at 14:43
Please provide more information for example code sample, Data Structure and DataType of the fields you are trying to import.. this will make it easier in my opinion to lend some sort of direction — MethodMan, Nov 14 '12 at 14:43
There is no way you can debug record by record using EF. It is performed as one transaction. — monstro, Nov 14 '12 at 14:58
I wanted to upvote your answer, but it's part of the question:) Anyway, this technique worked perfectly for me. Thanks for your help! — Chris Vesper, Mar 11 '16 at 19:10
I didn't find any answers that compared column widths directly from the database table to the widths of values we're trying to insert, so I went ahead and created methods in C# to do just that. Take a look below: https://stackoverflow.com/a/71342036/8644294 — Ash K, Mar 03 '22 at 18:27

score 5 · Answer 1 · answered Nov 14 '12 at 15:02

5

You can't at that level. SQL Server is rejecting the entire query.

I would add some pre-checks to the data against your database constraints for string size, date formats, etc.

Alternatively you could TRIM each string field in the raw data to the corresponding field size before trying to insert.

answered Nov 14 '12 at 15:02

D Stanley

149,601
11
178
240

I can't perform any data cleanup or correction. All I need is to find which value is causing the problem and expand corresponding field in the database. – monstro Nov 14 '12 at 15:31
Can you get the length of each string in the data to see which ones are longer that the destination field? – D Stanley Nov 14 '12 at 16:08

score 4 · Answer 2 · answered Nov 14 '12 at 14:58

4

catch (DbEntityValidationException ex)
{
    foreach (var item in ex.EntityValidationErrors)
    {
        //... inspect here 
    }
}

You can find the information you need inside foreach loop.

Hope that helps.

answered Nov 14 '12 at 14:58

Cuong Nguyen

333
2
10

Thanks, Just tried, but with catch is not intercepting the exception. – monstro Nov 14 '12 at 15:26
1

If this error is not raised in the DbEntityValidationException then it means that model is not in sync with the database. Try recreating the model from the database because it's still the easiest way to determine the problematic field. – Ciprian Teiosanu Oct 17 '13 at 09:16
1

Same here. On my SaveChanges in EF6.01 where one field is too long, I get a SqlException as the inner exception inside a UpdateException inside a DbUpdateException. My model is in sync. – Dave Jul 11 '14 at 03:09
13

Minus one - because the returned exception is not type of `DbEntityValidationException` so the answer is not helpful for this question. – mimo Apr 13 '16 at 15:37
not hte exception catched – phil123456 Oct 11 '21 at 13:51

score 1 · Answer 3 · edited Apr 02 '19 at 14:36

1

You can check data before saving, using EF metadata, and raise appropriate error.

edited Apr 02 '19 at 14:36

Lynn Crumbling

12,985
8
57
95

answered Apr 13 '14 at 06:10

Ben

2,454
26
32

score 0 · Answer 4 · answered Mar 09 '15 at 14:34

Not sure about truncation specifically, but here is a tip for when you get an exception that tells you to examine EntityValidationErrors. Usuaully when debugging it won't let you see that property (unless you already had an explicit catch). However, you can open up quick watch and type $exception. Now you should be able to drill in and find that property. You can also just type the following:

(System.Data.Entity.Validation.DbEntityValidationException)$exception

score 0 · Answer 5 · answered Aug 12 '16 at 04:20

private static string FindLongStrings(object testObject)
    {
        foreach (PropertyInfo propInfo in testObject.GetType().GetProperties())
        {
            foreach (ColumnAttribute attribute in propInfo.GetCustomAttributes(typeof(ColumnAttribute), true))
            {
                if (attribute.DbType.ToLower().Contains("varchar"))
                {
                    string dbType = attribute.DbType.ToLower();
                    int numberStartIndex = dbType.IndexOf("varchar(") + 8;
                    int numberEndIndex = dbType.IndexOf(")", numberStartIndex);
                    string lengthString = dbType.Substring(numberStartIndex, (numberEndIndex - numberStartIndex));
                    int maxLength = 0;
                    int.TryParse(lengthString, out maxLength);

                    string currentValue = (string)propInfo.GetValue(testObject, null);

                    if (!string.IsNullOrEmpty(currentValue) && currentValue.Length > maxLength && lengthString!="max")
                        return testObject.GetType().Name + "." + propInfo.Name + " " + currentValue + " Max: " + maxLength;

                }
            }
        }
        return "";
    }


foreach (object insert in dtx.GetChangeSet().Inserts)
            {
                string result = FindLongStrings(insert);
                if (string.IsNullOrEmpty(result) == false)
                {
                    responseBuilder.Append(result);
                }
            }

If responseBuilder is not empty, then it contains the field name, allowed length and error message.

Your solution requires the field width information to be defined in the entity model (for eg: `[Column("SomefieldName", TypeName = "varchar(10)")]` annotation in `SomefieldName` property or in the `modelBuilder`), so sadly this didn't work out for me. So I went ahead and created methods to **get the column width information directly from the database table**. Checkout my answer here: https://stackoverflow.com/a/71342036/8644294 — Ash K, Mar 03 '22 at 18:32

score 0 · Answer 6 · edited Sep 30 '20 at 06:21

0

I have got that problem twice, and the problem is that when EF creates the table in the database, it sets varchar(1) to string attributes, so when i try to insert information it is not possible because of the length, I recommend you to check the fields in the table

edited Sep 30 '20 at 06:21

nahidf

2,260
1
15
22

answered Sep 29 '20 at 18:23

user14363141

11

This is far from any standard EF behavior so it has to do with circumstances you don't mention, making this not suitable for an answer. And "check the fields in the table" has been said before (and is too obvious to mention). – Gert Arnold Sep 30 '20 at 06:35

EF Exception: String or binary data would be truncated. The statement has been terminated.?

6 Answers6

Linked