Last Updated - Thu Nov 23 2017

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!