SQL Error: String or binary data would be truncated. The statement has been terminated.

Posted Date: 18 Jul 2009	Resource Type: Errors and Solutions	Category: General
Author: Viji RAJKUMAR	Member Level: Diamond
Rating:	Points: 4

Problem Description:

The SQL Server error "String or binary data would be truncated. The statement has been terminated" happens when try to update/insert the sql table column with data having length more than the column length.

Consider the following example:

The table dgDownloadStatus consists of the following fields:

Column Name Data Type Length Allow Nulls

DownloadStatus nvarchar 50 1
FinishDownloadStatus nvarchar 50 1
ErrorDescription nvarchar 50 1

Here we have allocated 50 characters length for ErrorDescription column.

Assume the following error message: (Length greater than 50)

strErrorMessage = "Source SQL Connection Opening Failed: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

But When we try to update the table with the following command



Try

 strSql = "UPDATE dpGroupTable SET DownloadStatus = 'Failed', ErrorDescription='" & _
                strErrorMessage & "' WHERE [Group] = '" & sGroupName & _
             "' and [Table]='" & sTableName & "'"


            ErrSqlCmd = New SqlCommand(strSql, connection)
            ErrSqlCmd.ExecuteNonQuery()


You will get an error String or binary data would be truncated. The statement has been terminated.


Catch ex as Exception

    Msgbox ex.Message  '---- Here the exception error will be thrown

End Try

Solution Description:

1. Make sure the length of the column is long enough to hold the large error message.

2. Increase the length of the column

3. Or update the error message with the number of characters less than or equal to the length of the columns

Responses to the resource: "SQL Error: String or binary data would be truncated. The statement has been terminated."

Author: Mayura Sangeene 04 Nov 2009 Member Level: Bronze Points : 1

Your Ans is really useful to me, the problem was of Column width only.

Thanks and Regards
Mayura Sangeene

Author: Viji RAJKUMAR 04 Nov 2009 Member Level: Diamond Points : 1

Mayura,

glad to hear that my solution helped you.

Yes the problem is with column width , but we do get the error message in a different manner so that we might get confused..

Regards,

Viji Rajkumar

Feedbacks

Popular Tags

What are tags ?

Search Tags

SQL Error: String or binary data would be truncated. The statement has been terminated. .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: .NET Error: Unable to Read the Project File
	Previous Resource: Cross-thread operation not valid: "Control.CheckForIllegalCrossThreadCalls"
	Return to Discussion Resource Index
	Post New Resource
	Category: General

Post resources and earn money!

More Resources