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

Question

1.31/5 (4 votes)

See more:

Getting following Exception what could be the reason

"String or binary data would be truncated.
The statement has been terminated."

Posted 15-Dec-13 21:04pm

Member 10310320

Updated 24-Mar-21 6:24am

Add a Solution

4 solutions

Solution 6

Just a related note.

If this happens as part of a EF migration roll-back/'Down()' method, they you can *consider* setting ANSI-Warnings to off & on before & after the column length-decrease (- 'Set Ansi_Warnings Off') - using the 'Sql()' method to do it, as the '-Force' option won't fix it; e.g.:

C#

Sql(@"Set Ansi_Warnings Off");

// Reduce/decrease column to shorter length
AlterColumn("whitelabel.EquipItems", "Name", c => c.String(nullable: false, maxLength: 128));

Sql(@"Set Ansi_Warnings On");

Posted 24-Mar-21 6:24am

Dennis McEnaney

Comments

Richard Deeming 24-Mar-21 13:27pm

If you do that, your migration will irretrievably* delete part of your data, which is almost certainly not what you want to do.

If you really do want to do that, use SQL to trim the column to the new shorter length instead:

Sql("UPDATE whitelabel.EquipItems SET Name = Left(Name, 128) WHERE Len(Name) > 128;");

* Short of restoring from a backup.

Dennis McEnaney 5-Jan-22 13:20pm

Not sure what you're stating - how is the migration truncation different from your UPDATE truncation?

Richard Deeming 6-Jan-22 3:57am

The migration truncation is implicit, and not immediately obvious to anyone looking at the code. The first you'll know about it is when users start complaining that the data has been truncated.

Doing the truncation explicitly makes your intention clear - you're deliberately going to throw away part of the data. Nobody has to read the MS docs (or the comments, if you've provided any) to work out what effect the Set Ansi_Warnings Off command will have.

It should also make the programmer stop and think before doing it. Explicitly truncating the data is more of a concious decision than turning off a set of warnings to hide a "data truncation" error.

Solution 3

You can also get help from the link: [^]

Posted 28-Jun-16 8:37am

Stack Holder

Comments

Richard Deeming 28-Jun-16 15:13pm

This question was solved 2½ years ago!

Stack Holder 28-Jun-16 15:43pm

Maybe in future someone else may get help from this question.

Marcus Kramer 28-Jun-16 15:53pm

Maybe so, but it was SOLVED 2.5 years ago. If it were left unanswered I could see the point of adding a solution, but alas, that is not the case.

Stack Holder 28-Jun-16 15:56pm

Ohhh People code for points!!!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

An@nd Rajan10 · Accepted Answer · 2013-12-15T21:28:00

check your database..
actually this problem may occurring from database
for example

colum_name data_type
---------- ---------
id int
name varchar(8)
address varchar(50)

you insert the value like 1,anand rajan,chennai
id lenght 1
name lenght 10
address length 6

here name length is exceeding because of name datatype length is 8

i hope its may be helps you..

thanks

OriginalGriff · Accepted Answer · 2013-12-15T21:15:00

Solution 1

The error message is pretty explicit: "String or binary data would be truncated"

I'm guessing that you are inserting (or updating) a row in SQL from your application. What is happening is that the data you are trying to insert does not fit in the field: if the data is text, then look at the column definition: you have a defined length of (say) 10 or 50 characters but the data you are trying to insert is longer than that.
Either increase the size of the column, or reduce the size of the data you are sending.

Posted 15-Dec-13 21:15pm

OriginalGriff

Comments

Member 14788228 5-Apr-20 13:49pm

o do it but same i am facing the problem

OriginalGriff 5-Apr-20 15:05pm

"Either increase the size of the column, or reduce the size of the data you are sending."
We can't see your code, so that is all we can tell you to do. Look at your DB, find out how big the field is, and use the debugger to find out exactly what data you are passing - not what you think you are passing, but exactly what you are passing.

Member 14846312 30-May-20 6:49am

how to modify the data type in tables

OriginalGriff 30-May-20 7:01am

First off, this is unrelated to the original question, so it needs a new question of its own.
Use this:
https://www.codeproject.com/Questions/ask.aspx
And post one of your own.

Secondly, the quality of any the response is directly proportional to the quality of the question: if you type as little as possible then you don't give us much information, and we can't really help you. Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
So tell us exactly what you are trying to do , what you have tried, what happened when you tried, and what help you need. Show us sample data, and explain what you want to see.
Help us to help you!

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

4 solutions

Solution 6

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us