2147217833 String or binary data would be truncated

Question

Hi Have an error occuring when I try to update a record via stored procedure. The error I get is 2147217833 String or binary data would be truncated.

I've done a length on each of the fields that I'm inserted and they should be fitting comfortably in to the the database fields - i.e. the length isn't greater that the column specifications.

Completely baffled at the moment - any other reasons why this error might occur?

Thanks,

Set objReturnParam = objComm.CreateParameter("Return",adInteger,adParamReturnValue)
Set objRiskIDParam = objComm.CreateParameter("@riskID",adBigInt,adParamInput) 
Set objControlsDescriptionParam = objComm.CreateParameter("@ControlsDescription",adVarChar,adParamInput,5000)
Set objTreatmentParam = objComm.CreateParameter("@Treatment",adVarChar,adParamInput,5000)
Set objControlsParam = objComm.CreateParameter("@Controls",adVarChar,adParamInput,10)
Set objPriorityParam = objComm.CreateParameter("@Priority",adVarChar,adParamInput,6)
Set objProbabilityParam = objComm.CreateParameter("@Probability",adVarChar,adParamInput,6)
Set objImpactParam = objComm.CreateParameter("@Impact",adVarChar,adParamInput,6)
Set objScoreParam = objComm.CreateParameter("@Score",adInteger,adParamInput)
Set objReviewTimeframeParam = objComm.CreateParameter("@ReviewTimeframe",adVarChar,adParamInput,6)
Set objReviewDateParam = objComm.CreateParameter("@ReviewDate",adDate,adParamInput)
Set objDateReviewedParam = objComm.CreateParameter("@DateReviewed",adDate,adParamInput)
Set objReviewerIDParam = objComm.CreateParameter("@ReviewerID",adInteger,adParamInput)      


objComm.Parameters("@riskID") = lRiskID
objComm.Parameters("@ControlsDescription") = strControlsDescription
objComm.Parameters("@Treatment") = strTreatment
objComm.Parameters("@Controls") = strControls
objComm.Parameters("@Priority") = strPriority
objComm.Parameters("@Probability") = strProbability
objComm.Parameters("@Impact") = strImpact
objComm.Parameters("@Score") = iScore
objComm.Parameters("@ReviewTimeframe") = strReviewTimeframe
objComm.Parameters("@ReviewDate") = cStr(Year(dReviewDate)) + "-" + cStr(Month(dReviewDate)) + "-" + cStr(Day(dReviewDate)) + " 00:00:00"
objComm.Parameters("@DateReviewed") = cStr(Year(Date)) + "-" + cStr(Month(Date)) + "-" + cStr(Day(Date)) + " 00:00:00"
objComm.Parameters("@ReviewerID") = Cstr(Session("UserID"))

when I output each of the variables that I'm trying to update, the length of each is:

lRiskID: 2
strControlsDescription: 6
strTreatment: 6
strControls: 4
strPriority: 0
strProbability: 1
strImpact: 1
iScore: 1
strReviewTimeframe: 0
Reviewdate19
dateReviewed19
reviewerid2

[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[CONTROLS_DESCRIPTION] [varchar](5000) COLLATE Latin1_General_CI_AS NOT NULL,
[TREATMENT] [varchar](5000) COLLATE Latin1_General_CI_AS NOT NULL,
[PRIORITY] [varchar](6) COLLATE Latin1_General_CI_AS NOT NULL,
[PROBABILITY] [varchar](6) COLLATE Latin1_General_CI_AS NOT NULL,
[IMPACT] [varchar](6) COLLATE Latin1_General_CI_AS NOT NULL,
[SCORE] [tinyint] NOT NULL,
[REVIEW_TIMEFRAME] [varchar](6) COLLATE Latin1_General_CI_AS NOT NULL,
[PROPOSED_REVIEW_DATE] [datetime] NOT NULL,
[DATE_REVIEWED] [datetime] NULL,
[REVIEWER_ID] [int] NULL,

Answer 1

Are you able to share the schema and the data?

You are almost definitely missing a column that is being truncated somewhere.

Answer 2

Without schema and code, it's be hard.

Random thoughts:

• Are you concatenating, or using CAST/varchar without a length?
• Do you have trailing spaces (for example, LEN ignores trailing spaces)
• Or is there an audit trigger?

Edit, after code added

Where does @Controls go? There is no column...

To me, this implies the truncate error is not for this table

Answer 3

Is it possible that you are trying to insert more than 8060 bytes in a row for in page data types?

Answer 4

Your parameter declarations do not all match the data types of your table. You're using adBigInt for a numeric (use adNumeric), adInteger for a tinyint (use adTinyInt).
It looks like you're in VB6, and in VB6 the adDate datatype was used for Access. For updating a SQL Server DB you need to use adDBTimeStamp as the parameter datatype instead.

http://www.devguru.com/Technologies/ado/quickref/command%5Fcreateparameter.html