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

c# entity-framework entity-framework-6 exception

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:

1
7
4/13/2017 5:24:01 PM

Accepted Answer

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;
   }
}
6
4/28/2017 8:44:13 AM

Popular Answer

Using Entity framework profiler to capture the sql queries from the entity framework, find your update query and run it against the database directly, you have good chances of finding the field.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow