Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-02
4
Medium Priority
?
871 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 2000 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

Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

670 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