none
Find exact column sql error from linq RRS feed

  • Question

  • Well I'm hoping someone knows the answer.

     

    I make changed to a table via linq

     

    I then call db.SubmitChanges() on the Data Context and i get the following error.

     

    String or binary data would be truncated.

     

    Does anyone know how to find the exact column that is conflicting?

     

    Friday, March 28, 2008 3:08 PM

Answers

All replies

  • Hello,

    First, if you use LINQ To SQL, you should go in this forum.

    For your problem, I think you have a property (probably of type string) with has a value too long for your DB column.

    For example, you have "azerty" for property mapped on a nvarchar(2) DB column.

    Friday, March 28, 2008 3:30 PM
  • For your question, you have a SqlException and I have not found how retrieving column from this SqlException.

    Friday, March 28, 2008 3:53 PM
  • you can't.

     

    i don't have the link with me, but you can vote on adding it to the next version of sql. katmei or something

     

     

     

     

    Friday, March 28, 2008 6:05 PM
  •  

    If you can find the link please post it because this problem has been a big nuisance for me, thanks.
    Friday, March 28, 2008 7:22 PM
  • If you're having a Linq to SQL problem why post in the Linq general forum?

    Friday, March 28, 2008 7:24 PM
  • https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=353232

     

     

    Search Helper: "String or binary data would be truncated."

    Saturday, July 26, 2008 6:25 AM
  • As is noted in the connect item, this is a shortcoming in the error messages that SQL Server returns. It's not something that LINQ can give much help with otherwise.

     

    Jim Wooley

    www.ThinqLinq.com

     

    Monday, July 28, 2008 9:24 PM
    Moderator
  • Jim,

     

    It might be a limitation in SQL Server but I respectfully disagree that is not something Linq to SQL can help with.

     

    Linq to SQL knows the schema of the Table(s) and Column(s).  It knows the data types and sizes.  I'm pretty sure it has the information it needs to track down the problem.

     

    Regards,

    Mike D.

    Wednesday, July 30, 2008 7:37 PM
  • First finding the exact column is a problem that sql has and Microsoft has failed for 3 years to do anything about even though tons of complaints have been filed.

     

    I did not know that for some odd reason Microsoft has chosen to ignore the most annoying issue ever.

     

    There is a ticket placed in the suggestions forums somewhere. Hundreds of people have voted on it.

     

     

     

    Thursday, July 31, 2008 2:13 AM
  • Great news... I installed SP1 for .Net 3.5 and guess what... they provide detailed information now!!!

     

    Example: "Cannot insert the value NULL into column 'FullQualifiedUserName', table 'WcmsDevEmpty.Users.Users'; column does not allow nulls. INSERT fails.
    The statement has been terminated."

     

    Monday, September 8, 2008 2:31 PM
  • I've got this problem on .net 4.0 - no details just "String or binary data would be truncated." So it works only on 3.5 sp1 strange...
    Tuesday, July 6, 2010 10:14 AM
  • @mayrand, I think I was incorrect and was mixing up exceptions.  I don't think anything has changed since the initial release.

    This is bummer...

    What I've been doing in my code is add OnValidate, check if insert or update and then check the string lengths.


    Regards, Mike DePouw
    Tuesday, July 6, 2010 5:22 PM
  • The 'string or binary data would be truncated' is a SQL Server warning. It would be nice if it was more specific but for now (SQL2008) that's about as specific as it gets. More on that in this thread: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ec184168-ddd1-4df5-b2c7-c6671b602a3d

    Now, what we can do with Linq-to-SQL is to add some checks in SubmitChanges to detect when we're about to pass a too long value to a char/nchar/varchar/nvarchar field. E.g.:

    partial class SomeDataContext
    {
      public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
      {
        //get hold of the change set
        ChangeSet cs = this.GetChangeSet();
    
        //types that we will check for maxlength
        Type[] typesToCheck = new Type[] { typeof(string), typeof(System.Data.Linq.Binary) };
    
        //get pending inserts and updates along with the members that can have a max length
        var insertsUpdates = (
          from i in cs.Inserts.Union(cs.Updates)
          join m in this.Mapping.GetTables() on i.GetType() equals m.RowType.Type
          select new { Entity = i, Members = m.RowType.DataMembers.Where(dm => typesToCheck.Contains(dm.Type)).ToList() }
          ).Where(m => m.Members.Any()).ToList();
    
        //check all pending inserts and updates for members containing a value longer than the allowed max length for the db column
        foreach (var ins in insertsUpdates)
        {
          foreach (System.Data.Linq.Mapping.MetaDataMember mm in ins.Members)
          {
            int maxLength = GetMaxLength(mm.DbType);
            if (mm.MemberAccessor.HasValue(ins.Entity))
            {
              int memberValueLength = GetMemberValueLength(mm.MemberAccessor.GetBoxedValue(ins.Entity));
              if (maxLength > 0 && memberValueLength > maxLength)
              {
                InvalidOperationException iex = new InvalidOperationException("Member " + mm.Name + " in " + mm.DeclaringType.Name + " has a value that will not fit into column " + mm.MappedName + " (" + mm.DbType + ")");
                throw iex;
              }
            }
          }
        }
    
        base.SubmitChanges(failureMode);
      }
    
      private int GetMaxLength(string dbType)
      {
        int maxLength = 0;
        if (dbType.Contains("("))
        {
          dbType = dbType.Substring(dbType.IndexOf("(") + 1);
        }
        if (dbType.Contains(")"))
        {
          dbType = dbType.Substring(0, dbType.IndexOf(")"));
        }
        int.TryParse(dbType, out maxLength);
        return maxLength;
      }
    
      private int GetMemberValueLength(object value)
      {
        if (value.GetType() == typeof(string))
        {
          return ((string)value).Length;
        }
        else if (value.GetType() == typeof(Binary))
        {
          return ((Binary)value).Length;
        }
        else
        {
          throw new ArgumentException("Unknown type.");
        }
      }
    }
    

     


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    • Proposed as answer by TechStuffBC Thursday, August 5, 2010 4:26 PM
    Wednesday, July 7, 2010 2:24 AM
    Answerer
  • The posted solution needs to be changed as follows:

    • Change "LinqDataContextPartialClass" to the actual name of the DataContext you have (e.g. "PersonDataContext", etc.).
    • Also, KrisitoferA, the method GetMemberValueLength has a bug.  It crashes if the "value" is null, so I added a null check ==>  if(value == null) { return 0;}

    public partial class LinqDataContextPartialClass : System.Data.Linq.DataContext
    {
     public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
     {
     //get hold of the change set
     ChangeSet cs = this.GetChangeSet();
    
     //types that we will check for maxlength
     Type[] typesToCheck = new Type[] { typeof(string), typeof(System.Data.Linq.Binary) };
    
     //get pending inserts and updates along with the members that can have a max length
     var insertsUpdates = (
     from i in cs.Inserts.Union(cs.Updates)
     join m in this.Mapping.GetTables() on i.GetType() equals m.RowType.Type
     select new { Entity = i, Members = m.RowType.DataMembers.Where(dm => typesToCheck.Contains(dm.Type)).ToList() }
     ).Where(m => m.Members.Any()).ToList();
    
     //check all pending inserts and updates for members containing a value longer than the allowed max length for the db column
     foreach (var ins in insertsUpdates)
     {
     foreach (System.Data.Linq.Mapping.MetaDataMember mm in ins.Members)
     {
     int maxLength = GetMaxLength(mm.DbType);
     if (mm.MemberAccessor.HasValue(ins.Entity))
     {
     int memberValueLength = GetMemberValueLength(mm.MemberAccessor.GetBoxedValue(ins.Entity));
     if (maxLength > 0 && memberValueLength > maxLength)
     {
     InvalidOperationException iex = new InvalidOperationException("Member '" + mm.Name + "' in '" + mm.DeclaringType.Name + "' has a value that will not fit into column '" + mm.MappedName + "' (" + mm.DbType + ")");
     throw iex;
     }//if (maxLength > 0 && memberValueLength > maxLength)
     }//if (mm.MemberAccessor.HasValue(ins.Entity))
     }//foreach (System.Data.Linq.Mapping.MetaDataMember mm in ins.Members)
     }//foreach (var ins in insertsUpdates)
    
     base.SubmitChanges(failureMode);
     }//override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    
    
     private int GetMaxLength(string dbType)
     {
     int maxLength = 0;
    
     if (dbType.Contains("(")) { dbType = dbType.Substring(dbType.IndexOf("(") + 1); }
     if (dbType.Contains(")")) { dbType = dbType.Substring(0, dbType.IndexOf(")")); }
     int.TryParse(dbType, out maxLength);
     return maxLength;
     }//int GetMaxLength(string dbType)
    
     private int GetMemberValueLength(object value)
     {
     if (value == null) { return 0; }
     if (value.GetType() == typeof(string)) { return ((string)value).Length; }
     else if (value.GetType() == typeof(Binary)) { return ((Binary)value).Length; }
     else { throw new ArgumentException("Unknown type."); }
     }//int GetMemberValueLength(object value)
    }//partial class LinqDataContextPartialClass : System.Data.Linq.DataContext

     

    I'm not happy that Microsoft didn't include this in their L2S solution.

    "Hi, we're Microsoft. We'll tell you the general problem, or code a general solution, and that's all she (er, we) wrote.  Later!"

    • Proposed as answer by TechStuffBC Thursday, August 5, 2010 4:26 PM
    • Edited by TechStuffBC Thursday, August 12, 2010 3:37 PM change C# code
    Thursday, August 5, 2010 4:14 PM
  • Thanks a lot for sharing this long awaited sample!

    Once in a while, one spends painfull ours debugging Transact-SQL scripts and we do know that many times things just take much longer than they should, just because SQL Server developers/decisors never paid due attention to make SQL Error messages more precise; unfortunately, this kind of stuff does not seem to be valuable enough to get their attention and to save our neuroniums from burning.

    In my opinion, your solution would be perfect if it was actived only when target exception occurs. Therefore, I suggest base.SubmitChanges(failureMode) method should be called, leaving your code to be activated whenever an SqlConnectionException occurs.

    Hope this helps someone

    namespace YourNameSpace
    {
      partial class YourLinqDBContext
      {
    
        public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
        {
    
          try // because performance matters, offending column should only be search when corresponding exception occurs.
          {
            base.SubmitChanges(failureMode);
          }
    
          catch (System.Data.SqlClient.SqlException sqlex)
          {
    
            if (sqlex.Number == 8152) // String or binary data would be truncated
              LocateOffendingColumn();
            else
              throw sqlex;
          }
    
        }//override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    
    
        private void LocateOffendingColumn()
        {
          //get hold of the change set
          ChangeSet cs = this.GetChangeSet();
    
          //types that we will check for maxlength
          Type[] typesToCheck = new Type[] { typeof(string), typeof(System.Data.Linq.Binary) };
    
          //get pending inserts and updates along with the members that can have a max length
          var insertsUpdates = (
          from i in cs.Inserts.Union(cs.Updates)
          join m in this.Mapping.GetTables() on i.GetType() equals m.RowType.Type
          select new { Entity = i, Members = m.RowType.DataMembers.Where(dm => typesToCheck.Contains(dm.Type)).ToList() }
          ).Where(m => m.Members.Any()).ToList();
    
          //check all pending inserts and updates for members containing a value longer than the allowed max length for the db column
          foreach (var ins in insertsUpdates)
          {
            foreach (System.Data.Linq.Mapping.MetaDataMember mm in ins.Members)
            {
              int maxLength = GetMaxLength(mm.DbType);
              if (mm.MemberAccessor.HasValue(ins.Entity))
              {
                int memberValueLength = GetMemberValueLength(mm.MemberAccessor.GetBoxedValue(ins.Entity));
                if (maxLength > 0 && memberValueLength > maxLength)
                {
                  string ErrorMessage="Member '" + mm.Name + "' in '" + mm.DeclaringType.Name + "' has a value that will not fit into column '" + mm.MappedName + "' (" + mm.DbType + ")";
                  object OffendingRow = ins;
                  InvalidOperationException iex = new InvalidOperationException(ErrorMessage);
                  throw iex;
                }//if (maxLength > 0 && memberValueLength > maxLength)
              }//if (mm.MemberAccessor.HasValue(ins.Entity))
            }//foreach (System.Data.Linq.Mapping.MetaDataMember mm in ins.Members)
          }//foreach (var ins in insertsUpdates)
        }
    
        private int GetMaxLength(string dbType)
        {
          int maxLength = 0;
    
          if (dbType.Contains("(")) { dbType = dbType.Substring(dbType.IndexOf("(") + 1); }
          if (dbType.Contains(")")) { dbType = dbType.Substring(0, dbType.IndexOf(")")); }
          int.TryParse(dbType, out maxLength);
          return maxLength;
        }//int GetMaxLength(string dbType)
    
        private int GetMemberValueLength(object value)
        {
          if (value == null) { return 0; }
          if (value.GetType() == typeof(string)) { return ((string)value).Length; }
          else if (value.GetType() == typeof(Binary)) { return ((Binary)value).Length; }
          else { throw new ArgumentException("Unknown type."); }
        }//int GetMemberValueLength(object value)  }
      }
    }
    
    

     

     

     

    Tuesday, February 22, 2011 12:42 PM
  • In my opinion, your solution would be perfect if it was actived only when target exception occurs. Therefore, I suggest base.SubmitChanges(failureMode) method should be called, leaving your code to be activated whenever an SqlConnectionException occurs.


    One argument against doing it only when a SqlException is raised would be that the cost of an unnecessary db roundtrip (cross process or cross machine RPC call, query compilation, data validation etc) plus the cost of the exception is high. Maybe it won't save a lot in real life, but a db roundtrip is much more (by magnitudes) costly than the simple validation code in this code sample (which adds very little overhead on top of what L2S already does client side when building the insert/update statements)...


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    Thursday, February 24, 2011 3:28 AM
    Answerer