Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL String or Binary Data Would be Truncated. The Statement has been Terminated

Sep 7, 2012
Introduction: 

Here I will explain how to solve the problem of “string or binary data would be truncated. The statement has been terminated.” in sql server. 

Description:

One day I am trying to insert data into one table using SQL queries at that time I got error like “String or binary data would be truncated. The statement has been terminated.” Actually this problem because of I declared column datatype varchar(25) but I am inserting data more than 25 characters in that column. To solve this problem I modified column datatype varcha (25) to varchar(50)

I will explain with one example I have query like this

DECLARE @UserDetails TABLE(UserId INT, UserName VARCHAR(25),Designation VARCHAR(10))
INSERT INTO @UserDetails(UserId,UserName,Designation)
VALUES(1,'Suresh Dasari','Senior Software Engineer')
SELECT * FROM @UserDetails
If you observe above query I declared Designation field with VARCHAR(10) and inserting more than 10 characters because of that I got error like

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

(0 row(s) affected)
To solve this problem I changed Designation datatype VARCHAR(10) to VARCHAR(50) and run the below query  
DECLARE @UserDetails TABLE(UserId INT, UserName VARCHAR(25),Designation VARCHAR(50))
INSERT INTO @UserDetails(UserId,UserName,Designation)
VALUES(1,'Suresh Dasari','Senior Software Engineer')
SELECT * FROM @UserDetails
Once I run above query I got output like this
Happy Coding………

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

15 comments :

Manoj said...

gud one...

Anonymous said...

Thanks a lot for sharing.

Unknown said...

YOU SAVED MY TONS OF TIME..... THANK YOU

Narendran Namachivayam said...

Thanks a lot,
Useful information.

But am struggling,am having nearly 50+ columns, is there any query to find the column that the length which is getting exceed?


Narendran N

Anonymous said...

Thanks a lot :)

Unknown said...

Many many thank u sir for sharing this problem .....

Unknown said...

Thank's

Unknown said...

thank you i got the very good solution

Unknown said...

Thanks!!

Anonymous said...

Thank you, It works for me... :)

akash said...

thanks!!!

Anonymous said...

thanks and very usefull sir

Unknown said...

Simple and easy :)

Unknown said...

good Simplest

Unknown said...

I want to insert the video in the data base and m finding this error String or binary data would be truncated.
The statement has been terminated. Please can anybody tell me what data type should be used to insert hd video in the database with the size of more than 1 gb?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.