casting form data to integer

[Guest]

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>

[-__cfSearching__-]

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?

[JR__Bob__Dobbs]

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.

[Guest]

does cfquery param automatically do data casting?

[JR__Bob__Dobbs]

CFQUERYPARAM will use the type specified in the cfsqltype attribute.

[-__cfSearching__-]

does cfquery param automatically do data casting?

Essentially yes. If you were to use the cf_sql_integer type, ColdFusion would automatically convert your value into a integer object which is then sent to your database.

[-__cfSearching__-]

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.

Good catch.

[Dan_Bracuk]

You've been given the answer to your question.  I notice you appear to be storing dates as strings instead of date objects.  Bad idea.

[Guest]

Which data type would be best? I'm using a jQuery calendar plugin so it creates a date in the format MM/DD/YYYY, so i assumed this would be a string.

[ilssac]

Yes that is a string... but if you ever think that somebody might do any kind of date|time math or comparisons on the value, not just spit it out for display, you probably want to convert it to a date value in the database.  ColdFusion date|time functions and|or one of the date|time cfsqltypes of the <cfqueryparam...> tag make that pretty simple to do.

[Dan_Bracuk]

Store dates as dates.  Depending on the db software you are using, the datatype may or may not include a time portion.  If it does, your form data will give you whatever date it is, at exactly midnight.