Chris Love's Official ASP.NET Blog

String or binary data would be truncated

I am sure just about any of us that have done any development work against a database, and who hasn’t, has come across this error, ‘String or binary data would be truncated’. This is really a warning more than it is an error, but will stop any insert of update operation dead in its tracks if ANSI warnings are on, which typically they will be. The real issue is tracking down what column is causing the issue, which when you have one or two columns it is not too big of an issue. But typically there will be more columns involved, making it tough.

The issue occurs when you try to add or change the value of a character based field and the value has more characters than is allocated for the field. The other issue can be when you are trying to store a binary value, like a file, in a field that is too small to hold it. Basically, the problem is you are trying to store data that is too big!

Danny demonstrates a very good technique in tracking down what field is causing the truncation warning to be thrown, http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx. I recommend visiting that article to learn more about dealing with it in SQL Server.

First let me say, you should validate the size of your string or value before you try to store it in the database. Client validation is one thing, but of course that can be thwarted by most hackers. The business object should enforce any size limits. I typically check this in a property called IsValid I use in every one of my business objects. I admit I am not the most consistent with this, but I use it when I know it can be an issue.

In this example I use a combination of data checking methods to make sure my strings are within tolerance. In this example I have an Event entity that needs to have three fields, Title of 1 to 100 characters, Description of at least 1 character and an Event Date that is more than the min Date value, validated before it is considered a valid entity.

Public ReadOnly Property IsValid() As Boolean Implements IBaseEntity.IsValid
    Get
        If Helpers.IsValidString(Me.EventTitle, 1, 100) And _
          String.IsNullOrEmpty(Me.EventDesc) = False And _
           Me.EventDate > Date.MinValue Then
            Return True
        End If
        Return False
    End Get
End Property

The Description field maps to an ntext field, so it can be large, but I want it to have at least something in it. The String.IsNullOrEmpty (worth jumping to the 2.0 CLR if you are still 3 years behind alone!) is one of my favorite methods to validate a string with.

Dates are a tricky situation and I normally use the SmartDate class from CSLA to work around them. But in this case just making sure I have something more than the minimum allowed value for a date is acceptable. I think honestly you could set some sort of minimum value in the application’s configuration, but for this I will leave it as it is.

Finally I have a Helper class in use in this project. It contains a series of shared (static for your C# folks) methods that are used for a variety of things. In this case I have a method named IsValidString. It takes a string value, a minimum and maximum value and determines if the length criteria are met.

Public Shared Function IsValidString(ByVal vValue As String, ByVal vMax As Integer) As Boolean
    Return IsValidString(vValue, 0, vMax)
End Function

Public Shared Function IsValidString(ByVal vValue As String, ByVal vMin As String, ByVal vMax As Integer) As Boolean
    If vMin > 0 Then
        If String.IsNullOrEmpty(vValue) AndAlso vMin <= vValue.Length Then
            Return False
        End If
    End If
    If vMax < vValue.Length Then
        Return False
    End If
    Return True
End Function

Now, before I get folks leaving comments about extension methods, I know. Let me cover those in another post.

These concepts can can be extended to create a large series of validation methods that are general, or very specific to your application. I also want to point out this can be extended even further to include format validations, such as e-mail, phone numbers and passwords. This is important because more important that a potential truncation of a value is the potential for a SQL Injection attack. Good validation should always be in play to avoid a potential hack.

I have some other post in the works to keep extending these concepts, so stay tuned!

Share this post :