Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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:

like image 265
IAmGroot Avatar asked Apr 13 '17 16:04

IAmGroot


1 Answers

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;
   }
}
like image 196
Dryadwoods Avatar answered Oct 04 '22 18:10

Dryadwoods