Re: [ cf-dev ] Updating a Date into a Database

[Stephen Moretti]

James,

You're missing single quotes from around #form.FaresTxt#

Stephen

James Buckingham wrote:

Thanks for the quick response guy. I've change some of my code but I'm now
getting errors. Here's my query:
<cfquery datasource="#application.datasource#"
name="update_DateTable_record">	
	UPDATE Tour_dates
	SET Tour_dates.departuredates =
#createODBCDate(form.DepartureDateTxt)#
	  , Tour_dates.returndates = #createODBCDate(form.ReturnDateTxt)#
	  , Tour_dates.fares = #form.FaresTxt#
	WHERE (Tour_dates.Date_ID=#url.Tour_ID#) AND
(Tour_dates.Tour_ID=#url.ID#)	
</cfquery> 

And I get the following error:

ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
expression.
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (29:3) to (29:80).
...I'm also getting an error if I removed the single quotes from around the
#form# functions.
Any ideas?

-----Original Message-----
From: Dave Phipps [mailto:[EMAIL PROTECTED] 
Sent: 22 January 2004 14:11
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] Updating a Date into a Database

Hi James,

You might want to try the following:

<cfquery datasource="#application.datasource#" 
 name="update_record2">
UPDATE Tour_dates
SET Tour_dates.departuredates =
#createODBCDate(form.DepartureDateTxt)#,
Tour_dates.returndates =
#createODBCDate(form.ReturnDateTxt)#, Tour_dates.fares = '#form.FaresTxt#'
WHERE (Tour_dates.Date_ID=#url.Tour_ID#)
AND Tour_dates.Tour_ID=#url.ID#)
</cfquery>

Hope this helps.

Cheers

Dave

On Thu, 22 Jan 2004 14:02:06 -0000
  James Buckingham <[EMAIL PROTECTED]> wrote:
Afternoon guys,

I'm a bit stuck with some dates functions I'm working with and wondered 
if anyone can help. I've got a form which is  used to Amend information 
in an Access DB. When the user clicks the 'Amend' button the 
information in sent to a query to update the appropriate field. I can 
get it update everything though apart from the Departure and Return 
Date fields.
These are set to
Date/Time in Access. 

I sent a question in earlier and got this reply from Stephen Moretti:

"DateTime fields in Access need to be fed ODBC formatted date/times 
either using CreateODBCDate/CreateODBCDateTime or <cfqueryparam> You 
don't need to wrap odbc datetimes in quotes."

I've messed around with these ideas but no matter what I try I get an 
error message coming back. Here's what I've got at the moment:

<cfquery datasource="#application.datasource#" 
name="update_record2">
<cfset Departure_Date =
createODBCDate(#form.DepartureDateTxt#)>
<cfset Return_Date =
createODBCDate(#form.ReturnDateTxt#)>

UPDATE Tour_dates
SET Tour_dates.departuredates = '#Departure_Date#'
 , Tour_dates.returndates = '#Return_Date#'
 , Tour_dates.fares = '#form.FaresTxt#'
WHERE (Tour_dates.Date_ID=#url.Tour_ID#) AND
(Tour_dates.Tour_ID=#url.ID#)
</cfquery>
I've done a CFOUTPUT of the SET Departure_Date and it comes out as {d 
'2004-11-09'} I've also tried converting it using DateFormat first and 
then inserting it but nothing is working.

Can anyone give me some pointers?

Cheers,
James


--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED] For human
help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]