Solved

Coldfusion error 8152 - string or binary data would be truncated. Solution?

Posted on 2011-03-02
4
858 Views
Last Modified: 2012-05-11
I've got a form that has several textareas that users can enter text and submit. The field in the sql server 2005 DB is set to varchar(250). I have qforms validation that restricts the user to only 250 characters in each textarea. This works fine for the most part but occassionally I keep getting the 8152 error - string or binary data will be truncated error!

I'm not able to replicate this error but my try/catch block catches it infrequently and cfmails to me. Is there something I'm missing? tia
<textarea class="textArea" rows="3"  onfocus="className='textEditArea';" onblur="className='textArea';return isMaxLength(this);" name="answer_#question_id#"  maxlength="#theMaxLength#" onkeyup="return isMaxLength(this);" onpaste="return isMaxLength(this);" id="answer_#question_id#">#answer#</textarea>

Open in new window

0
Comment
Question by:roger_v
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 35018829
In your insert or update statement, you can just make sure you chop the text down as a final ensurance..

 '#left(answer,250)#'
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35018869
Of course you can validate after submit on the server side as well... javascript validation is never enough..

<cfif len(answer) gt 250>
    ---- do not save, instead return an error to the user
</cfif>
0
 
LVL 1

Author Comment

by:roger_v
ID: 35019693
@gde,

Sorry should've posted the insert/update code as well as the stored proc:

The insert/update is taking place from a cfc that has a function that runs a stored proc on sql server 2005:

In the CFC:

<cffunction name="saveText" output="false" access="public" returntype="void">
<cfargument name="myans" required="yes" type="string"/>
<cfstoredproc procedure="AnswerSave" datasource="#request.mydsn#">
<cfprocparam cfsqltype="cf_sql_varchar" type="in" value="#arguments.myans#"/>
</cfstoredproc>

In the storedproc (sql server):

ALTER PROCEDURE [dbo].[AnswerSave] (
  @myAns VARCHAR(8000)
)

AS

SET NOCOUNT ON

      BEGIN
            UPDATE  TblText
            SET         ans = @myAns,
                     LAST_UPDATED = getdate()
           
            WHERE        -----some code-----
      END

So you're saying I should do a left() in the stored proc?

-roger
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 35020096

Wow, that's a lot of code for a little update.  


Since you have so many layers, you could do it in any of them really.

In your stored procedure you could add substr() to the update statement  ans=substr(@myAns,1,250)

In your "saveText" function, you could left(xxx,250) the variable before passing it to AnswerSave

In your coldfusion action, you could left(xxx,250) when you, or before you, call saveText()


I guess I would leave the procedure as generic as possible (you have @myAns declared to be 8000 characters, so it is expecting anything really)

So, I would do it in saveText before calling the procedure

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question