Solved

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

Posted on 2011-03-02
4
849 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 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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