Skip to main content
B
BarrettChamberlain
Known Participant
April 12, 2011
Question

INSERT INTO statement contains following unknown field name

  • April 12, 2011
  • 2 replies
  • 5391 views

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

    This topic has been closed for replies.

    2 replies

    R
    Reed_Powell-ttnmOb
    Inspiring
    April 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

    B
    BarrettChamberlainAuthor
    Known Participant
    April 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

    A
    Amiya_Padhi
    Participating Frequently
    April 12, 2011

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

    B
    BarrettChamberlainAuthor
    Known Participant
    April 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

    A
    Amiya_Padhi
    Participating Frequently
    April 13, 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">,


    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
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices