Error: String or binary data would be truncated? That does that mean?

Question

_{Friday, August 10, 2007 10:01 AM}

I get the following error in this code:

Code Snippet

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        PhotoTableAdapter.Insert(SaveToAlbumID, OpenFileDialog1.FileName, PhotoDescription.Text, "PhotoTaken", "Added", 5, 0, "Viewed", "2007", "Mars", "25", "11:14")
        Me.PhotoTableAdapter.Update(Me.PhotoDataSet.Album)
    End Sub

Sqlexception was unhandled
String or binary data would be truncated
The statement has been terminated

It's the INSERT line that generates the error.
What does this mean?

All replies (2)

_{Friday, August 10, 2007 12:09 PM ✅Answered | 1 vote}

It means that you are attempting to insert a value into a (likely) text field that is longer than the maximum alowed length. Go to the table's definition and look at the length of your char,varchar,nvarchar fields -- I suspect it may be the one that receives the "PhotoDescription.Text value" -- and take note of the field length (i.e. Varchar(50)).  If the value your are inserting is longer than the maximum then you will get that exception. 

One way to avoid that, if you know the expected field lengths, might be to limit the user's capacity to enter mode than the allowed number of characters -- the textbox has a MaximumCharacters property.  The next best option is to perform a validation before allowing the insert.  If the PhotoDescription field allows 1000 characters then do something like this:

If txtPhotoDescription.Text.Length > 1000 Then txtPhotoDescription.Text = txtPhotoDescription.Text.Substring(0,1000)

As a final note, if you are not already handling it, you should be aware of the potential for Sql Injection where a user can enter harmful Sql keywords into inputted text.  Even if this program is for your own personal use it is worth getting used to the idea of always coding for Sql Injection for the time it really does matter.

_{Saturday, August 11, 2007 7:54 AM}

Thanks mate. That was correct.