Skip to main content
A
Anonymous
August 19, 2010
Question

casting form data to integer

  • August 19, 2010
  • 2 replies
  • 2007 views

I have a few hidden input fields that contain integers and I am going to insert these values into a sql server db into fields of type int. I tried using the CAST() function in my query, but it keeps throwing an error saying:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AS'.

Is there anything wrong with my query?

<cfquery datasource="db1">

INSERT INTO testSetup (id1, id2, id3, position, testStartDate)

VALUES(

   <cfqueryparam cfsqltype="cf_sql_varchar" value="form.id1">,

CAST(#form.id2# AS INTEGER),

CAST(#form.id3# AS INTEGER),

1,

<cfqueryparam cfsqltype="cf_sql_varchar" value="form.startDate">

)

</cfquery>

    This topic has been closed for replies.

    2 replies

    J
    JR__Bob__Dobbs
    Inspiring
    August 19, 2010

    Try using CFQUERYPARAM instead of CAST.

    <cfqueryparam cfsqltype="cf_sql_integer" value="#form.id2#">

    You should also put pound signs around the form variables in all of your CFQUERYPARAM tags.  This tells CF to treat these as variables instead of literal strings.

    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.id1#">

    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.startDate#">


    If testStartDate is a datetime column in your table you should change the cfsqltype to CF_SQL_TIMESTAMP if you are using MS SQL Server.
    A
    Anonymous
    August 19, 2010

    does cfquery param automatically do data casting?

    J
    JR__Bob__Dobbs
    Inspiring
    August 19, 2010

    CFQUERYPARAM will use the type specified in the cfsqltype attribute.

    -
    -__cfSearching__-
    Inspiring
    August 19, 2010

    My first thought is that "Position" might be a reserved keyword. Try escaping the column with square brackets renaming the column.

    If not, what are the actual values used in the query?

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