Oracle's picky about date datatypes. Always TODATE(xxx) your dates in the
query. And TOCHAR them elsewhere. I've stopped trying to figure out why. :-)
Greg Luce
----- Original Message -----
From: "Angél Stewart" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, January 16, 2001 9:35 AM
Subject: Help:Oracle Date Issues and Error Message.
> -------------------------------------------------------------
> Error Diagnostic Information
> ODBC Error Code = 37000 (Syntax error or access violation)
>
> [Microsoft][ODBC driver for Oracle][Oracle]ORA-00917: missing comma
>
> SQL = "INSERT INTO
> Empinfo_audit(EmplID,name,deptid,effdt,enddate,dayabs,reason)
VALUES('1111',
> 'Bond,James', '1017', '{ts '2001-01-15 00:00:00'}', '{ts '2001-01-18
> 00:00:00'}', '3', 'A')"
> ------------------------------------------------------------------
>
> Hello all,
>
> I am trying to perform the simple insert statement above, going through
ODBC
> with CF4.5 Professional to an Oracle 8i database.
>
> The CF Code is here:
> -----------------------------------------------------------------
> <CFQUERY NAME="UpdateRecord" DATASOURCE="xxx" username="xxxxxxx"
> password="xxxxxxx">
> INSERT INTO
> Empinfo_audit(EmplID,name,deptid,effdt,enddate,dayabs,reason)
> VALUES('#cur_emplid#',
> '#Evaluate("name_#cur_emplid#")#',
> '#Evaluate("deptid_#cur_emplid#")#',
> <CFIF #Evaluate("effdt_#cur_emplid#")# neq "">
> '#CREATEODBCDATETIME(Evaluate("effdt_#cur_emplid#"))#',
> </CFIF>
>
> <CFIF #Evaluate("EndDate_#cur_emplid#")# neq "">
> '#CREATEODBCDATETIME(Evaluate("EndDate_#cur_emplid#"))#',
> </CFIF>
>
> '#Evaluate("dayabs_#cur_emplid#")#',
> '#Evaluate("reason_#cur_emplid#")#')
> </CFQUERY>---->
> --------------------------------------------------------------------
>
> I can't understand why I'm getting that error, as all these variables
appear
> to be correctly seperated by commas, and the syntax looks correct. This is
> part of an input loop that is used to enter from multiple, dynamically
> generated rows of data entered by the user in the previous form. I am
> testing it now on just a single row of data.
>
> Secondly:
>
> My client INSISTS on entering dates in this format YYYYMMDD. I am unaware
of
> any date standard that uses that format.
> Also, It seems that Oracle is rejecting that format as a valid date entry.
> What..how..what the hell do I do to get this into Oracle properly using
that
> CreateODBCDATETIME function?????
>
> Thanks in advance,
> Gel.
> www.carigamer.com
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
