ColdFusion Error converting data type decimal to decimal

user_3493397 0
Viewed 321

I've got a bug in a CFStoredProc that's causing a crash whenever I try to send a decimal value to the stored procedure. The procedure simply inserts the value into a decimal(4,2) column in a SQL table. If I hard code a decimal value as the value of the cfprocparam, the procedure works. However, if the value is read in from a file, the I always get the error "Error converting data type decimal to decimal", no matter what I do to ensure the number is formatted correctly. Can anyone see what I'm doing wrong here?

My code essentially does the following:

  1. Read a CSV file and convert it to a Query
  2. Loop through the query to read the CSV data into variables
  3. Send those variables to a stored procedure as cfprocparams

I have tried:

  1. Formatting the number using numberFormat() before sending it to the stored procedure
  2. Formatting the number using decimalFormat() before sending it to the stored procedure
  3. Formatting the number using a custom function that does the same thing elsewhere in our application
  4. Formatting the number using the aforementioned functions in the 'value' part of the cfprocparam

Code samples of what I've tried:

<cfif structKeyExists(form, 'select_rHeight')>
     <cfset headerName = form['select_rHeight']>
     <cfset rHeight = csvQuery[headerName]>
     <cfset rHeight = numberFormat(rHeight, "__.00")>
</cfif>

<cfstoredproc procedure="name" datasource="name">
     <cfprocparam cfsqltype="CF_SQL_DECIMAL" dbvarname="@Height" scale="2" value=#rHeight# null="#NOT len(trim(rHeight))#" />
</cfstoredproc>


<cfif structKeyExists(form, 'select_rHeight')>
     <cfset headerName = form['select_rHeight']>
     <cfset rHeight = csvQuery[headerName]>
</cfif>

<cfstoredproc procedure="name" datasource="name">
     <cfprocparam cfsqltype="CF_SQL_DECIMAL" dbvarname="@Height" scale="2" value=#decimalFormat(rHeight)# null="#NOT len(trim(rHeight))#" />
</cfstoredproc>

I've done cfdumps and outputs of the variable before it gets sent to the stored procedure, and it always appears to be formatted correctly, but I always get this error message: [Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type decimal to decimal.

coldfusion sql-server
1 Answers

It turns out that the value being passed to the stored procedure was too large for the data type of the column into which it was being inserted. Data type was Decimal(3,2) and the value I was passing had more then three digits.

The solution was to check the size of the number being passed to the stored procedure, and give the user an error if the value is too large.

Thanks to @Tomalak for the help!

user_34933970
Related