469,336 Members | 5,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

String or binary data would be truncated

Coldfire
289 100+
i am having error ....details are

ASP.Net application...in which I have a textbox
<asp:TextBox ID="Other" TextMode=SingleLine CssClass="serviceBox" Width="250" Height="45" Runat="server" MaxLength="1000" /></asp:TextBox>

and this textbox is in a <asp:Repeater > which has the count of 35.

The textbox value is stored in the SQL DB
And the field data-type in the SQL DB is VARCHAR(1000)

Now the problem comes when I enter the text in the textbox with length more than 100(hundred) characters . The error that shows up is

----------------------
String or binary data would be truncated. The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

Stack Trace:

[SqlException: String or binary data would be truncated.
The statement has been terminated.]
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +195
XXXXXX.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
XXXXXX.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
XXXXXXL.SqlHelper.ExecuteNonQuery(String connectionString, String spName, Object[] parameterValues)
XXXXXX.Utilities.InsertCustomerService(Int32 MemberID, String OfferID, String CantAccess, String WontFunction, String Other)
----------------------

Now what i couldnt get is....the field length gives error when i give text more than 100 characters.......whats the deal with it..plz help asap!
Mar 5 '07 #1
7 17267
Coldfire
289 100+
in connection to my above query .......these are some more of the insights
regarding the error...i have found out that the main issue is in storedprocedure. But here in the SP , the length of the field is ntext rather than varchar(1000). But still it is not inserting values greater than 100 length

Msg 8152, Level 16, State 2, Procedure up_InsertCustomerService, Line 12
String or binary data would be truncated.
The statement has been terminated.
Mar 5 '07 #2
kenobewan
4,871 Expert 4TB
Have you made changes to the design or stored procedure recently? Please post the stored procedure. Thanks.
Mar 6 '07 #3
Coldfire
289 100+
Have you made changes to the design or stored procedure recently? Please post the stored procedure. Thanks.
"up_InsertCustomerService" this is the stored procedure it has been using

[HTML]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[up_InsertCustomerService]

@MemberID int,
@OfferID varchar(100),
@CantAccess varchar(100),
@WontFunction varchar(100),
@Other ntext

AS

INSERT INTO
CustomerService
(
MemberID,
OfferID,
CantAccess,
WontFunction,
Other
)
SELECT
@MemberID,
OfferIDList.Value,
CantAccessList.Value,
WontFunctionList.Value,
OtherList.Value
FROM
ufn_ConvertListToTable(@OfferID) OfferIDList
JOIN ufn_ConvertListToTable(@CantAccess) CantAccessList ON CantAccessList.ID = OfferIDList.ID
JOIN ufn_ConvertListToTable(@WontFunction) WontFunctionList ON WontFunctionList.ID = OfferIDList.ID
JOIN ufn_ConvertListToTable(@Other) OtherList ON OtherList.ID = OfferIDList.ID


[/HTML]
and the function "ufn_ConvertListToTable" is
[HTML]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[ufn_ConvertListToTable] (@list ntext)
--set the Value varchar(100) to ntext
RETURNS @tbl TABLE (ID INT IDENTITY, Value ntext NOT NULL) AS
BEGIN
DECLARE
@pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen

SET @pos = charindex('|', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (Value) VALUES(@str)
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex('|', @tmpstr)
END

SET @leftover = @tmpstr
END

IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (Value) VALUES(@leftover)

RETURN
END




[/HTML]

I have found out the same reason of datatype mismatch or data-overflow but not yet pinpointed that at where in function "ConvertListToTable" it needs to be changed
Mar 6 '07 #4
Coldfire
289 100+
hurray ........... i have changed "@list ntext" to "@list varchar(1000)" and Value "varchar(100)" to "varchar(1000)" in the below given function that was called from my SP......Now its working fine ;)

[HTML]
ALTER FUNCTION [dbo].[ufn_ConvertListToTable] (@list ntext)
--set the Value varchar(100) to ntext
RETURNS @tbl TABLE (ID INT IDENTITY, Value ntext NOT NULL) AS
BEGIN
DECLARE
[/HTML]


regards
Saad
____________________________________________
Mar 6 '07 #5
kenobewan
4,871 Expert 4TB
Well done, thanks for sharing the solution :).
Mar 7 '07 #6
or u could use ntext, it is bigger than varchar(1000)

here is a function for cutting the length of user data


Expand|Select|Wrap|Line Numbers
  1. public static string CutString(string text, int chars)
  2.     {
  3.         StringBuilder MyStringBuilder = new StringBuilder(text, chars);
  4.         if (MyStringBuilder.Length > chars)
  5.         {
  6.             MyStringBuilder.Length = chars;
  7.         }
  8.         return Common.deConverttoHtml(MyStringBuilder.ToString());
  9.     }
Jan 22 '08 #7
Typically this error happens when there is a mismatch between data types. I have seen this error occur in many places including SSIS, Reporting Services and procs.

I would say use either CAST or CONVERT functions to make sure you are getting the correct data type and data length when passing the values.


Baghul
Instructor
Mar 24 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Bernie Yaeger | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.