Solved

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

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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