CODEWITHSUNDEEP
c#sql-serverentity-frameworkexception
Slava
Slava

Reputation: 6640

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

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

enter image description here

Now I can see the Email is too long.

Upvotes: 63

Views: 49609

Answers (6)

user14363141
user14363141

Reputation: 11

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

Upvotes: 0

Ben
Ben

Reputation: 2484

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

Upvotes: 1

Ali
Ali

Reputation: 1225

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.

Upvotes: 0

ShawnFumo
ShawnFumo

Reputation: 2178

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

Upvotes: 0

D Stanley
D Stanley

Reputation: 152556

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.

Upvotes: 5

Cuong Nguyen
Cuong Nguyen

Reputation: 331

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

You can find the information you need inside foreach loop.

Hope that helps.

Upvotes: 3

Related Questions