INSERT INTO statement contains following unknown field name

Report · Apr 11, 2011

I’m using Coldfusion 9,0,0,251028 on Windows 7 64-bit, with a Microsoft Access 97 database.

When I run this query:

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".

Report · Apr 11, 2011

Please make sure 'exp_dat' is a valid column name in the 'news' table.

Report · Apr 11, 2011

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.

Report · Apr 12, 2011

Owainnorth wrote:
1) Why are you using Access?

The place I'm working for refuses to upgrade .

Report · Apr 12, 2011

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

Report · Apr 12, 2011

<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?

Report · Apr 12, 2011

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">,

Report · Apr 12, 2011

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

Report · Apr 13, 2011

"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

Report · Apr 12, 2011

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

Report · Apr 12, 2011

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

Adobe Community

INSERT INTO statement contains following unknown field name