title: ColdFusion - Inserting form field into SQL Server MONEY
body: <p>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.</p>
  
  <p>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. (!)</p>
  
  <p>I tried using CFQUERYPARAM with the type CF_SQL_DECIMAL, but that didn't work. Any thoughts would be greatly appreciated.</p>
  
  <p>Here's the form code:</p>
  
  <pre><code>&lt;CFFORM ACTION="_execute_project_payment.cfm?action=Add" METHOD="post" NAME="FormX"&gt;
  &lt;CFOUTPUT&gt;
  &lt;INPUT TYPE="hidden" NAME="projectID" VALUE="#projectID#"&gt;
  &lt;TR&gt;
  &lt;TD CLASS="label" ALIGN="right" VALIGN="top"&gt;Payments:&lt;/TD&gt;
  
  &lt;TD&gt;
  
  $&lt;CFINPUT NAME="payment" TYPE="TEXT" VALIDATE="FLOAT" SIZE="15" REQUIRED="YES" MESSAGE="You must enter a payment amount."&gt;&amp;nbsp;&amp;nbsp;&amp;nbsp;Date:
  
  
  &lt;CFINPUT TYPE="text" name="payment_date" id="payment_date" SIZE="12" VALIDATE="DATE" REQUIRED="YES" MESSAGE="You must enter a payment date."&gt;
  
  
  &lt;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=’’" /&gt;
  &lt;script type="text/javascript"&gt;
  Calendar.setup({
  inputField : "payment_date",
  ifFormat : "%m/%d/%Y",
  button : "trigger_payment_date",
  align : "Tl",
  singleClick : false
  });
  &lt;/script&gt;
  
  
  &lt;INPUT TYPE="Submit" VALUE="Add"&gt;
  
  &lt;BR&gt; 
  
  &lt;/TD&gt;
  &lt;/TR&gt;
  
  &lt;/CFOUTPUT&gt;
  &lt;/CFFORM&gt;
  </code></pre>
  
  <p>And here's the insert:</p>
  
  <pre><code>&lt;CFSET paydateODBC = CreateODBCDate(FORM.payment_date)&gt;
  
  &lt;CFQUERY NAME="add_project_payment" DATASOURCE="#dsource#" USERNAME="#usern#" PASSWORD="#passw#"&gt;
  INSERT INTO project_payments (
                          projectID,
                          payment,
                          payment_date
                      )
              VALUES (
                          #projectID#,
                          #FORM.Payment#,
                          #paydateODBC#
                      )
  &lt;/CFQUERY&gt;
  </code></pre>
  
  <p>And here's the error, trying to enter 666.10 as the payment:</p>
  
  <pre><code>[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 : &lt;/CFQUERY&gt;
  
  SQLSTATE      22007
  SQL        INSERT INTO project_payments ( projectID, payment, payment_date ) VALUES ( 433, {d '0666-10-01'}, {d '2009-05-15'} )
  VENDORERRORCODE       241
  </code></pre>