How to Solve String or Binary Data Would Be Truncated the Statement Has Been Terminated VB Net

Leave a comment

String or Binary Data would be truncated (Error number 8152) is a common error. Usually, it occurs once you try to insert any data in a string (char,nchar, varchar,nvarchar) data type column that is more than the size of the column. In this case, you have to check the data size with respect to the column width and identify which column is making an issue and solve it.

CAUSE OF THE ERROR NUMBER 8152

The String or Binary Data would be truncated error happens when the value persisted in a field is higher (in character count) than the one the database column max value allows. For instance if there is a simple text field which by default generates a 256 max char column in the database, the error is going to be thrown if entering in this field 257+ characters.

CAUSE OF THE ERROR NUMBER 8 1 5 2

HOW TO SOLVE THE ERROR NUMBER 8152

This happens when C#(model) tries to save data records for columns whose size is defined less in SQL SERVER database table where the value to pass to this column string length is bigger. To solve this error you only need to change column of the table in SQL SERVER database by using SQL Server script. Only increasing the size of the column in the table works. You do not need to redeploy the application on the Prod/Test environment.

Actually, it is very easy if you are dealing with less columns in a table. However, it becomes nightmare if you are dealing with insert into a query with big number of columns and you have to check one by one column. 

We received this query from one of our readers asking if there is a shortcut to solve this problem and give the column name along with the data making issues. We started searching for the solution but could not get a proper one. So, we started developing this solution. Before proceeding with the solution, we would like to make a sample to demonstrate the issue.

Now, you are able to see the sample below.

Create Table My table (Num Int, Column1 Varchar 3)

Insert Into Mytable Values (1, Test)

You need to look at column1 its size is 3. However the given value is of length 4. So, you are going to get the error.

To solve the error, you are able to follow these steps below:

  • At the first step, you have to pass the string value less than or equal to its size, 3 characters like the below.

Insert Into My Table Values (1, Tes)

  • If you want to compress this error, you are able to set the ansi warnings parameter to Off.
  • If you use ansi_warnings parameter as Off, the error will be compressed and whatever is able to suitablein the column. It is going to be inserted and the rest is going to be truncated.
  • Now, the string test is going to be stored in your table and it will not return any error.

ANOTHER SOLUTION

The error message does not contain adequate information regarding which field length value is causing the error. In order to troubleshoot and determine which field value which is causing the error, you have to revise the data which is entered once making the content. Try embellishment the values of the fields one by one until the saving of the content passes successfully.

  • When the error occurs for the made through the backend, you are able to go through the field inputs and delete the values one by one and perform the saving operation after each deletion until the saving is successful.
  • When the error occurs for the items made through the API, you are able to go through the values received as an input or static values which were set in the code and perform the same process of deleting values one by one.

To make you understand what text field of the document contains the long string which prevents the document publishing refer to the  file: DocumentExtractor zip. For your information, it contains a webform with Sitefinity API which is going to report on the length of the string properties of a document and show them to the user. Doing this, it is able to be determined which field contains a lot of symbols easily.

In the text below, we are going to share how to use the DocumentExtractor aspx. Apparently, there are some steps that you need to do to use the DocumentExtractor aspx. Here are steps:

  • Firstly, add it to the site files. For note: it does not need compilation, you only need add it and use it directly by requesting it by URL.
  • After that, you have to log in to Sitefinity with a user who has permissions to manage documents.
  • In this step, you need to fill in the textbox with the name of the Document Library where this document is. if the document is in subfolders under the top library do not care about those, only the top level library is enough)
  • The next step that you have to do is to fill in the textbox for the document Title and the language in which the error occurs. If the website does not use multiple languages (in case it has only the default language EN) you are able to let us know and try later.
  • The form is going to output the details for this document and its text properties and their length.

Well, this is an explanation on how to solve the error number 8152. If you have this problem, you do not hesitate to try doing those steps above. As we said before that the error is a very common error. It means that you are not alone, lots of other people who have got this problem. In solving the error, you have to ensure that you do steps by steps correctly and completely. With this, we are sure that you are able to solve your problem.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *