?
Solved

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

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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