C# & SQL Server Insert ExecuteNonQuery: which column is causing the error "String or Binary Data Would Be Truncated?"

Question

I'm trying to insert into a table in a C# program. I have this insert command:

var insertSql = @"INSERT INTO dbo.[Case]
                  VALUES (@Id, @IsDeleted, @CaseNumber, @ContactId, @AccountId, @ParentId, @SuppliedName, @SuppliedEmail, @SuppliedPhone, @SuppliedCompany, @Type, @RecordTypeId, @Status, @Reason, @Origin...

And then I've got many lines adding in the parameters like so:

var command = new SqlCommand(insertSql, easySoftConn);

if (case2.Id != null)
    command.Parameters.AddWithValue("@Id", case2.Id);
else
    command.Parameters.AddWithValue("@Id", DBNull.Value);

if (case2.IsDeleted != null)
{
    if (case2.IsDeleted == "true")
        command.Parameters.AddWithValue("@IsDeleted", 1);
    else
        command.Parameters.AddWithValue("@IsDeleted", 0);
}
else
    command.Parameters.AddWithValue("@IsDeleted", DBNull.Value);

if (case2.CaseNumber != null)
    command.Parameters.AddWithValue("@CaseNumber", case2.CaseNumber);
else
    command.Parameters.AddWithValue("@CaseNumber", DBNull.Value);

if (case2.ContactId != null)
    command.Parameters.AddWithValue("@ContactId", case2.ContactId);
else
    command.Parameters.AddWithValue("@ContactId", DBNull.Value);       
...

When I finally execute the insert:

try
{
    command.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException e)
{
    CLog.Write(e.Message.ToString(), CLog.ErrLvl.Error);...
}

I get the error:

String or binary data would be truncated

My issue is, the error doesn't tell me which column would be truncated. I've got 80 columns I'm inserting into, and I'd rather not go through them one-by-one. Is there a way to get the error handling to tell me exactly which field is throwing the error?

EDIT: I have a full stack trace in my log file but it still doesn't tell me which column, I just shortened it to the actual error here.

Answer 1

Looks like the value of one or more of your parameters has more length than the table cell can contain. You should to look at table column definitions.

Answer 2

Depending on your SQL version you can apply a KB to get this to show more data as stated here - Link

It effectively starts to show messages like the following

Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.

This came from this great post Link which goes much further to explain how you can try and search for the column should this not be possible. The post also talks about how you can do manual searching although I'd imagine if the list of columns is too large that may be something you want to avoid.

Answer 3

Switching to using strongly typed data access would head this one off sooner:

• Add a dataset file to your project
• Open it, right click the surface, add tableadapter, set connection parameters, add a query of SELECT * FROM [Case]
• FInish the wizard, a datatable and adapter are generated. The DB is used to drive the creation, so all the string columns have a MaxLength property in the dataset that comes from the DB
• Attempting to add a row to this table will now cause an error like "unable to set column XYZ, the value violates the MaxLength limit for the column"

Data access code looks like:

var dt = new YourDataSetNameHere.CaseDataTable();
dt.AddCaseRow(put, your, values, here, you , dont, need, to worry, about, null, this, or, data, type, that, because, VS, handles, it, all, for, you, in, the, DataSet.Designer.cs, file);

new YourDataSetNameHereTableAdapters.CaseTableAdapter().Update(dt); //save the new row;

So it'll save you a boatload of time writing boring data access code too