String or binary data would be truncated. The statement has been terminated. when inserting into the table

Question

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.

I am getting the error:

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

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.

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.

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: 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(); }

Solution:

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 .

I solved it by using a switch statement instead of directly adding it to the insert statement.

i.e

            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;

   
            }

Answer 1

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.

Query level

INSERT INTO dbo.TEST (ColumnTest)
VALUES (‘Test truncation warnings’)
OPTION (QUERYTRACEON 460);
GO

Server Level

DBCC TRACEON(460, -1);
GO

From SQL Server 2019 you can enable it at database level:

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = 150
ALTER DATABASE SCOPED CONFIGURATION 
SET VERBOSE_TRUNCATION_WARNINGS = ON;

The old output message is:

Msg 8152, Level 16, State 30, Line 13
String or binary data would be truncated.
The statement has been terminated.

The new output message is:

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‘'.

In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.

Answer 2

"String or binary data would be truncated. The statement has been terminated" is reporting that one of the values you are trying to insert is too large for the SQL Column. For example, you'll see this error if you try insert "Hello, World" into a SQL Column that can only hold 4 characters. Truncate the value before inserting, or change the datatype of the SQL Column.