CODEWITHSUNDEEP
ms-accesscoldfusioninsertms-jet-ace
Barrett Chamberlain
Barrett Chamberlain

Reputation: 129

INSERT INTO statement contains following unknown field name

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

Upvotes: 1

Views: 17710

Answers (4)

user2057674
user2057674

Reputation: 77

Given an INSERT query, such as 

INSERT INTO tblname ('column1', 'column2', 'column3' ...) VALUES ...

Try using square brackets- that's []- around the column names, like so:

INSERT INTO tblname ([column1], [column2], [column3] ...) VALUES ...

This worked for me in the Access SQL GUI Interpreter, but might not solve the OP's specific situation (Coldfusion, etc).

Upvotes: 2

Robbiegod
Robbiegod

Reputation: 1014

Try to wrap every value you insert in your insert statement with single quotes.ie '#variable#', '#another_var#' -- and so on. I think I had the same problem, but doing that fixed it.

Upvotes: 0

Edward Spring
Edward Spring

Reputation: 51

I have run into this issues several times , with MS Access. I know the Column name is spelled correctly and that the column exists. E.G., I can do a 'SELECT' on the column with no problem, but when I try a simple SQL INSERT, I get that error: "... Unknown field name" I typed nothing, just used copy and paste, so there is no chance of a typo. Access literally does recognize a column name in SELECT statement, and DOES NOT recognize that same column name when attempting an INSERT.

It appears to be a bug in MS Access 2010.

Upvotes: 0

Andreas Schuldhaus
Andreas Schuldhaus

Reputation: 2648

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

Upvotes: 5

Related Questions