Error, String or Binary Data Would Be Truncated When Trying to Insert

error, string or binary data would be truncated when trying to insert

From @gmmastros's answer

Whenever you see the message....

string or binary data would be truncated

Think to yourself... The field is NOT big enough to hold my data.

Check the table structure for the customers table. I think you'll find that the length of one or more fields is NOT big enough to hold the data you are trying to insert. For example, if the Phone field is a varchar(8) field, and you try to put 11 characters in to it, you will get this error.

How to fix error String or binary data would be truncated in SQL Server

You should check the columns and their datatypes. One of the columns is smaller than the data you are trying to insert.

So for instance if email is a VARCHAR (10) this would break, because the input exceeds the max characters of 10.

String or binary data would be truncated on INSERT statement

You're trying to insert a description that's too long for the column's specification. Just create the column with a larget size (e.g., here 100 characters should suffice) and you should be OK:

CREATE TABLE Package
(pkgcode char(4) primary key,
 pkgname varchar(30) not null,
 pkgdescription varchar(100) not null, -- Here!
 pickuplocation varchar(20) not null,
 costperadult decimal (7,2) not null,
 costperchild decimal (7,2) not null,
 pkgremarks varchar(30) null
)

Error | String or binary data would be truncated

The column location has a limit of 20. The data you're trying to insert has a length of 24.

SELECT LEN('29 Lame Street St. James')

You might want to increase the limit for the location column.

ALTER TABLE [ticket] ALTER COLUMN [location] VARCHAR(255)

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

There are many problems in your code, not just the one that raises the current exception.

First: You have text fields with a precise size, you cannot insert more characters than the size of the field. We don't know anything about the value passed for the SupplierName but the string WebCustomer is 11 chars and you try to insert it in a field with space only for 10 chars.

Second: Using nchar means that your fields are always filled with spaces to reach the length required. Of course this is inefficient with a lot of records, not to mention the work required to trim away those spaces when you need to show your data.

Third: ExecuteNonQuery returns the number of rows affected by your command. In this case it is always 1, you don't get the return value of SELECT SCOPE_IDENTITY(). Use ExecuteScalar.

Fourth: Some of your fields are numeric, you insert strings for them. This means that the database engine try to convert them back to the correct datatype. Usually, for integers, you can go away freely, but with floats there is a higher chance that you get a datatype mismatch error caused by difference between the locale settings of your code and the locale settings of your database. Fix for that in the next point.

Fifth: You should use parameters not string concatenation. This avoids the Sql Injection hack or the parsing error caused by automatic conversion of a string back to a numeric value. A mismatch from the locale settings used in your code and the database setting will cause errors. With a parameter you specify the type and do not convert the value. The database engine is happy.

So.....(after changing to nvarchar and after checking the length of the values)

string cmdText = @"insert into tbl_Transaction_Master 
    (Supplier_ID,Order_Price,Unique_ID,
     Supplier_Name,He_Is_a,Transaction_Date) values 
    (@webid, @price,@sessionid,@user,'WebCust.',getdate()); 
    SELECT SCOPE_IDENTITY()";

SqlCommand cmd2 = new SqlCommand(cmdText, conn);
cmd2.Parameters.Add("@webid", SqlDbType.Int).Value = WebUserID 
cmd2.Parameters.Add("@price", SqlDbType.Decimal).Value = Session["Order_Price"];
cmd2.Parameters.Add("@sessionid", SqlDbType.Int).Value = Session["WebUserid"];
cmd2.Parameters.Add("@user", SqlDbType.NVarChar).Value =User;
int temp = Convert.ToInt32(cmd2.ExecuteScalar());
Session["order_ID"] = temp;

SQL Server String or binary data would be truncated

You will need to post the table definitions for the source and destination tables for us to figure out where the issue is but the bottom line is that one of your columns in the source table is bigger than your destination columns. It could be that you are changing formats in a way you were not aware of. The database model you are moving from is important in figuring that out as well.

---

Related Topics