ColdFusion - Inserting form field into SQL Server MONEY

Question

I'm trying to insert a value from a form field into a SQL Server MONEY field using ColdFusion. If the amount is just dollars, or if the cents are more than 12, the insert goes fine.

But if the cents amount is less than 12, according to the error that's thrown, the system appears to be converting the money amount into a date format before the insert, without my asking for that. And then, of course, it errors out, because I'm trying to insert a date into a MONEY field. (!)

I tried using CFQUERYPARAM with the type CF_SQL_DECIMAL, but that didn't work. Any thoughts would be greatly appreciated.

Here's the form code:

<CFFORM ACTION="_execute_project_payment.cfm?action=Add" METHOD="post" NAME="FormX">
<CFOUTPUT>
<INPUT TYPE="hidden" NAME="projectID" VALUE="#projectID#">
<TR>
<TD CLASS="label" ALIGN="right" VALIGN="top">Payments:</TD>

<TD>

$<CFINPUT NAME="payment" TYPE="TEXT" VALIDATE="FLOAT" SIZE="15" REQUIRED="YES" MESSAGE="You must enter a payment amount.">&nbsp;&nbsp;&nbsp;Date:


<CFINPUT TYPE="text" name="payment_date" id="payment_date" SIZE="12" VALIDATE="DATE" REQUIRED="YES" MESSAGE="You must enter a payment date.">


<img src="cal_icon.gif" id="trigger_payment_date"
style="cursor: pointer; border: 0px;"
title="Date selector"
onmouseover="this.style.background=’red’;"
onmouseout="this.style.background=’’" />
<script type="text/javascript">
Calendar.setup({
inputField : "payment_date",
ifFormat : "%m/%d/%Y",
button : "trigger_payment_date",
align : "Tl",
singleClick : false
});
</script>


<INPUT TYPE="Submit" VALUE="Add">

<BR> 

</TD>
</TR>

</CFOUTPUT>
</CFFORM>

And here's the insert:

<CFSET paydateODBC = CreateODBCDate(FORM.payment_date)>

<CFQUERY NAME="add_project_payment" DATASOURCE="#dsource#" USERNAME="#usern#" PASSWORD="#passw#">
INSERT INTO project_payments (
         projectID,
                        payment,
                        payment_date
        )
      VALUES (
         #projectID#,
                        #FORM.Payment#,
                        #paydateODBC#
        )
</CFQUERY>

And here's the error, trying to enter 666.10 as the payment:

[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting datetime from character string.

The error occurred in C:\Websites\qrpqiy\toubltracker\_execute_project_payment.cfm: line 21

19 :         #projectID#,
20 :                         #FORM.payment#,
21 :                         #paydateODBC#
22 :        )
23 : </CFQUERY>

SQLSTATE      22007
SQL        INSERT INTO project_payments ( projectID, payment, payment_date ) VALUES ( 433, {d '0666-10-01'}, {d '2009-05-15'} )
VENDORERRORCODE       241

Answer 1

Personally, I never use the Money type in SQL. I find I have a better degree of control if I use the Decimal field type.

Answer 2

<cfqueryparam value="#theamount#" cfsqltype="cf_sql_money">

that's what I use and I've never had a problem. if you can post your query and some stub data, it would help to determine the cause better.

Answer 3

Well, according to Adobe, the problem was my field names. Apparently in CF there is an obscure old function that you can get a field like "payment" to be evaluated as a date when you also include a "payment_date" field. Argh. I changed the field names, and it works now.

Adobe's docs on this: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=validateData_10.html#1154690