I am trying to update a column in a SQL Server DB from C# that is defined as nVarChar(max)
. The update works as long as the text length is 4000 characters or less. As soon as the length is 4001 I get the infamous error:
String or binary data would be truncated.
The statement has been terminated.
The update will work if it is run manually from within SQL Server Management Studio.
The C# code calls a SP to perform the update. I listed all the relevant code below:
CREATE TABLE [dbo].[UpdateTable]
(
[PK_ID] [int] IDENTITY(1,1) NOT NULL,
[NEWTEXT] [nvarchar](max) NULL,
CONSTRAINT [PK_UpdateTable]
PRIMARY KEY CLUSTERED ([PK_ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--DATABASE STORED PROCEDURE
CREATE Procedure [dbo].[usp_UpdateText]
@ID int,
@NEWTEXT nvarchar (max)
AS
SET NOCOUNT ON
UPDATE [UpdateTable]
SET [NEWTEXT] = @NEWTEXT
WHERE PK_ID = @ID;
RETURN
C# DB code:
using System.Data;
using System.Data.SqlClient;
public static void UpdateText(AjaxObjects.TextObject obj)
{
SqlConnection connection = DataBase.GetConnection();
using (connection)
{
connection.Open();
using (SqlCommand cmd = new SqlCommand("usp_UpdateText", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = obj.Id;
cmd.Parameters.Add("@NEWTEXT", SqlDbType.NVarChar, -1).Value = obj.Text;
cmd.ExecuteNonQuery();
}
connection.Close();
}
}