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.
String or binary data would be truncated. The statement has been terminated. Simple Error
As @Krish pointed out you need to allocate space .i.e define size for each column
Create Table rs
(
Id int IDENTITY (1,1) Primary Key,
mId int Not NUll,
ad varchar(2) Not NUll,
st varchar(2) Not NUll,
et varchar(2) Not NUll,
nt varchar(max)
);
insert into rs ( nt, et, st, ad, mId)
values ('as','as','as','as',12)
OR
The default size is 1 so this should also work... but this is not what you want (it just to explain the problem)
INSERT INTO #rs ( mId, ad, st, et, nt)
VALUES (12, 'a','a', 'a', 'a')
Error: String or binary data would be truncated. The statement has been terminated
Yes, you are allowing any old string that people are typing in. You need to constrain each parameter to the maximum length allowed in that column. For example, if you don't want the error, you can truncate username to 20 characters or you can raise your own error when that parameter value is more than 20 characters. To truncate you could simply say:
Dim Username As String = txtUsername.ToString.Substring(0,20)
Or better yet, when you build your form, specify a MaxLength
for the form field that matches the column in your table. Why let a user type in more than 20 characters if your table is only designed to hold 20?
As an aside, nchar
is a terrible data choice for most of this data, unless usernames, passwords, etc. will always be exactly 20 characters. You should strongly consider nvarchar
.
Related Topics
Confusion with Oracle Connect By
What's the Simplest Way to Import an SQLite SQL File into a Web SQL Database
How to Print a Triangle of Stars Using SQL
Do We Need to Specify "Not Null" for Primary Key? Oracle/Sql
What Is the Equivalent of Varchar(Max) in Oracle
Calculate the Difference Between Results of Two Count(*) Queries Based on 2 Tables in Postgresql
Diff Between Top 1 1 and Select 1 in SQL Select Query
Postgresql: Not in Versus Except Performance Difference (Edited #2)
Postgresql - Group by Clause or Be Used in an Aggregate Function
Is There a Postgres Command to List/Drop All Materialized Views
Special Characters Displaying Incorrectly After Bulk Insert
How to Select the Last 10 Rows of an SQL Table Which Has No Id Field
Performance - Single Join Select VS. Multiple Simple Selects
Is There Is Any Performance Issue While Using Isnull() in SQL Server
How to Query in SQLite for Different Date Format