Skip to main content
A
ajblust
Participant
May 8, 2009
Question

Inserting form field into SQL Server MONEY

  • May 8, 2009
  • 2 replies
  • 2466 views

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.

    This topic has been closed for replies.

    2 replies

    B
    Bert_Dilliën
    Inspiring
    May 8, 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.

    A
    ajblustAuthor
    Participant
    May 8, 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...

    ilssac
    ilssac
    Inspiring
    May 8, 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.

    A
    ajblustAuthor
    Participant
    May 8, 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:


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

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

    Insert code:

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

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

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

    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

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

    ilssac
    ilssac
    Inspiring
    May 8, 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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices