CODEWITHSUNDEEP
sql-servercoldfusion
Adam J. Blust
Adam J. Blust

Reputation: 75

ColdFusion - Inserting form field into SQL Server MONEY

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

Upvotes: 1

Views: 2671

Answers (3)

Adam J. Blust
Adam J. Blust

Reputation: 75

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

Upvotes: 3

rip747
rip747

Reputation: 9455

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

Upvotes: 2

Steve -Cutter- Blades
Steve -Cutter- Blades

Reputation: 5432

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.

Upvotes: 2

Related Questions