SQL error '80040e21' when adding more text than the Field can handle

[Arnaldo Cabral]

I have a field in a table called description. I set the lenght to
7000 characters and the datatype to char on the description field.
Problem is that some people are putting more than 7000 characters into
the description field and are getting the error below. How can I
resolve this?



ERROR BELOW:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.

/kb/kb/submitadd.asp, line 80

[Aaron Bertrand [MVP]]

>I have a field in a table called description. I set the lenght to

> 7000 characters and the datatype to char on the description field.
> Problem is that some people are putting more than 7000 characters into
> the description field and are getting the error below. How can I
> resolve this?

Before you send it to the database, trim it to 7000 characters. You can do
this in VBScript/ASP by saying:

sql = "INSERT ... VALUES('" & left(var, 7000) & "')"

Instead of:

sql = "INSERT ... VALUES('" & var & "')"

You should also consider handling this validation on the client side
beforehand, so users aren't surprised when their content was silently
trimmed on them.

BTW, datatype should be VARCHAR, not CHAR. Unless everyone is expected to
enter *exactly* 7000 characters.
[url]http://www.aspfaq.com/2354[/url]

I'd also be interested in the code (both ASP and SQL) that causes this
error. What you *should* have received is:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Which would translate in ASP as:

Microsoft OLE DB Provider for SQL Server error '80040e57'
String or binary data would be truncated.

See [url]http://www.aspfaq.com/2289[/url] for more information.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Ray at]

Function TextIn(theText, theMaxLength)
Dim sResult
sResult = theText
If Len(sResult) > theMaxLength Then sResult = Left(sResult,
theMaxLength)
sResult = "'" & Replace(sResult, "'", "''") & "'"
End Function


sValue = Request.Form("textfield")
sSQL = "EXEC YourProcedure " & TextIn(sValue,7000)

Ray at work

"Arnaldo Cabral" <wwwacny@yahoo.com> wrote in message
news:63108917.0404130911.60dec2c0@posting.google.c om...

> I have a field in a table called description. I set the lenght to
> 7000 characters and the datatype to char on the description field.
> Problem is that some people are putting more than 7000 characters into
> the description field and are getting the error below. How can I
> resolve this?
>
>
>
> ERROR BELOW:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
>
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
>
> /kb/kb/submitadd.asp, line 80