INSERT INTO statement contains following unknown field name

Question

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

Answer 1

The error message given doesn't complain about the value inserted. It states that the field "exp_dat" doesn't exist in your table/view "news".

The first thing to do is to double check if there actually is a field named "exp_dat". If you don't have direct access to the database, just fire up a query from within ColdFusion and dump the query result.

<cfquery name="getSomeRowsFromNewsTable" datasource="#db#" maxrows="10">
select * from news
</cfquery>
<cfdump var="#getSomeRowsFromNewsTable#" abort="true">

Maybe someone aliased the field, or did not include an existing field in a view, or it's called "exp_dattim", or ...