Coldfusion Input Form & Time

Question

Why isn't the time inserting into the database. It gives me this error.

"Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Resources: Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debug Output Settings, and select the Robust Exception Information option. Check the ColdFusion documentation to verify that you are using the correct syntax. Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.2; Trident/7.0) Remote Address 127.0.0.1 Referrer //localhost:8500/Travel/toursInsert.cfm?APTANA_NOCACHE_1388693909596=1388693909596 Date/Time 02-Jan-14 03:18 PM"

<div id="InputForm">
 <cfset todayDateTime = Now()> 
 <cfform name="insertComments" id="insertComments">
    <fieldset>
            <label for="Remarks">Remarks<br />
            </label>
            <cftextarea name="Remarks" cols="55" rows="4" label="Tour Description" required="yes" validateat="OnSubmit" message="Please enter your comment here" enabled="no"></cftextarea>
          </p>
          <p>
            <label for="Users">Submitters Name</label>
            <br />
            <cfinput type="text" name="Users" message="Please enter your name here." validateat="onSubmit" required="yes" id="Name" size="10" maxlength="60">
          </p>
            <p>
            <label for="Image_ID">Image ID</label>
            <br />
            <cfinput type="text" name="Image_ID" message="Please enter Image_ID Number Here." validateat="onSubmit" required="yes" id="Image_ID" size="10" maxlength="60">
          </p>
        <p>
            <cfinput type="submit" name="insertComments" value="Insert Comments" id="submit">
        </p>
    </fieldset>
</cfform>

  <cfif IsDefined("form.InsertComments")>
                    <cfquery datasource="AccessTest">
                    INSERT INTO CommentsDB (Remarks, Users, Image_ID, Time)
                 VALUES ('#form.Remarks#','#form.Users#','#form.Image_ID#',#DateTimeFormat(todayDateTime, "yyyy.MM.dd hh:nn aaa")#)
                    </cfquery></cfif>

</div>

If access has something that returns the current date and time, use it in your query and forget about passing that particular value from ColdFusion. — Dan Bracuk, Jan 02 '14 at 13:09
First, you should be using `cfqueryparam` in your query. Second, why are you using Access? There are better solutions, such as MySQL and PostgreSQL, that are free, more powerful, and a much better choice. — Scott Stroz, Jan 02 '14 at 13:28
replace `#DateTimeFormat(todayDateTime, "yyyy.MM.dd hh:nn aaa")#` with `#now()#` and test it. — Azam Alvi, Jan 02 '14 at 13:43
Access has a function that can get the current datetime. http://stackoverflow.com/questions/2136552/getdate-equivalent-for-jet-access-database-need-last-month-records — James A Mohler, Jan 02 '14 at 15:57
1) Please post the full error message including the generated SQL 2) What is the data type of the `Time` column: varchar or datetime? Also, `Time` is a bad choice for a column name because it is [typically a reserved word](http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx). Using it as an object name can cause syntax errors if not properly escaped. — Leigh, Jan 02 '14 at 18:39
If debugging is not enabled on your server, you can wrap the query in a try/catch to capture full message - including the generated sql. Just do a cfdump of the `#cfcatch#` structure inside the cfcatch clause. Also, you still did not tell us what is the data type of the `Time` column: string or date/time? We need to know the data type in order to recommend the right code. — Leigh, Jan 02 '14 at 20:32
Changed that to a String and then changed Time to Date_Time and replaced #DateTimeFormat(todayDateTime, "yyyy.MM.dd hh:nn aaa")# with #now()# and it works. — Michael Downey, Jan 02 '14 at 20:54
No, leave the column type as `date/time`. Storing dates as strings is a recipe for disaster...trust me ;-) — Leigh, Jan 02 '14 at 21:41

score 4 · Answer 1 · answered Jan 02 '14 at 07:52

4

You need to give Access the date in odbc date time format.

Rather than use dateformat try using createodbcdatetime()

Also you should use cfqueryparam on each value in your insert statement as a final validation of the values being passed to the database. cfqueryparam with a SQL type of date time will make life easier for handling dates and times.

answered Jan 02 '14 at 07:52

Stephen Moretti

2,442
1
18
29

1

createodbcdatetime() is not required. If you use now() as a value in a query paramter, it will work. – Dan Bracuk Jan 02 '14 at 13:11
2

I think the broader point is that he should be inserting a datetime object, *not* a string. That said, both `createODBCDateTime` and `now()` return a date object, so either will work with `cfsqltype="cf_sql_timestamp"`. (Though technically `createODBCDateTime(now())` is redundant.) – Leigh Jan 02 '14 at 18:19

score 2 · Accepted Answer · edited May 23 '17 at 12:03

Explanation:

Ignoring best practices for a moment, there are several issues with the original query.

The first potential problem is that Time is a reserved word, making it a bad choice for a column name. IIRC, using a reserved word as an object name is one cause of the error you are seeing. If that is the case, you are options are to either:

Rename the column to avoid the conflict altogether (best option) .. OR
Escape the column name with square brackets, ie INSERT INTO ( [Time], ...)

#DateTimeFormat(todayDateTime, "yyyy.MM.dd hh:nn aaa")#

Second, you appear to be inserting a string without using quotes. Raw string values must be enclosed in quotes. Otherwise, the database will interpret the value as some type of object (table name, column name, ...) causing a syntax error.

Third, you should not insert strings into a date/time column anyway. Date strings are ambiguous and can be misinterpreted depending on the database settings. So even if the query succeeds, you might end up inserting the wrong date. For consistent results, use date objects instead. For example, you could use the CF now() function:

_{Note: Date objects should not be enclosed in quotes}

    INSERT INTO TableName ( SomeDateTimeColumn )
    VALUES ( #now()# )

Recommendation:

However, having said all that ... using raw values in a query is NOT recommended. Instead you should be using cfqueryparam with ALL query parameters. It provides a host of important benefits, not the least of which is protecting your database against sql injection. (Plus, you do not have to worry about things like pesky quoting issue)

Putting all of the above tips together, your query should look something like below. I will leave it to you to update the cfsqltypes to match the data types of your table columns.

   <cfquery datasource="AccessTest">
      INSERT INTO CommentsDB (Remarks, Users, Image_ID, YourTimeColumnName )
      VALUES  
      (
         <cfqueryparam value="#form.Remarks#" cfsqltype="cf_sql_longvarchar">
         , <cfqueryparam value="#form.Users#" cfsqltype="cf_sql_varchar">
         , <cfqueryparam value="#form.Image_ID#" cfsqltype="cf_sql_integer">
         , <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">
      )
   </cfquery>

A tag starting with 'CF' has been detected. This tag is not supported by this version of ColdFusion. Please verify your typo and try again. Unknown tag: cfquerparam. ColdFusion cannot determine how to process the tag cfquerparam because the tag is unknown and is not in any imported tag libraries. The tag name might be misspelled. — Michael Downey, Jan 03 '14 at 18:48
Remarks- LongText, Users- ShortText, Image_ID- Number, Field1- Date/Time — Michael Downey, Jan 03 '14 at 18:49
Oops, obviously a typo for `cfqueryparam`. As far as the data types, I am out of touch with Access terminology, but my guess would be longText=>memo, shortText=>text. Just do a search for "ColdFusion cfqueryparam MS Access". There is an old type matrix out there I wrote up a long time ago. — Leigh, Jan 03 '14 at 18:59

score 1 · Answer 3 · edited Jan 02 '14 at 14:38

1

Have you created the datasource in cfadministrator? if yes it should worked out

<cfif IsDefined("form.InsertComments")>
   <cfquery datasource="AccessTest">
       INSERT INTO CommentsDB (Remarks, Users, Image_ID, Time)
       VALUES (
          <cfqueryparam value="#form.Remarks#"/>,
          <cfqueryparam value="#form.Users#"/>,
          <cfqueryparam value="#form.Image_ID#"/>,
          <cfqueryparam value="#DateTimeFormat(now(),'mmm-dd-yyyy')#")/>
   </cfquery>
</cfif>

edited Jan 02 '14 at 14:38

Joe C

3,506
2
21
32

answered Jan 02 '14 at 12:11

Naresh vurukonda

41
1
5

1

The error he gets is a syntax error. The error he would receive if it was a datasource issue would be completely different. – Joe C Jan 02 '14 at 14:38
1

This insert is only doing the date, not the datetime – James A Mohler Jan 02 '14 at 15:55
1

1) Always specify a `cfsqltype` with `cfqueryparam`. Otherwise you loose many of the benefits. 2) IF the target is a date/time column, you should use date *objects* not strings. – Leigh Jan 02 '14 at 18:43

Coldfusion Input Form & Time

3 Answers3