错误：字符串或二进制数据将被截断。该语句已终止。 ASPX [英] Error: String or binary data would be truncated. The statement has been terminated. aspx

问题描述

我想将数据插入到我tbl_Transaction_Master表。
这里是我的数据库表tbl_Transaction_Master

i want to insert data into my tbl_Transaction_Master table. here is my database table tbl_Transaction_Master

和我的数据库插入命令查询

and my query for database insert command is

 SqlCommand cmd2 = new SqlCommand("insert into tbl_Transaction_Master(Supplier_ID,Order_Price,Unique_ID,Supplier_Name,He_Is_a,Transaction_Date) values ('" + WebUserID + "','" + Session["Order_Price"] + "','" + Session["WebUserid"] + "','"+User+"','WebCustomer',getdate()); SELECT SCOPE_IDENTITY()", conn);
                int temp = cmd2.ExecuteNonQuery();
                Session["order_ID"] = temp;

我收到错误如上所述。

i am getting the error as mentioned above.

推荐答案

有在code诸多问题，只是引发当前异常的人们不。

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

第一：您有一个precise大小的文本字段，可以不超过字段的大小插入多个字符。我们不知道被传为SupplierName但是字符串 WebCustomer 的任何有价值的东西是11个字符，并尝试插入它在空间领域只为10个字符。

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.

二：使用 NCHAR 意味着你的领域总是充满了空间，达到所需的长度。当然，这是低效用了很多纪录，更何况当你需要展示你的数据修剪掉那些空间所需的工作。

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.

第三：返回的ExecuteNonQuery受你的命令行数。在这种情况下，它始终是1，你没有得到 SELECT SCOPE_IDENTITY的返回值（）。使用的ExecuteScalar。

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.

第四：你的一些字段是数字，你插入字符串他们。这意味着该数据库引擎试图将它们转换回正确的数据类型。通常情况下，对于整数，你可以走自由，但花车还有就是你获得由您code的区域设置和数据库的区域设置的差异数据类型不匹配错误的机会较高。在接下来的点修复了这一点。

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.

第五：您应该使用参数不字符串连接。这避免了SQL注入攻击或造成一个字符串返回到一个数值的自动转换解析错误。从您的code使用的区域设置和数据库设置不匹配将导致错误。有了一个参数指定的类型和不换算的数值。数据库引擎是幸福的。

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.

所以.....（改变为nvarchar后检查值的长度后）

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;

这篇关于错误：字符串或二进制数据将被截断。该语句已终止。 ASPX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！