String or binary data would be truncated

[Coldfire]

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!

[Coldfire]

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.

[kenobewan]

Have you made changes to the design or stored procedure recently? Please post the stored procedure. Thanks.

[Coldfire]

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

[Coldfire]

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
____________________________________________

[kenobewan]

Well done, thanks for sharing the solution :).

[vainternet]

or u could use ntext, it is bigger than varchar(1000)

here is a function for cutting the length of user data

public static string CutString(string text, int chars)
    {
        StringBuilder MyStringBuilder = new StringBuilder(text, chars);
        if (MyStringBuilder.Length > chars)
        {
            MyStringBuilder.Length = chars;
        }
        return Common.deConverttoHtml(MyStringBuilder.ToString());
    }

[Baghul Mughal]

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