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 Article With Your Friends!

Googles Ads Facebook Pixel Bing Pixel LinkedIn Pixel