'string or binary data would be truncated' in C# but not in SSMS

Question

I have a piece of code which deletes and inserts a record into a table. The INSERT though always fails with the message "string or binary data would be truncated".

I do know that this error is typically caused by trying to write a string that's longer than the column's maximum length. But the strange thing is, that the same query works correctly when I execute it "manually" through SQL Server Management Studio.

Here's my table:

CREATE TABLE [dbo].[a_settings](
  [a_setting_name] [nvarchar](30) NOT NULL,
  [a_setting_value] [nvarchar](80) NULL
)

and the query I am using:

DELETE FROM [a_settings] WHERE [a_setting_name] = 'x1';
INSERT INTO [a_settings] ([a_setting_name], [a_setting_value])
  VALUES ('x1', '0000000000000000000000000000000000000000');

and the C# code I am using:

SqlCommand cmd =
  new SqlCommand(
    String.Format(@"
      DELETE FROM [{0}] WHERE [a_setting_name] = @setting_name;
      INSERT INTO [{0}] ([a_setting_name], [a_setting_value]) VALUES (@setting_name, @setting_value)",
      TBL_SETTINGS),
        Connection);
cmd.Parameters.Add(new SqlParameter("setting_name", "x1"));
cmd.Parameters.Add(new SqlParameter("setting_value", "0000000000000000000000000000000000000000"));
cmd.ExecuteNonQuery();

Without making you have to count them: there are 40 zeroes.

The thing is: if I reduce the number of zeroes to something like 30, it works. But with 40 I get the "string or binary data would be truncated". In SSMS though, it works either way.

EDIT: Initially I used this code instead of the one shown above with SQL-Parameters

SqlCommand cmd =
  new SqlCommand(
    String.Format(@"
      DELETE FROM [{0}] WHERE [a_setting_name] = '{1}';
      INSERT INTO [{0}] ([a_setting_name], [a_setting_value]) VALUES ('{1}', '{2}')",
      TBL_SETTINGS, 'x1', '0000000000000000000000000000000000000000'),
        Connection);
cmd.ExecuteNonQuery();

And I then placed a breakpoint, copied the value of cmd.CommandText and pasted it right into SMSS (removing the \r and \t chars) and ran the query: it worked. In C# it did not.

Are you sure the query succeeds in SSMS? Three fields and two values is a bad combination. — Dan Bracuk
– Dan Bracuk, Commented Jan 11, 2014 at 15:35
Did you try with the @ at the start of the parameter name? Also, you can explicitly give the SqlDbType for the parameter type and its size. — Andrew Morton
– Andrew Morton, Commented Jan 11, 2014 at 15:48
Really strange indeed. Since the column is defined as nvarchar(80) I would not expect to get an error when adding 40 characters. — Mikael Eriksson
– Mikael Eriksson, Commented Jan 11, 2014 at 16:00

Andrew Morton · Accepted Answer · 2014-01-11 16:16:16Z

2

If instead you add your parameters as follows, does it work?

cmd.Parameters.Add(new SqlParameter("@setting_name", SqlDbType.NVarChar, 30).Value = "x1");
cmd.Parameters.Add(new SqlParameter("@setting_value", SqlDbType.NVarChar, 80).Value = new String('0', 40));

answered Jan 11, 2014 at 16:16

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2017-07-21 16:47:24Z

0

I had a very similar problem, with the only difference being that my code was calling a stored procedure. It took a while to identify the issue.

In the end, I found that my stored procedure was automatically populating "created by" and "updated by" fields with the currently logged in user. The C# code ran as a completely different user than I was when I was using SSMS. When running in C#, the username was too long for the field. When in SSMS, it was not. Increasing the size of those fields fixed the issue.

answered Jul 21, 2017 at 16:47

David

11 bronze badge

Collectives™ on Stack Overflow

'string or binary data would be truncated' in C# but not in SSMS

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related