trying to insert datetime stamp from coldfusion into access

[TechBoyJK]

Hi,

I am doing a CFFILE upload, and need to insert the "file.timecreated" variable into an access database via odbc.

I am inserting the variable into a text field, aka its not date/time formated. I just want the info from the variable inserted. It will be formated when called upon.

Once the cffile operation is done, within the same transaction tag, I set the file.timecreated variable to a local variable like local_time_created

<cfset local_time_created = '#file.timecreated#'>

And then I try and insert the "local_time_created" field into a database like

file_time_created = '#local_time_created#'

The query works fine if I try inserting a blank value, but as soon as I try and insert the file.timecreated value, it gives this error

Caught an error.
Type: DATABASE
Message: ODBC Error Code = 22005 (Error in assignment)

Detail: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


Now, I have tried, all within the same transaction tag, to set the local variable like

<cfset local_time_created = '#createodbcdatetime(file.timecreated)#'>

and then do the same update

file_time_created = '#local_time_created#'

it wil then say

Caught an error.
Type: DATABASE
Message: ODBC Error Code = 37000 (Syntax error or access violation)

Detail: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{ts '2004-08-22 18:58:08'}''.

Now this db will eventually be ported over to SQL Server, is this an ACCESS only error?


The whole thing is that I am trying to use a single column in the db for both date and time, and then format it when I need it use it. Am I better off using two columns in the db, for date and time?

 

[KLin]

Originally posted by: TechBoyJK
Hi,

I am doing a CFFILE upload, and need to insert the "file.timecreated" variable into an access database via odbc.

I am inserting the variable into a text field, aka its not date/time formated. I just want the info from the variable inserted. It will be formated when called upon.

Once the cffile operation is done, within the same transaction tag, I set the file.timecreated variable to a local variable like local_time_created

<cfset local_time_created = '#file.timecreated#'>

And then I try and insert the "local_time_created" field into a database like

file_time_created = '#local_time_created#'

The query works fine if I try inserting a blank value, but as soon as I try and insert the file.timecreated value, it gives this error

Caught an error.
Type: DATABASE
Message: ODBC Error Code = 22005 (Error in assignment)

Detail: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


Now, I have tried, all within the same transaction tag, to set the local variable like

<cfset local_time_created = '#createodbcdatetime(file.timecreated)#'>

and then do the same update

file_time_created = '#local_time_created#'

it wil then say

Caught an error.
Type: DATABASE
Message: ODBC Error Code = 37000 (Syntax error or access violation)

Detail: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{ts '2004-08-22 18:58:08'}'' .

Now this db will eventually be ported over to SQL Server, is this an ACCESS only error?


The whole thing is that I am trying to use a single column in the db for both date and time, and then format it when I need it use it. Am I better off using two columns in the db, for date and time?

The bolded part is your problem. I think Access is trying to insert everything between the double quotes. I'm assuming Ts(timestamp?) is meant as a prequalifier? I don't know if SQL server will treat it different or not. You may want to download MSDE, and try inserting the data into a SQL table and see how it treats the data. What you need to do is strip off everything except the actual datetime string, ie ("2004-08-22 18:58:08") before inserting it into the access table.

 

[MrChad]

Get rid of the single quotes:

file_time_created = #CreateODBCDateTime(File.TimeCreated)#

 

[TechBoyJK]

even if i remove the single quotes, i still get

{ts '2004-08-22 19:13:19'}

which spits the error

Caught an error.
Type: DATABASE
Message: ODBC Error Code = 37000 (Syntax error or access violation)

Detail: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{ts '2004-08-22 19:13:19'}''.

 

[TechBoyJK]

fixed, i removed both the single quotes from the first cfset, along with the single quotes in the query