#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    69
    Rep Power
    11

    Using Access 2000 w/coldfusion. Need help setting DATE


    Ok, I don't want to write a huge write up here because I am at my wits end. I have been having problems UPDATING a form because of a column in one of my tables called "articledate"

    I have/had that column in Access set to Date/Time and the format I typed as yyyy-mm-dd

    Now, in coldfusion, I made sure to manipulate the date value to be

    #DateFormat(articledate, "yyyy-mm-dd")#

    Well, I have had nothing but grief because it never even shows the appropriate date.

    I enter 1995-12-05 and it returns something stupid like

    1905-05-31

    Why its screws it up is beyond me.

    So what am I asking is for someone to just tell me the most basic way to get a date in access to work with coldfusion without any problems. At least then, I can tweak it from there.

    What values do I set in Access, what do I have to do in Coldfusion to get it to work, etc..

    Thanks
    Bryan
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    If the field is a date data type in Access, you can try using the ODBC date functions like:

    #createODBCDate( Now() )# or
    #createODBCDate( '12/31/03' )#

    See if that works.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    69
    Rep Power
    11
    If you don't mind me asking here are a couple files that contain my code

    1. This file contains the coldfusion code for the actual FORM page where information is either entered or edited
    http://www.btkdesigns.com/editform.htm

    2. This page is where the "magic" happens, where it will INSERT or UPDATE depending on what has been passed
    http://www.btkdesigns.com/insert-edit.htm

    This might bring some light into the problem

    Now, do I need to alter the FORMAt in access, or just leave it blank?

    Thanks
    Bryan
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    I think that all you should need to do is use the odbcDateFormat() function in the insert and update:

    <cfset EditMode=IsDefined("FORM.articleID")>
    <cfif EditMode>
    <CFQUERY datasource="#dsn#">
    UPDATE articles
    SET gamedID=<cfqueryparam value="#FORM.gameID#" cfsqltype="cf_sql_integer">,
    typeID=<cfqueryparam value="#FORM.articletypes#" cfsqltype="cf_sql_integer">,
    articletitle=<cfqueryparam value="#Trim(FORM.articletitle)#" cfsqltype="cf_sql_varchar">,
    articletext=<cfqueryparam value="#Trim(FORM.articletext)#" cfsqltype="cf_sql_varchar">,
    articledescription=<cfqueryparam value="#Trim(FORM.articleID)#" cfsqltype="cf_sql_varchar">,
    articledate=<cfqueryparam value="#odbcDateFormat( trim( FORM.articledate ) )#," cfsqltype="cf_sql_date">,
    postedby=<cfqueryparam value="#Trim(FORM.postedby)#" cfsqltype="cf_sql_varchar">,
    postedemail=<cfqueryparam value="#Trim(FORM.postedemail)#" cfsqltype="cf_sql_varchar">
    WHERE articleID=<cfqueryparam value="#FORM.articleID#" cfsqltype="cf_sql_integer">
    </CFQUERY>
    <cfset formtitle="You have Updated article #FORM.articletitle#">
    <CFSET yes="Please go back to the menu list and confirm the deletion, <a href=""gamelist.cfm"" class=""body"">Go back</a>">
    <cfelse>
    <CFQUERY datasource="#dsn#">
    INSERT INTO articles (gameID,
    typeID,
    articletitle,
    articletext,
    articledescription,
    articledate,
    postedby,
    postedemail )
    VALUES ( #FORM.gameID#,
    #FORM.articletypes#,
    '#TRIM(FORM.articletitle)#',
    '#TRIM(FORM.articletext)#',
    '#TRIM(FORM.articledescription)#',
    #odbcDateFormat( trim( FORM.articledate ) )#,
    '#TRIM(FORM.postedby)#',
    '#TRIM(FORM.postedemail)#' )
    </cfquery>
    <cfset formtitle="You have Inserted article #articletitle#">
    <cfset yes="Please go back to the menu list and confirm the deletion, <a href=""gamelist.cfm"" class=""body"">Go back</a>">
    </cfif>


    See if that works.

IMN logo majestic logo threadwatch logo seochat tools logo