Click here to Skip to main content
15,917,859 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I Am executing this query but getting error ..

C#
insert into tbl_item_rate
select convert(varchar(50),itemcode),convert(numeric(18,2),pricetoretailer),
convert(numeric(18,2),mrp),convert(numeric(18,2),standardrate),
convert(numeric(18,2),pricetostockiest),convert(datetime,'2015/01/01',103),null
from r2

Error :
C#
String or binary data would be truncated.
The statement has been terminated.
Posted
Updated 20-Mar-15 0:34am
v3
Comments
Thanks7872 20-Mar-15 6:34am    
Its means, you are inserting more data than it can handle. In very simple words, you have declared some column as varchar(10) and storing data with length more than 10.
Saral S Stalin 20-Mar-15 6:34am    
Can you post the schema of tbl_item_rate and r2
Member 11337367 20-Mar-15 6:45am    
CREATE TABLE [dbo].[tbl_item_rate](
[c_Item_code] [varchar](10) NOT NULL,
[n_rate] [numeric](18, 2) NULL,
[n_mrp] [numeric](18, 2) NULL,
[n_std_rate] [numeric](18, 2) NULL,
[n_pts_rate] [numeric](18, 2) NULL,
[d_date_from] [datetime] NOT NULL,
[d_date_to] [datetime] NULL,
CONSTRAINT [PK_tbl_item_rate] PRIMARY KEY CLUSTERED
(
[c_Item_code] ASC,
[d_date_from] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[r2](
[itemcode] [varchar](50) NULL,
[itemname] [varchar](50) NULL,
[shortname] [varchar](50) NULL,
[itemtype] [float] NULL,
[companyname] [varchar](50) NULL,
[description] [varchar](150) NULL,
[pack] [float] NULL,
[pricetoretailer] [float] NULL,
[mrp] [float] NULL,
[standardrate] [float] NULL,
[pricetostockiest] [float] NULL,
[brandcode] [nvarchar](255) NULL,
[categorycode] [nvarchar](255) NULL,
[divisioncode] [varchar](50) NULL,
[groupcode] [nvarchar](255) NULL,
[nrvrate] [nvarchar](255) NULL,
[manufacturercode] [nvarchar](255) NULL,
[qtyperstrip] [nvarchar](255) NULL
) ON [PRIMARY]



As you have provided the schema's the problem is very clear now.

tbl_item_rate first column is [c_Item_code] [varchar](10) NOT NULL, you are inserting a varchar(50) column r2.ItemCode to it.

Either you increase the size of c_item_code to varchar(50) or as the way you write change
SQL
convert(varchar(50),itemcode)
to
SQL
convert(varchar(10),itemcode)


Note that this will cause a truncation in value which is present in itemcode but your code wont fail :)
 
Share this answer
 
v3
Comments
[no name] 20-Mar-15 6:58am    
"Either you increase the size of c_item_code to varchar(50) (best thing)"

You should be cautious with that kind of advice. You don't know if he's "playing around" with his own local DB or if he works on something bigger, potentially in a company. A column length restriction might be in place for a very good reason and the proper solution might be to restrict the input length.
Saral S Stalin 20-Mar-15 7:01am    
Noted your point. Thanks for the comment.
[no name] 20-Mar-15 7:07am    
You're welcome.
SQL SERVER – A quick solution to ‘String or binary data would be truncated’ using Stored procedure[^] can help you figure out the exact column that is creating this error.
 
Share this answer
 
Look at your database, and the size of the columns you declared - NVARCHAR columns for example dafualt to a maximum of 50 characters, so if you try to insert more than that into the column, it will fail because the whole text will not fit: Hence "String or binary data would be truncated." SQL will not throw away data without telling you...

It's also a very good idea to name the columns you are inserting into as part of your INSERT statement:
SQL
INSERT INTO MyTable (Column1Name, Column2Name) VALUES ('value for C1', 'value for c2')
If you don't, the SQL will insert the values in the current column order, starting with the top / left most and proceeding sequentially. It will not try to "skip" columns if the type is inappropriate, and your error may be related to that as well.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900