System.Data.SqlClient.SqlException – String or binary data would be truncated

The System.Data.SqlClient.SqlException – String or binary data would be truncated is a runtime exception in C# that occurs when the application tries to insert or update data in a SQL Server database, but the data exceeds the maximum length allowed by the column. This typically happens when:

1. The data being inserted or updated is too large for the column’s defined size.
2. The column’s size in the database schema does not match the expected size in the application.

Here’s how you can troubleshoot and fix this issue:

---

1. Check Column Size in Database Schema

• Ensure that the column size in the database schema matches the expected size in the application. Example:

   ALTER TABLE MyTable
   ALTER COLUMN MyColumn VARCHAR(100); -- Increase column size

---

2. Validate Data Length Before Inserting/Updating

• Validate the length of the data before inserting or updating it in the database. Example:

   string data = "This is a very long string that exceeds the column size";
   if (data.Length <= 50) // Check against column size
   {
       // Insert or update data
   }
   else
   {
       Console.WriteLine("Data exceeds column size");
   }

---

3. Truncate Data if Necessary

• Truncate the data to fit within the column size if exceeding the limit is acceptable. Example:

   string data = "This is a very long string that exceeds the column size";
   if (data.Length > 50)
   {
       data = data.Substring(0, 50); // Truncate data
   }
   // Insert or update data

---

4. Use try-catch for Error Handling

• Use a try-catch block to handle the SqlException gracefully and log detailed error information. Example:

   try
   {
       // Insert or update data
   }
   catch (SqlException ex)
   {
       if (ex.Number == 8152) // Error number for "String or binary data would be truncated"
       {
           Console.WriteLine("Error: Data exceeds column size"); // Handle the exception
       }
   }

---

Example of Correct Code

using System;
using System.Data.SqlClient;

public class Program
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=MyDatabase;User Id=myUsername;Password=myPassword;";
        string data = "This is a very long string that exceeds the column size";

        // Example: Truncate data if necessary
        if (data.Length > 50)
        {
            data = data.Substring(0, 50); // Truncate data
        }

        try
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var command = new SqlCommand("INSERT INTO MyTable (MyColumn) VALUES (@Data)", connection);
                command.Parameters.AddWithValue("@Data", data);
                command.ExecuteNonQuery(); // Insert data
                Console.WriteLine("Data inserted successfully");
            }
        }
        catch (SqlException ex)
        {
            if (ex.Number == 8152) // Error number for "String or binary data would be truncated"
            {
                Console.WriteLine("Error: Data exceeds column size"); // Handle the exception
            }
            else
            {
                Console.WriteLine("Error: " + ex.Message); // Handle other SQL exceptions
            }
        }
    }
}

---