SQLException : String or binary data would be truncated

In general, there isn't a way to determine which particular statement caused the error. If you're running several, you could watch profiler and look at the last completed statement and see what the statement after that might be, though I have no idea if that approach is feasible for you.

In any event, one of your parameter variables (and the data inside it) is too large for the field it's trying to store data in. Check your parameter sizes against column sizes and the field(s) in question should be evident pretty quickly.

BEGIN TRY
    INSERT INTO YourTable (col1, col2) VALUES (@val1, @val2)
END TRY
BEGIN CATCH
    --print or insert into error log or return param or etc...
    PRINT '@val1='+ISNULL(CONVERT(varchar,@val1),'')
    PRINT '@val2='+ISNULL(CONVERT(varchar,@val2),'')
END CATCH

It depends on how you are making the Insert Calls. All as one call, or as individual calls within a transaction? If individual calls, then yes (as you iterate through the calls, catch the one that fails). If one large call, then no. SQL is processing the whole statement, so it's out of the hands of the code.

I have created methods to:

1. Get the column width of all the columns of a table where we're trying to make this insert/ update. (I'm getting this info directly from the database.)
2. Compare the column widths to the width of the values we're trying to insert/ update.

Step 1:

Get the column width of all the columns directly from the database:

// I took HUGE help from this Microsoft docs website: - AshishK
// https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=netframework-4.7.2#System_Data_SqlClient_SqlConnection_GetSchema_System_String_System_String___
private static Dictionary<string, int> GetColumnSizesOfTableFromDatabase(string tableName, string connectionString)
{
    var columnSizes = new Dictionary<string, int>();
            
    using (var connection = new SqlConnection(connectionString))
    {
        // Connect to the database then retrieve the schema information.  
        connection.Open();

        // You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
        // You can use four restrictions for Column, so you should create a 4 members array.
        String[] columnRestrictions = new String[4];

        // For the array, 0-member represents Catalog; 1-member represents Schema;
        // 2-member represents Table Name; 3-member represents Column Name.
        // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
        columnRestrictions[2] = tableName;

        DataTable allColumnsSchemaTable = connection.GetSchema("Columns", columnRestrictions);

        foreach (DataRow row in allColumnsSchemaTable.Rows)
        {
            var columnName = row.Field<string>("COLUMN_NAME");
            var dataType = row.Field<string>("DATA_TYPE");
            var characterMaxLength = row.Field<int?>("CHARACTER_MAXIMUM_LENGTH");

            // I'm only capturing columns whose Datatype is "varchar" or "char", i.e. their CHARACTER_MAXIMUM_LENGTH won't be null.
            if(characterMaxLength != null)
            {
                columnSizes.Add(columnName, characterMaxLength.Value);
            }
        }

        connection.Close();
    }

    return columnSizes;
}

Step 2:

Compare the column widths with the width of the values we're trying to insert/ update:

public static Dictionary<string, string> FindLongBinaryOrStringFields<T>(T entity, string connectionString)
{
    var tableName = typeof(T).Name;
    Dictionary<string, string> longFields = new Dictionary<string, string>();
    var objectProperties = GetProperties(entity);
    //var fieldNames = objectProperties.Select(p => p.Name).ToList();

    var actualDatabaseColumnSizes = GetColumnSizesOfTableFromDatabase(tableName, connectionString);
            
    foreach (var dbColumn in actualDatabaseColumnSizes)
    {
        var maxLengthOfThisColumn = dbColumn.Value;
        var currentValueOfThisField = objectProperties.Where(f => f.Name == dbColumn.Key).First()?.GetValue(entity, null)?.ToString();

        if (!string.IsNullOrEmpty(currentValueOfThisField) && currentValueOfThisField.Length > maxLengthOfThisColumn)
        {
            longFields.Add(dbColumn.Key, $"'{dbColumn.Key}' column cannot take the value of '{currentValueOfThisField}' because the max length it can take is {maxLengthOfThisColumn}.");
        }
    }

    return longFields;
}

public static List<PropertyInfo> GetProperties<T>(T entity)
{
    //The DeclaredOnly flag makes sure you only get properties of the object, not from the classes it derives from.
    var properties = entity.GetType()
                            .GetProperties(System.Reflection.BindingFlags.Public
                            | System.Reflection.BindingFlags.Instance
                            | System.Reflection.BindingFlags.DeclaredOnly)
                            .ToList();

    return properties;
}

Usage:

Let's say we're trying to insert someTableEntity of SomeTable class that is modeled in our app like so:

public class SomeTable
{
    [Key]
    public long TicketID { get; set; }
    public string SourceData { get; set; }
}

And it's inside our SomeDbContext like so:

public class SomeDbContext : DbContext
{
    public DbSet<SomeTable> SomeTables { get; set; }
}

This table in Db has SourceData field as varchar(16) like so:

Now we'll try to insert value that is longer than 16 characters into this field and capture this information:

public void SaveSomeTableEntity()
{
    var connectionString = "server=SERVER_NAME;database=DB_NAME;User ID=SOME_ID;Password=SOME_PASSWORD;Connection Timeout=200";
        
    using (var context = new SomeDbContext(connectionString))
    {
        var someTableEntity = new SomeTable()
        {
            SourceData = "Blah-Blah-Blah-Blah-Blah-Blah"
        };
        
        context.SomeTables.Add(someTableEntity);
        
        try
        {
            context.SaveChanges();
        }
        catch (Exception ex)
        {
            if (ex.GetBaseException().Message == "String or binary data would be truncated.\r\nThe statement has been terminated.")
            {
                var badFieldsReport = "";
                List<string> badFields = new List<string>();
                
                // YOU GOT YOUR FIELDS RIGHT HERE:
                var longFields = FindLongBinaryOrStringFields(someTableEntity, connectionString);

                foreach (var longField in longFields)
                {
                    badFields.Add(longField.Key);
                    badFieldsReport += longField.Value + "\n";
                }
            }
            else
                throw;
        }
    }
}

The badFieldsReport will have this value:

'SourceData' column cannot take the value of 'Blah-Blah-Blah-Blah-Blah-Blah' because the max length it can take is 16.

Most of the answers here are to do the obvious check, that the length of the column as defined in the database isn't smaller than the data you are trying to pass into it.

Several times I have been bitten by going to SQL Management Studio, doing a quick:

sp_help 'mytable'

and be confused for a few minutes until I realize the column in question is an nvarchar, which means the length reported by sp_help is really double the real length supported because it's a double byte (unicode) datatype.

i.e. if sp_help reports nvarchar Length 40, you can store 20 characters max.

Simply Used this: MessageBox.Show(cmd4.CommandText.ToString()); in c#.net and this will show you main query , Copy it and run in database .

Checkout this gist. https://gist.github.com/mrameezraja/9f15ad624e2cba8ac24066cdf271453b.

public Dictionary<string, string> GetEvilFields(string tableName, object instance)
    {
        Dictionary<string, string> result = new Dictionary<string, string>();

        var tableType = this.Model.GetEntityTypes().First(c => c.GetTableName().Contains(tableName));

        if (tableType != null)
        {
           int i = 0;

           foreach (var property in tableType.GetProperties())
           {
               var maxlength = property.GetMaxLength();
               var prop = instance.GetType().GetProperties().FirstOrDefault(_ => _.Name == property.Name);

               if (prop != null)
               {
                   var length = prop.GetValue(instance)?.ToString()?.Length;

                   if (length > maxlength)
                   {
                        result.Add($"{i}.Evil.Property", prop.Name);
                        result.Add($"{i}.Evil.Value", prop.GetValue(instance)?.ToString());
                        result.Add($"{i}.Evil.Value.Length", length?.ToString());
                        result.Add($"{i}.Evil.Db.MaxLength", maxlength?.ToString());
                        i++;
                    }
               }
            }
       }

       return result;
    }