Inserting form field into SQL Server MONEY

Report · May 08, 2009

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. Tearing my hair out here with this baffling behavior.

Report · May 08, 2009

Code for the form and the SQL input would be helpful.

It would be good to know if you do any pre-processing of the form data before the SLQ as well.

This is not something ColdFusion is going to do just because it does not like you. The code is somehow doing this, but without seeing the code the most advice I can offer is to "Look at the code"

There is some built in features that developers sometimes unintentially trip up against, but without seeing the code I can't say this is happening, so just what out for hidden form control with underscores in the name.

Report · May 08, 2009

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."> 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>

<BR>

</TD>
</TR>

</CFOUTPUT>
</CFFORM>

--------------------------------------------------------------------

Insert code:

-----------------------------------------------------------------------

<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>

-----------------------------------------------------------------------

I thought maybe the Javascript calendar date picker might be messing things up, but I took that out and got the same result.

Here's the error text:

--------------------------------------------------------------------

[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
DATASOURCE       toubltracker

---------------------------------------------------------------------

Report · May 08, 2009

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

Rename this field to something that does NOT end in "_date". This is exactly what I was looking for. ColdFusion has a little known and used feature that one can use to validate form fields by creating other form fields that end in an underscore "_" character and a data type. What this tells ColdFusion to do is to validate a field named "payment" to be a date data type. Exactly what you are fighting.

In CF it is best just not to use underscores in form field names, Camel Case or hyphens or something else is better.

This is all clearly spelled out in the documentation but if one never reads that section this can be very surprising behavior.

Report · May 08, 2009

Thanks, Ian. Wow - the rabbit hole goes deep, doesn't it?

-Adam.

Report · May 08, 2009

Yes it does, I believe this is the oldest version of form validation in ColdFusion dating back to before 4.5 as far as I know. I presume it is kept for backwards compatibility. It is actually pretty cool what can be done with it when used intentionally. But very frustrating when it start happenings unintentionally. Here is a link to the appropriate section of the documentation.

http://livedocs.adobe.com/coldfusion/8/htmldocs/validateData_10.html#1154690

Report · May 08, 2009

Thanks again. I have run up against these old legacy things in the past, and they can be maddening. I changed the field names, and it works now. Whew!

-Adam.

Report · May 08, 2009

Hi,

When the type of the DB field is Money, you should use the CFQueryParam type of CF_SQL_MONEY instead of the DECIMAL. See documentation of the respective param tag in the livedocs.

Cheers,

Bert.

Report · May 08, 2009

CFQUERYPARAM does not help this situation. In fact, when I put it in, it converts values that would error out (like 666.10) to massive negative numbers. It seems that CF is doing this conversion to the date format before the insert is even attempted, and I don't know why it's happening or how to stop it. Very frustrating...