>- see footer for list info -< Try this... <cfset CurrencyRateDate = createODBCDate(top.xmlAttributes.time) /> instead of your dateformat line.
If that doesn't work, you'll need to parse the date, something like... <cfset myYear = listFirst(top.xmlAttributes.time,"-")> <cfset myMonth = listGetAt(top.xmlAttributes.time,2,"-")> <cfset myYear = listLast(top.xmlAttributes.time,"-")> <cfset CurrencyRateDate = createODBCDate(createDate(myYear,myMonth,myDay))/> -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Fawcett Sent: 15 March 2006 22:34 To: Coldfusion Development Subject: RE: [CF-Dev] parsing xml and inserting into Access2000 >- see footer for list info -< I'm not getting an error anymore with the code below but the date is showing up in the database as 10/06/1905 instead of 2006-03-15 ?? <cfhttp url="<http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml>http://www.ecb. int/stats/eurofxref/eurofxref-daily.xml" method="get" /> <cfset xml = XMLParse(cfhttp.filecontent) /> <cfset top = "#xml.xmlRoot.Cube.Cube#" /> <cfset query_string = "INSERT INTO tblDailyRates (CurrencyRateDate"/> <cfloop index="i" from="1" to="#ArrayLen(top.xmlchildren)#"> <cfset child = top.xmlchildren[i] /> <cfset query_string = query_string & ",#child.xmlattributes.currency#"/> </cfloop> <cfset query_string = query_string & " ) VALUES (#CurrencyRateDate#" /> <cfloop index="i" from="1" to="#ArrayLen(top.xmlchildren)#"> <cfset child = top.xmlchildren[i] /> <cfset query_string = query_string & ",#child.xmlattributes.rate#"/> </cfloop> <cfset query_string = query_string & ")" /> <cfquery name="q_addCurrency" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#"> #query_string# </cfquery> At 13:55 15/03/2006, you wrote: > >- see footer for list info -< >you need to match the number of columns with the >number of values. you're missing a close >bracket after the values. you probably need to >put the column Date in square brackets as that's a reserved word. > >Duncan Cumming >New Media Developer >Customer Relations Management / Education >Fife Council >700 4105 / 01592 414105 > > >>> [EMAIL PROTECTED] 15/03/2006 10:52:20 >>> > >- see footer for list info -< >I'm trying > > <cfset query_string = "INSERT INTO > >tblDailyRates(Date,USD,JPY,CYP,CZK,DKK,EEK,GBP,HUF,LTL,LVL,MTL,PLN,SEK,SIT, SKK,CHF,ISK,NOK,BGN,HRK,RON,RUB,TRY,AUD,CAD,CNY,HKD,IDR,KRW,MYR,NZD,PHP,SGD, THB,ZAR) > >VALUES >(#Date# " /> > > >but am getting a syntax error?? > >I'd also like for thes specific columns match the ones in the xml file > > >At 11:02 15/03/2006, you wrote: > > >- see footer for list info -< > > > ><cfset query_string = "INSERT INTO tblDailyRates (column1,column2) VALUES > >(#Date# " /> > > > > > >K > > > -----Original Message----- > > > From: [EMAIL PROTECTED] [mailto:dev- > > > [EMAIL PROTECTED] On Behalf Of Shawn Fawcett > > > Sent: 15 March 2006 09:51 > > > To: Coldfusion Development > > > Subject: RE: [CF-Dev] parsing xml and inserting into Access2000 > > > > > > >- see footer for list info -< > > > that fixed that problem. I also got rid of the > > > quotes around '#child.xmlattributes.rate#' > > > > > > the thing though is my first field CurrencyRateID > > > (autonumber) needs somehow to be skipped...the > > > date is trying to go into the autonumber field. > > > > > > To avoid this I'd like to explicitly name the > > > destination columns. how could I do this? > > > > > > > > > > > > > > > At 10:15 15/03/2006, you wrote: > > > > >- see footer for list info -< > > > >Your problem is with the date format, first up, you don';t need the > >quotes > > > >around dates, so try... > > > > > > > ><cfset query_string = "INSERT INTO tblDailyRates VALUES (#Date# " /> > > > > > > > >If that still throws an error try > > > > > > > ><cfset Date = createODBCDate(top.xmlAttributes.time) /> > > > >Instead of your dateformat line. > > > > > > > >-----Original Message----- > > > >From: [EMAIL PROTECTED] > > > >[mailto:[EMAIL PROTECTED] On Behalf Of Shawn Fawcett > > > >Sent: 15 March 2006 05:38 > > > >To: Coldfusion Development > > > >Subject: [CF-Dev] parsing xml and inserting into Access2000 > > > > > > > > >- see footer for list info -< > > > >I'm trying to get the code below to insert data > > > >into a currency rate table. The table fields are: > > > >ID (autonumber), Date, USD, JPY, CYP, and another > > > >30 odd currencies. I'm getting the following > > > >error: "Error Executing Database Query. > > > >[Macromedia][SequeLink JDBC Driver][ODBC > > > >Socket][Microsoft][ODBC Microsoft Access Driver] > > > >Syntax error (missing operator) in query expression '''2006-03-14'''. " > > > > > > > >not quite sure what I'm missing here. > > > >thanks for any help, > > > >Shawn > > > > > > > > > > > > <cfhttp > > > >url="http://www.ecb.int/stats/eurofxref/eur > ofxref-daily.xml" method="get" > >/> > > > > <cfset xml = XMLParse(cfhttp.filecontent) /> > > > > <cfset top = "#xml.xmlRoot.Cube.Cube#" /> > > > > <cfset Date = DateFormat(top.xmlAttributes.time,"yyyy-mm-dd") /> > > > > <cfset query_string = "INSERT INTO > tblDailyRates VALUES ('#Date#'" /> > > > > <cfloop index="i" from="1" to="#ArrayLen(top.xmlchildren)#"> > > > > <cfset child = top.xmlchildren[i] /> > > > > <cfset query_string = query_string & > >",'#child.xmlattributes.rate#'" > > > >/> > > > > </cfloop> > > > > <cfset query_string = query_string & ")" /> > > > > <cfquery name="q_addCurrency" > > > >datasource="#application.DS#" > > > >username="#application.dbuser#" password="#application.dbpass#"> > > > > #query_string# > > > > </cfquery> > > > > > > > > > > > > > > > >ARAXI Traductions France > > > >100 Passage Henri Régnault - 92400 Courbevoie La Défense 6 > > > >Tél. : 01 49 03 41 49 - Fax : 01 49 03 41 42 > > > >_______________________________________________ > > > > > > > >For details on ALL mailing lists and for joining or leaving lists, go to > > > >http://list.cfdeveloper.co.uk/mailman/listinfo > > > > > > > >-- > > > >CFDeveloper Sponsors:- > > > > >- Hosting provided by www.cfmxhosting.co.uk -< > > > > >- Forum provided by www.fusetalk.com -< > > > > >- DHTML Menus provided by www.APYCOM.com -< > > > > >- Lists hosted by www.Gradwell.com -< > > > > >- CFdeveloper is run by Russ Michaels, > feel free to volunteer your help > >-< > > > > > > > > > > > > > > > >_______________________________________________ > > > > > > > >For details on ALL mailing lists and for joining > > > >or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo > > > > > > > >-- > > > >CFDeveloper Sponsors:- > > > > >- Hosting provided by www.cfmxhosting.co.uk -< > > > > >- Forum provided by www.fusetalk.com -< > > > > >- DHTML Menus provided by www.APYCOM.com -< > > > > >- Lists hosted by www.Gradwell.com -< > > > > >- CFdeveloper is run by Russ Michaels, > feel free to volunteer your help > >-< > > > > > > ARAXI Traductions France > > > 100 Passage Henri Régnault - 92400 Courbevoie La Défense 6 > > > Tél. : 01 49 03 41 49 - Fax : 01 49 03 41 42 > > > _______________________________________________ > > > > > > For details on ALL mailing lists and for joining or leaving lists, go to > > > http://list.cfdeveloper.co.uk/mailman/listinfo > > > > > > -- > > > CFDeveloper Sponsors:- > > > >- Hosting provided by www.cfmxhosting.co.uk -< > > > >- Forum provided by www.fusetalk.com -< > > > >- DHTML Menus provided by www.APYCOM.com -< > > > >- Lists hosted by www.Gradwell.com -< > > > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help > >-< > > > > > > > > > >_______________________________________________ > > > >For details on ALL mailing lists and for joining > >or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo > > > >-- > >CFDeveloper Sponsors:- > > >- Hosting provided by www.cfmxhosting.co.uk -< > > >- Forum provided by www.fusetalk.com -< > > >- DHTML Menus provided by www.APYCOM.com -< > > >- Lists hosted by www.Gradwell.com -< > > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< > >ARAXI Traductions France >100 Passage Henri Régnault - 92400 Courbevoie La Défense 6 >Tél. : 01 49 03 41 49 - Fax : 01 49 03 41 42 >_______________________________________________ > >For details on ALL mailing lists and for joining >or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo > >-- >CFDeveloper Sponsors:- > >- Hosting provided by www.cfmxhosting.co.uk -< > >- Forum provided by www.fusetalk.com -< > >- DHTML Menus provided by www.APYCOM.com -< > >- Lists hosted by www.Gradwell.com -< > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< > >********************************************************************** >This email and any files transmitted with it are >confidential and intended solely for the use of >the individual or entity to whom they are >addressed and should not be disclosed to any other party. >If you have received this email in error please >notify your system manager and the sender of this message. > >This email message has been swept for the >presence of computer viruses but no guarantee is >given that this e-mail message and any attachments are free from viruses. > >Fife Council >Tel: +44 (0) 1592 414141 >********************************************************************** >_______________________________________________ > >For details on ALL mailing lists and for joining >or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo > >-- >CFDeveloper Sponsors:- > >- Hosting provided by www.cfmxhosting.co.uk -< > >- Forum provided by www.fusetalk.com -< > >- DHTML Menus provided by www.APYCOM.com -< > >- Lists hosted by www.Gradwell.com -< > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< ARAXI Traductions France 100 Passage Henri Régnault - 92400 Courbevoie La Défense 6 Tél. : 01 49 03 41 49 - Fax : 01 49 03 41 42 _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
