sql gives string or binary data would be truncated

Question

User-1202579601 posted

I used table insert query. Pasted it multiple times ans changed the values. Some queries working properly while others give string or binary data truncated.

Unable to detect the error.

Answer 1

User475983607 posted

The error means you are trying to INSERT data that is larger than the column declaration.  Usually, the error has a lot more information like the column.

Answer 2

User-893317190 posted

Hi pmdrait,

I have made a test, the exception could be detected.

Below is my code . My test table only have a column of type nvarchar(50).

  private static string constr = ConfigurationManager.ConnectionStrings["EntityDb"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
     

       
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand com = new SqlCommand("insert into [testData] values (@data)", con))
                {
                   
                    string test = "";
                    for (int i = 0; i < 100; i++)
                    {
                        test += "test";
                    }
                    com.Parameters.AddWithValue("data", test);
                    con.Open();
                    com.ExecuteNonQuery();
                }
            }

    }

The result.

Maybe you are using try catch , so  the server couldn't show you the error message.

As mgebhard  has said,the error means you are trying to INSERT data that is larger than the column declaration.Please change the datatype of your column so that the column could store more data.

Best regards,

Ackerly Xu