字符串或二进制数据将被截断.该语句已终止.插入桌子时 [英] String or binary data would be truncated. The statement has been terminated. when inserting into the table
问题描述
在我的应用程序中，我从table1中获得一行，然后在视图中显示它，然后在回复之后，将回复插入到表2中，并从表1中删除条目，并从table1中获得下一个问题.
In my application I am getting one row from table1 displaying it in the view and then after reply I am inserting the reply into table 2 and deleting the the entry from table 1 and getting the next question from table1.
我遇到了错误:
字符串或二进制数据将被截断.该声明已终止
String or binary data would be truncated. The statement has been terminated
在httppost方法中.我已经通过应用断点检查了post方法中tempdata的值，并且没有问题.数据库中的值的类型为 nvarchar ，但id为 int 类型.
in the httppost method. I have checked the values of the tempdata in the post method by applying a breakpoint and there is no problem with it.
The values in the database are of type nvarchar except the id which is of int type.
我无法找到为什么我仍然收到错误消息.该错误显示在execute方法中，但我无法找到其背后的原因.
I am not able to find why I still get the error. The error shows in the execute method but I am not able to find the reason behind it.
我要插入的东西大部分是字符串，有些可能在字符串中包含特殊字符，例如 * ， \ 等，直至700个字符.
The things that I am inserting are mostly strings and some may contain special character in the string like * , \ etc and up to 700 characters.
PS:目前我已经忽略了SQL注入威胁
PS: Presently I have ignored SQL injection threat
[HttpGet]
public ActionResult Index()
{ string connstr = "Here is the connection string";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand(" Select top(1) Id , Body , Email_subject , Queue , Intent , Tagging FROM
table1 ");
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var Did = reader["Id"].ToString();
TempData["id "] = Int64.Parse(Did);
TempData["subject "] = reader["Email_subject"].ToString();
TempData["body"] = reader["Body"].ToString();
TempData["intent"] = reader["Intent"].ToString();
TempData["queue"] = reader["Queue"].ToString();
TempData["tagging"] = reader["Tagging"].ToString();
}
conn.Close();
TempData.Keep();
return View();
}
[HttpPost]
public ActionResult Index(string Correctornot)
{ TempData.Keep();
string connstr = My connection String;
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT table2 ON ; INSERT INTO table2 ( Id ,Body, Response ,Queue , Intent , Tagging , Email_subject) VALUES ( '" + TempData["id"] + "' , '" + TempData["body"] + "'
, '" + Correctornot + "' , '" + TempData["Queue"] + "' , '" + TempData["intent"] + "' , '" + TempData["tagging"] + "' , '" + TempData["subject"] + "');DELETE FROM table1 where Id = '" + TempData["id"] + "' ;");
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
SqlDataReader reader2 = cmd.ExecuteReader();
SqlCommand cmd2 = new SqlCommand(" Select top(1) Id , Body , Email_subject , Queue , Intent , Tagging FROM table1");
cmd2.CommandType = System.Data.CommandType.Text;
cmd2.Connection = conn;
SqlDataReader reader3 = cmd2.ExecuteReader();
while (reader3.Read())
{
var Did = reader3["Id"].ToString();
TempData["id "] = Int64.Parse(Did);
TempData["subject "] = reader3["Email_subject"].ToString();
TempData["body"] = reader3["Body"].ToString();
TempData["intent"] = reader3["Intent"].ToString();
TempData["queue"] = reader3["Queue"].ToString();
TempData["tagging"] = reader3["Tagging"].ToString(); }
conn.Close();
TempData.Keep();
return View(); }
解决方案:
我能够解决问题，但我仍然不知道其背后的原因.
I was able to solve the problem but I still don't know the reason behind it.
问题是由于单击按钮返回的值所致.尽管该值不太大(只是正确"和不正确")，但是当我尝试将其插入数据库时，却给了我错误.
The problem was due to the value returned by clicking the button. Though the value was not too large ( it was just "correct " and "not correct") , when i tried to insert it into the database it gave me the error .
我通过使用switch语句解决了这一问题，而不是直接将其添加到insert语句中.
I solved it by using a switch statement instead of directly adding it to the insert statement.
即
switch (Correctornot)
{
case "Correct":
sql = "Insert INTO [dbo].[Labeler_Email_For_confirmation_Agents](Body , Response , Queue , Intent , Tagging , Email_subject ) Values ( '" + TempData["body"].ToString() + "' , 'yes' , '" + TempData["queue"].ToString() + "' , '" + TempData["intent"].ToString() + "' , '" + TempData["tagging"].ToString() + "' , '" + TempData["email_subject"].ToString() + "');";
break;
case "Not Correct":
sql = "Insert INTO [dbo].[Labeler_Email_For_confirmation_Agents](Body , Response , Queue , Intent , Tagging , Email_subject ) Values ( '" + TempData["body"].ToString() + "' , 'no' , '" + TempData["queue"].ToString() + "' , '" + TempData["intent"].ToString() + "' , '" + TempData["tagging"].ToString() + "' , '" + TempData["email_subject"].ToString() + "');";
break;
}推荐答案
SQL Server 2016 SP2 CU6和SQL Server 2017 CU12为了返回截断警告的详细信息，引入了跟踪标志460.您可以在查询级别或服务器级别启用它.
SQL Server 2016 SP2 CU6 and SQL Server 2017 CU12 introduced trace flag 460 in order to return the details of truncation warnings. You can enable it at the query level or at the server level.
查询级别
INSERT INTO dbo.TEST (ColumnTest)
VALUES (‘Test truncation warnings’)
OPTION (QUERYTRACEON 460);
GO
服务器级别
DBCC TRACEON(460, -1);
GO
在SQL Server 2019中，您可以在数据库级别启用它:
From SQL Server 2019 you can enable it at database level:
ALTER DATABASE SCOPED CONFIGURATION
SET VERBOSE_TRUNCATION_WARNINGS = ON;
旧的输出消息是:
Msg 8152, Level 16, State 30, Line 13
String or binary data would be truncated.
The statement has been terminated.
新的输出消息是:
Msg 2628, Level 16, State 1, Line 30
String or binary data would be truncated in table 'DbTest.dbo.TEST', column 'ColumnTest'. Truncated value: ‘Test truncation warnings‘'.
在将来的SQL Server 2019版本中，消息2628将默认替换消息8152.
In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.
这篇关于字符串或二进制数据将被截断.该语句已终止.插入桌子时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
