String or binary data would be truncated

[Coldfire]

i am having error ....details are

ASP.Net application...i n which I have a textbox
<asp:TextBox ID="Other" TextMode=Single Line CssClass="servi ceBox" 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.Sql Client.SqlExcep tion: 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.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.Sql Client.SqlComma nd.ExecuteNonQu ery() +195
XXXXXX.SqlHelpe r.ExecuteNonQue ry(SqlConnectio n connection, CommandType commandType, String commandText, SqlParameter[] commandParamete rs)
XXXXXX.SqlHelpe r.ExecuteNonQue ry(String connectionStrin g, CommandType commandType, String commandText, SqlParameter[] commandParamete rs)
XXXXXXL.SqlHelp er.ExecuteNonQu ery(String connectionStrin g, String spName, Object[] parameterValues )
XXXXXX.Utilitie s.InsertCustome rService(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_InsertCustom erService, 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_InsertCusto merService" this is the stored procedure it has been using

[HTML]set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go


ALTER PROCEDURE [dbo].[up_InsertCustom erService]

@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.Val ue,
CantAccessList. Value,
WontFunctionLis t.Value,
OtherList.Value
FROM
ufn_ConvertList ToTable(@OfferI D) OfferIDList
JOIN ufn_ConvertList ToTable(@CantAc cess) CantAccessList ON CantAccessList. ID = OfferIDList.ID
JOIN ufn_ConvertList ToTable(@WontFu nction) WontFunctionLis t ON WontFunctionLis t.ID = OfferIDList.ID
JOIN ufn_ConvertList ToTable(@Other) OtherList ON OtherList.ID = OfferIDList.ID


[/HTML]
and the function "ufn_ConvertLis tToTable" is
[HTML]set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go


ALTER FUNCTION [dbo].[ufn_ConvertList ToTable] (@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(@lis t) / 2
BEGIN
SET @chunklen = 4000 - datalength(@lef tover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list , @textpos, @chunklen))
SET @textpos = @textpos + @chunklen

SET @pos = charindex('|', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmps tr, 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(@le ftover)) <> ''
INSERT @tbl (Value) VALUES(@leftove r)

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 "ConvertListToT able" it needs to be changed

[Coldfire]

hurray ........... i have changed "@list ntext" to "@list varchar(1000)" and Value "varchar(10 0)" to "varchar(10 00)" in the below given function that was called from my SP......Now its working fine ;)

[HTML]
ALTER FUNCTION [dbo].[ufn_ConvertList ToTable] (@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