"String or binary data would be truncated." linq exception, cant find which field has exceeded max length

Question

String or binary data would be truncated. linq exception, cant find which field has exceeded max length.

i have around 350 fields. i checked each and every textbox maxlength with database field maxlength, everything seems to be correct, but i still get the exception.

please help

Answer 1

Make use of SQLPROFILER and check generated insert query will provide you more help to resolve issue you have.

Step-By-Step: An introduction to SQL Server Profiler

Answer 2

Troubleshooting this error with 350 fields can be extremely difficult, and SQL Server Profiler isn't much help in this case (finding the long string in the generated SQL is like finding a needle in a haystack).

So, here is an automated way to find the actual strings that are exceeding the database size limit. This is a solution that's out there on the internet, in various forms. You probably don't want to leave it in your production code, since the attribute/property searching is pretty inefficient, and it'll add extra overhead on every save. I'd just throw it in your code when you encounter this problem, and remove it when you're done.

How it works: it iterates over all properties on an object you're about to save, finding the properties with a LINQ to SQL ColumnAttribute. Then, if the ColumnAttribute.DbType contains "varchar", you know it's a string and you can parse that part of the attribute to find the maximum length.

Here's how to use it:

            foreach (object update in context.GetChangeSet().Updates)
            {
                FindLongStrings(update);
            }

            foreach (object insert in context.GetChangeSet().Inserts)
            {
                FindLongStrings(update);
            }

            context.SubmitChanges();

And here's the method:

    public static void 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)
                        Console.WriteLine(testObject.GetType().Name + "." + propInfo.Name + " " + currentValue + " Max: " + maxLength);

                }
            }
        }
    }

Answer 3

If you checked the max length of every textbox to the max length of every field, it is entirely possible the error is happening through a trigger. Are there triggers on the table?