Copy link to clipboard
Copied
I’m using Coldfusion 9,0,0,251028 on Windows 7 64-bit, with a Microsoft Access 97 database.
When I run this query:
<cfquery name="put_in_info" datasource="#db#">
insert into news
(is_current, display, mes_dat,mes_tim,mes_sub,mes_text,scrollshow,exp_dat)
values
(1,1, #createodbcdate(now())#, #createodbctime(now())#, '#subject#', '#message#',1, #session.expdate#)
</cfquery>
I get this error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The INSERT INTO statement contains the following unknown field name: 'exp_dat'. Make sure you have typed the name correctly, and try the operation again. The error occurred in H:\Inetpub\pvalert.com\listserver\admin\templates\post_breaking.cfm: line 26
Called from H:\Inetpub\pvalert.com\listserver\admin\new_process.cfm: line 54
Called from H:\Inetpub\pvalert.com\listserver\admin\templates\post_breaking.cfm: line 26
Called from H:\Inetpub\pvalert.com\listserver\admin\new_process.cfm: line 54
24 : (is_current, display, mes_dat,mes_tim,mes_sub,mes_text,scrollshow,exp_dat)
25 : values
26 : (1,1, #createodbcdate(now())#, #createodbctime(now())#, '#subject#', '#message#',1, #session.expdate#)
27 : </cfquery>
28 :
VENDORERRORCODE | -1507 |
SQLSTATE | HYS22 |
SQL | insert into news (is_current, display, mes_dat,mes_tim,mes_sub,mes_text,scrollshow,exp_dat) values (1,1, {d '2011-04-11'}, {t '17:49:09'}, 'Test message - please ignore', 'This is a test message, please ignore. ',1, {ts '2011-05-15 00:00:00'}) |
DATASOURCE | rpv_list |
Exp_dat is an expiration date column in a table that I need to update.
I've tried removing the "session." on the expdate variable, and that did nothing. Likewise for removing the any spaces around line 24's "exp_dat".
Copy link to clipboard
Copied
Please make sure 'exp_dat' is a valid column name in the 'news' table.
Copy link to clipboard
Copied
1) Why are you using Access?
2) Why are you not using cfqueryparams?
3) Completely agree, it looks simply like you've gotten the name "exp_dat" wrong.
Copy link to clipboard
Copied
Owainnorth wrote:
1) Why are you using Access?
The place I'm working for refuses to upgrade .
Copy link to clipboard
Copied
Amiya Padhi wrote:
Please make sure 'exp_dat' is a valid column name in the 'news' table.
Yes, here is a screenshot of the design view of the news table: http://i.imgur.com/INiNg.png
Copy link to clipboard
Copied
<cfquery name="put_in_info" datasource="#db#">
insert into news
(is_current,display,mes_dat,mes_tim,mes_sub,mes_text,scrollshow,exp_dat)
values
(
<cfqueryparam value="1" cfsqltype="cf_sql_numeric">,
<cfqueryparam value="1" cfsqltype="cf_sql_numeric">,
<cfqueryparam value="#DateFormat(now(),'yyyy-mm-dd')#" cfsqltype="cf_sql_date">,
<cfqueryparam value="#TimeFormat(now(),'hh-mm-ss')#" cfsqltype="cf_sql_date">,
<cfqueryparam value="#subject#" cfsqltype="cf_sql_varchar">,
<cfqueryparam value="#message#" cfsqltype="cf_sql_varchar">,
<cfqueryparam value="1" cfsqltype="cf_sql_numeric">,
<cfqueryparam value="#DateFormat(session.expdate,'yyyy-mm-dd')#" cfsqltype="cf_sql_date">
)
</cfquery>
Can you please confirm this by checking?
Copy link to clipboard
Copied
Amiya Padhi wrote:
Can you please confirm this by checking?
I tried this and got:
The cause of this output exception was that: coldfusion.runtime.locale.CFLocaleBase$InvalidDateTimeException: 05-58-46 is an invalid date or time string..
The error occurred in H:\Inetpub\pvalert.com\listserver\admin\templates\post_breaking.cfm: line 30
Called from H:\Inetpub\pvalert.com\listserver\admin\new_process.cfm: line 54
Called from H:\Inetpub\pvalert.com\listserver\admin\templates\post_breaking.cfm: line 30
Called from H:\Inetpub\pvalert.com\listserver\admin\new_process.cfm: line 54
Called from H:\Inetpub\pvalert.com\listserver\admin\templates\post_breaking.cfm: line 30
Called from H:\Inetpub\pvalert.com\listserver\admin\new_process.cfm: line 54
28 : <cfqueryparam value="1" cfsqltype="cf_sql_numeric">,
29 : <cfqueryparam value="#DateFormat(now(),'yyyy-mm-dd')#" cfsqltype="cf_sql_date">,
30 : <cfqueryparam value="#TimeFormat(now(),'hh-mm-ss')#" cfsqltype="cf_sql_date">,
31 : <cfqueryparam value="#subject#" cfsqltype="cf_sql_varchar">,
32 : <cfqueryparam value="#message#" cfsqltype="cf_sql_varchar">,
Copy link to clipboard
Copied
I guess there is typo..
<cfqueryparam value="#TimeFormat(now(),'hh-mm-ss')#" cfsqltype="cf_sql_date">
must be
<cfqueryparam value="#TimeFormat(now(),'hh-mm-ss')#" cfsqltype="cf_sql_time">
Can you please try with this and let me know if you still find any issues there.
Thanks
Copy link to clipboard
Copied
"hh-mm-ss" doesn't look like a valid time-format string to me.
However CF should just extract the time part of a CF date object anyhow, shouldn't it, before passing it to JDBC? One shouldn't need to use dateFormat() in a CFQUERYPARAM..?
--
Adam
Copy link to clipboard
Copied
We can help you more if you do the following: write a piece of code that does a SELECT
from your table, and then displays the contents of the .ColumnList variable. Copy/paste that info into a posting.
-reed
Copy link to clipboard
Copied
Reed Powell wrote:
...do the following: write a piece of code that does a SELECT
from your table, and then displays the contents of the .ColumnList variable. Copy/paste that info into a posting.
DISPLAY,EXP_DAT,ID,IS_CURRENT,MES_DAT,MES_SUB,MES_TEXT,MES_TIM,SCROLLSHOW