- see footer for list info -<
just to confirm somewhere the date is being taken as a calculation
when I change the date field to a text filed
instead of the funny 10/06/1905 date I get:
2006/03/16 = 41.7916666666667 (my result in acces)
2006-03-16 = 1987 (my result in acces)
woldn't it be in the values line where we have to
once again specify that the data is actually a date?
<cfset query_string = query_string & " ) VALUES (#CurrencyRateDate#" />
could be rewritten something like
<cfset query_string = query_string & " ) VALUES
(DateFormat(#CurrencyRateDate#,'yyyy/mm/dd')" />
...need a little help with the syntax though :-}
At 10:15 16/03/2006, you wrote:
>- 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 -<
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 -<
