Makes sense :) Cheers Peter
-----Original Message----- From: Peter Harrison [mailto:[EMAIL PROTECTED] Sent: 22 January 2004 15:46 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Updating a Date into a Database Yes, simply do this before or instead of the cfquery: <div><pre> <cfoutput> 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#) </cfoutput> </pre></div> - Peter -----Original Message----- From: James Buckingham [mailto:[EMAIL PROTECTED] Sent: 22 January 2004 15:30 To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] Updating a Date into a Database Hi Peter, ... And thanks. Based of your comment is there anyway of just dumping the SQL results onto the screen for me to review or would I need to setup a CFOUTPUT to do this? I'm looking to add some validation at a later point definatly but I'm completely new to CF so I'm trying to tackle one thing at a time ;) Appreciate the feedback though. Cheers, James -----Original Message----- From: Peter Harrison [mailto:[EMAIL PROTECTED] Sent: 22 January 2004 15:09 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Updating a Date into a Database This is not directly an answer to your error, but I have an important suggestion: Validate your data (eg. ID cannot be blank), escape the potential special characters (such as single quotes) before using it in the query. This prevents what are known as SQL injection exploits. Now, to help solve your problem, try outputting the SQL directly into the page, copy and paste the resulting query into whatever database query tool you are using to test it. It might visually show you where the query is flawed. HTH - Peter -----Original Message----- From: James Buckingham [mailto:[EMAIL PROTECTED] Sent: 22 January 2004 14:43 To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] Updating a Date into a Database 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] -- ** 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] -- ** 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]
