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. — to StackOverflow
– to StackOverflow, Commented Apr 13, 2017 at 18:01
@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
– IAmGroot, Commented Apr 14, 2017 at 9:05
This is happening when you are running update-database? Is it when you are seeding the data in the database? — trees_are_great
– trees_are_great, Commented Apr 24, 2017 at 9:29
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
– Evk, Commented Apr 24, 2017 at 12:22

Leniel Maccaferri · Accepted Answer · 2022-08-03 23:05:27Z

8

+100

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 compile 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;
   }
}

edited Aug 3, 2022 at 23:05

Leniel Maccaferri

103k48 gold badges381 silver badges495 bronze badges

answered Apr 28, 2017 at 7:58

Dryadwoods

2,9205 gold badges45 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

IAmGroot Over a year ago

As you said, its not a great solution. And would require a lot of coding for each enttity. But it does answer the question. (And is the only answer that does) +1

Dryadwoods Over a year ago

@Doomsknight yep, and I am sure that I could code you something to do this automatically for you (with reflection or so) :) But I think this is not what you are looking for.

IAmGroot Over a year ago

Going to assume that there is no ideal answer, and that I think there is an issue with the meta data, as it should catch before attempting a save. But that since this is the best given the situation, I have awarded it the bounty / correct answer to close the question.

Dryadwoods Over a year ago

Thanks for the accepted answer, I will also give you some reflection code: wait some seconds :P

Dryadwoods Over a year ago

Done, I think you get the point.... I just dont know if this will work :P in theory it should :P

|

Shaik · Accepted Answer · 2017-04-26 22:11:09Z

2

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.

answered Apr 26, 2017 at 22:11

Shaik

3942 gold badges3 silver badges11 bronze badges

Comments

Daniel Lorenz · Accepted Answer · 2017-04-13 17:55:09Z

0

ProspectName is the field that could be truncated. I don't know how that is mapped to your database, though. You could look that up in FluentApi or DataAnnotation depending on how you set it up.

You'll need to see what the MaxLength is set for this field and make sure it matches with your database max length. If it does, then you'll need to make sure you don't pass a too long of a string on this field. It could also be that the data type doesn't match between the entity and database, so check that too.

Right now, I just don't have a lot of other information to help you further.

answered Apr 13, 2017 at 17:55

Daniel Lorenz

4,3461 gold badge35 silver badges40 bronze badges

2 Comments

IAmGroot Over a year ago

Its a database first, EDMX deployment. How would i pro grammatically find which field was the issue. When this error shows.

Daniel Lorenz Over a year ago

I would right click on the entity class and Go To Definition. There, you would see ProspectName. Right click on that Find All References. You should see somewhere where this is used in Property() or something. Go there and you should see what the max length it is using. If your code lets a longer string than that get set on that property, then you will get this error every time.

IbrarMumtaz · Accepted Answer · 2018-05-04 09:51:19Z

0

Following on from @Shaik:

public MyDbContext(string connectionString, ILogger<PeopleHrDbContext> logger)
    : base(connectionString)
{
    _logger = logger;
    Database.Log = logger.Debug;
}

I am using NLog and Ioc, so you can pass a logger to your ef context and in your configured log output, you can see what the generated queries looks like. For me that is the debug output window. I have pasted in below my NLog set-up to support this.

  <targets>
    <target xsi:type="Debugger" name="d" 
            layout="${longdate} ${logger} ${uppercase:${level}} ${message}" />
  </targets>

  <rules>
    <logger name="*" minlevel="Trace" writeTo="d" />
  </rules>

answered May 4, 2018 at 9:51

IbrarMumtaz

4,46210 gold badges48 silver badges66 bronze badges

4 Comments

Auspex Over a year ago

I've got that, and it doesn't help you to find the DB column.

IbrarMumtaz Over a year ago

I think the idea is print out your sql queries to the output window and then manuall copy and paste into SSMS and run it locally to see what is going wrong with the sql.

Auspex Over a year ago

Well, I had the query, and I had the values. Since it was EF core, it was batching the updates in random quantities (well, I presume not random, but an algorithm that's opaque to the programmer), and so all I could tell was that it was in one of 83 rows. Since it wasn't the first row, I know it wasn't all rows. Sure, I could have narrowed it down with a binary search in no more than 9 steps. Since I didn't actually need all 25 columns, I narrowed those down with a binary search, and fortunately found I didn't have to have the problem column. It's still tedious & should be unnecessary..

IbrarMumtaz Over a year ago

@Auspex - sorry dude - dont have much experience with efcore. My post for anyone needing help in EF6 debugging column issues. It automagically prints out sql queries to defined logger in the background.

Collectives™ on Stack Overflow

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

4 Answers 4

9 Comments

Comments

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related