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
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
replace #DateTimeFormat(todayDateTime, "yyyy.MM.dd hh:nn aaa")# with #now()# and test it. — Azam Alvi
Access has a function that can get the current datetime. stackoverflow.com/questions/2136552/… — James A Mohler
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. Using it as an object name can cause syntax errors if not properly escaped. — Leigh

Leigh Leigh · Accepted Answer · 2014-01-02T21:30:01

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>

Coldfusion Input Form & Time

3 Answers