Truncated String Data error when inserting into an nvarchar(max) field

Question

I have a SqlDataSource and an <asp:ListView> setup to handle comments on another form, with the following textbox:

<asp:TextBox ID="CommentTextBox" runat="server" Text='<%# Bind("Comment") %>' Width="400px" Height="125px" TextMode="MultiLine" />

The datasource has the following InsertCommand and asp:Parameter set on it:

InsertCommand="INSERT INTO [tblSLS_SpecComments] ([SpecID], [Author], [Comment]) VALUES (@SpecID, @Author, @Comment)" <asp:Parameter Name="SpecID" Type="Int32" /> <asp:Parameter Name="Author" Type="String" /> <asp:Parameter Name="Comment" Type="String" />

When I try to insert a longer comment (an example would be 500 'f's), I get an error:

String or binary data would be truncated.
The statement has been terminated.

The column that I'm inserting into is of datatype nvarchar(max), so it should be able to hold about a book's worth of characters, which is substantially fewer than I'm handing it.

Any clue about what's going on?

Shouldn't be. Author is setup as nvarchar(50), and is being truncated in code behind to only be the username (verified this). — Crimius
– Crimius, Commented Jul 18, 2013 at 14:48
@Crimius - Use SQL Server Profiler to trace the query being executed. You can then see exactly what is happening. I would think Mr Mush is correct here :) — Meff
– Meff, Commented Jul 19, 2013 at 13:47

Roman O · Accepted Answer · 2014-09-02 14:36:53Z

You could check does your table have any trigger to another datatable, and the trigger tries insert/update the value into smaller field.

Community · Accepted Answer · 2017-05-23 11:53:37Z

Nvarchar(Max) can't be the source of that error. Check this question Check other field like Author and use MaxLenght property of texbox where ever possible.

BornToCode · Accepted Answer · 2016-11-20 13:46:48Z

It happened to me when I tried to insert long values from the designer (Edit top 200 rows...) on a nvarchar(max) field.

When I ran the update manually from the query window it worked.

kuklei · Accepted Answer · 2020-05-21 15:32:19Z

I had a strange case with SQL Server 2008 where I could not insert into a nvarchar(max) column even if the query was not trying to insert anything at all (like Where 1 <> 1). The only solution that worked for me was sp_updatestats. Apparently, the server was thinking that the source column value could potentially be larger than nvarchar(max) (in fact it was nvarchar(20))

Collectives™ on Stack Overflow

Truncated String Data error when inserting into an nvarchar(max) field

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related