DbUpdateException: Which field is causing "String or binary data would be truncated"

Question

I am getting a DbUpdateException with message

String or binary data would be truncated

I understand that one of the fields in the entity won't fit the length of the column in the database. And that I could go down and check them manually.

What I am trying to do however, is get a sensible error message which might tell me which field it actually is! E.g.

String or binary would be truncated at field ProspectName.

I am able to print out a lot of random information. and have tried various stuff. But nothing points to the field name.

Please note this is NOT of type DbEntityValidationException, it is a DbUpdateException

// DbUpdateException exception
foreach (var entry in exception.Entries)
{ 
    builder.AppendLine(String.Format("Error at: Type {0}", entry.Entity.GetType().Name));

    if ((exception.InnerException is System.Data.Entity.Core.UpdateException) &&
        (exception.InnerException.InnerException is System.Data.SqlClient.SqlException))
    {
        var updateException = (System.Data.Entity.Core.UpdateException)exception.InnerException;

        var sqlException = (System.Data.SqlClient.SqlException)exception.InnerException.InnerException;
        var result = new List<ValidationResult>();

        for (int i = 0; i < sqlException.Errors.Count; i++)
        {
            builder.AppendLine(String.Format("Error code: {0} ", sqlException.Errors[i].Number));
            builder.AppendLine(String.Format("Source: {0} ", sqlException.Errors[i].Source));
            builder.AppendLine(String.Format("Message: {0} ", sqlException.Errors[i].Message));
            builder.AppendLine(String.Format("State: {0} ", sqlException.Errors[i].State));
            builder.AppendLine(String.Format("Procedure: {0} ", sqlException.Errors[i].Procedure));
        } 
    }
}

Complete error:

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

Error at: Type tree_1ECACDBB4458C7A9DEC7CD183FD8B8C3473502FEFFACF160E17AD47718DCE5EA
Error code: 8152
Source: .Net SqlClient Data Provider
Message: String or binary data would be truncated.
State: 14
Procedure:
Error code: 3621
Source: .Net SqlClient Data Provider
Message: The statement has been terminated.
State: 0
Procedure:

You could consider temporarily having SET ANSI_WARNINGS OFF. Then run the query and inspect what was written to the database, and you may be able to visually identify the offending column. — Joe
@Joe How would I programmatically get it to tell me which field though? I understand i can manually check. But we have many old db deployments that have varied field lengths. And it would be nice if i could be told by the program which field, since it knows one is overflowing. — IAmGroot
This is happening when you are running update-database? Is it when you are seeding the data in the database? — trees_are_great
@Sam I get this error, trying to update an entity in the database. No doubt, one of the fields is set too long. But how would you get it to report in the error, which field. — IAmGroot
Max length should be respected and throw validation error. Maybe it's not that column which causes problem? Maybe you have some triggers? Hard to tell given the information you provided. You will have to debug this as usual (remove code until you get minimal code to reproduce it). — Evk

Dryadwoods Dryadwoods · Accepted Answer · 2017-04-28T07:58:00

One "ugly" solution (but functional and using ONLY C# code) to find exactly which property is giving you that error would be:

In case you are doing an update do this:

 var myDBObj = db.Mytables.Where(x=>x.Id == myId).FirstOrDefaul();
 if(myDBObj == null) return false; // or something else with the error msg

 myDBObj.Property1 = myObjToSave.Property1;
 db.SaveChanges();

 myDBObj.Property2 = myObjToSave.Property2;
 db.SaveChanges();

 myDBObj.Property3 = myObjToSave.Property3;
 db.SaveChanges();
 .... // EACH PROPERTY....
 myDBObj.PropertyX = myObjToSave.PropertyX;
 db.SaveChanges();

Now, with a brake point or an incremental variable to track the "position", etc... you will know exactly in this specific case where you have the exception....

NOTE: this is an ugly solution JUST to track down where it is failling... NEVER use this in production... and of course IMO there are other more friendly things like having a sql profiler and see the generated SQL and then try to run it on the sql management studio and then see the error there......

UPDATE #1

Something like this (note: I did not compiled it) :P

Type type = obj.GetType();
PropertyInfo[] properties = type.GetProperties();  
string lastPropertyWithError = ""; // You can replace this with a list or so  
foreach (PropertyInfo property in properties)
{
   try{
    property.SetValue(myDBObj, property.GetValue(myObj, null));
    db.SaveChanges();
   }catch()
   {
     lastPropertyWithError  = property.Name;
   }
}

DbUpdateException: Which field is causing "String or binary data would be truncated"

4 Answers