Skip navigation
BarrettChamberlain
Currently Being Moderated

INSERT INTO statement contains following unknown field name

Apr 11, 2011 6:01 PM

 

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

 
Replies
  • Currently Being Moderated
    Apr 11, 2011 9:14 PM   in reply to BarrettChamberlain

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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 11, 2011 11:22 PM   in reply to Amiya Padhi

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 12, 2011 5:23 AM   in reply to BarrettChamberlain

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 12, 2011 10:56 AM   in reply to BarrettChamberlain

    <cfquery name="put_in_info" datasource="#db#">
          insert into news

     

                      (is_current,display,mes_dat,mes_tim,mes_sub,mes_text,scrollshow,exp_d at)
          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?

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 12, 2011 9:06 PM   in reply to BarrettChamberlain

    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
     
    |
    Mark as:
  • Currently Being Moderated
    Apr 13, 2011 12:19 AM   in reply to Amiya Padhi

    "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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points