Change of date when using an UPDATE query in CF | Member | | Join Date: Mar 2007
Posts: 94
| | |
Hi!
I have data returned from a database displayed on a webpage for amending.
A date on the form is selected using a pop-up calendar which returns it to Access. When the date is retrieved the dateFormat function is used to display it as dd/mm/yyyy.
However, when I run an update query, allowing the user to change any field and refind the record, the date is always changed to 30/12/1899.
Is there a problem with my update query?
update tblWorkshops
set WORecdate = #form.WORecdate#,
where ID = #ID#
I have not used createODBCdate(variable_Name) as I don't know where it needs to be created (ie on the cfm webpage or the action script that updates the database).
Thanks!
Neil
|  | Site Moderator | | Join Date: Nov 2006 Location: UK
Posts: 14,581
| | | re: Change of date when using an UPDATE query in CF
Use cfqueryparam for the data input and set the CFSQLType attribute to use the CF_SQL_DATE type.
| | Member | | Join Date: Mar 2007
Posts: 94
| | | re: Change of date when using an UPDATE query in CF
Quote:
Originally Posted by acoder Use cfqueryparam for the data input and set the CFSQLType attribute to use the CF_SQL_DATE type.
Hi acoder,
Do I do this on the .cfm form page or the action script behind the form?
Thanks
Neil
| | Site Moderator | | Join Date: Nov 2006 Location: UK
Posts: 14,581
| | | re: Change of date when using an UPDATE query in CF
It has to be within the query, so that would be the action page.
| | Member | | Join Date: Mar 2007
Posts: 94
| | | re: Change of date when using an UPDATE query in CF
Quote:
Originally Posted by acoder It has to be within the query, so that would be the action page.
Acoder,
Thanks for your help, I`m slowly piecing it together...
Neil (i`m a designer dammit, not a coder!)
| | Site Moderator | | Join Date: Nov 2006 Location: UK
Posts: 14,581
| | | re: Change of date when using an UPDATE query in CF
No problem. If you get stuck, post your code and I'll see what I can do.
| | Member | | Join Date: Mar 2007
Posts: 94
| | | re: Change of date when using an UPDATE query in CF
Quote:
Originally Posted by acoder No problem. If you get stuck, post your code and I'll see what I can do.
Yup, I`m stuck!
The query takes an inputted workorder number, matches it against the field in the database and returns the unique Workorder value and associated data. It's when the record is updated that the date value is being changed. Here's the associated code:
Find record code: -
-
<cfquery datasource="repairmdb" name="GetClientNo">
-
select ID,
-
WorkshopFK,
-
ClientFK,
-
SiteFK,
-
WORecdate,
-
ResponseLevelFK,
-
ClientWONO,
-
WorkshopWONO,
-
WorkshopJOBNO,
-
AssetNO,
-
AssetClassFK,
-
LocalID,
-
ManufacturerFK,
-
SerialNO,
-
BuildYear,
-
ModelFK,
-
Frame,
-
PowerRating,
-
Volts,
-
Rmin,
-
Amps,
-
Ph,
-
ImpellerDetails,
-
Hz,
-
ExRated,
-
AssetNotes,
-
QuoteP,
-
QuotePDate,
-
Price,
-
QuoteAccepted,
-
QuoteACDate,
-
TargetCDate,
-
UQuoteNotes,
-
ActualCDate,
-
DeliveryDate,
-
FailureReasonFK,
-
FRNotes,
-
RSLoggedDate,
-
RSInspectDate,
-
InspectedByFK,
-
RSQuotedDate,
-
RSApprovedDate,
-
RSWIPDate,
-
AssignedToFK,
-
RSDelDate,
-
RSCompDate
-
from tblWorkshops
-
where 1=1
-
<cfif #form.csearch# is not "">
-
and ClientWONO like '%#form.csearch#%'
-
</cfif>
-
And this is the update query code: -
<cftransaction>
-
-
<cfparam name="form.ExRated" default="0">
-
<cfparam name="form.QuoteP" default="0">
-
<cfparam name="form.QuoteAccepted" default="0">
-
-
-
<cfquery name="updatedatabase" datasource="repairmdb">
-
-
update tblWorkshops
-
set WorkshopFK = '#form.WorkshopFK#',
-
ClientFK = '#form.ClientFK#',
-
SiteFK = '#form.SiteFK#',
-
WORecdate = #form.WORecdate#,
-
ResponseLevelFK = '#form.ResponseLevelFK#',
-
ClientWONO = '#form.ClientWONO#',
-
WorkshopWONO = '#form.WorkshopWONO#',
-
WorkshopJOBNO = '#form.WorkshopJOBNO#',
-
AssetNO = '#form.AssetNO#',
-
AssetClassFK = '#form.AssetClassFK#',
-
LocalID = '#form.LocalID#',
-
ManufacturerFK = '#form.ManufacturerFK#',
-
SerialNO = '#form.SerialNO#',
-
BuildYear = '#form.BuildYear#',
-
ModelFK ='#form.ModelFK#',
-
Frame = '#form.Frame#',
-
PowerRating = '#form.PowerRating#',
-
Volts = '#form.Volts#',
-
Rmin = '#form.Rmin#',
-
Amps = '#form.Amps#',
-
Ph = '#form.Ph#',
-
ImpellerDetails = '#form.ImpellerDetails#',
-
Hz = '#form.Hz#',
-
ExRated = #form.ExRated#,
-
AssetNotes = '#form.AssetNotes#',
-
QuoteP = #form.QuoteP#,
-
QuotePDate = '#form.QuotePDate#',
-
Price = '#form.Price#',
-
QuoteAccepted = #form.QuoteAccepted#,
-
QuoteACDate = '#form.QuoteACDate#',
-
TargetCDate = '#form.TargetCDate#',
-
UQuoteNotes = '#form.UQuoteNotes#',
-
ActualCDate = '#form.ActualCDate#',
-
DeliveryDate = '#form.DeliveryDate#',
-
FailureReasonFK = '#form.FailureReasonFK#',
-
FRNotes = '#form.FRNotes#',
-
RSLoggedDate = '#form.RSLoggedDate#',
-
RSInspectDate = '#form.RSInspectDate#',
-
InspectedByFK = '#form.InspectedByFK#',
-
RSQuotedDate = '#form.RSQuotedDate#',
-
RSApprovedDate = '#form.RSApprovedDate#',
-
RSWIPDate = '#form.RSWIPDate#',
-
AssignedToFK = '#form.AssignedToFK#',
-
RSDelDate = '#form.RSDelDate#',
-
RSCompDate = '#form.RSCompDate#'
-
where ID = #form.ID#
-
-
</cfquery>
-
</cftransaction>
-
-
Not sure if it helps, but our ancient servers are running CF5.
Can't see what i am donig wrong for the date value to be changed in Access to 30/12/1899.
Thanks
Neil
| | Member | | Join Date: Mar 2007
Posts: 94
| | | re: Change of date when using an UPDATE query in CF
Acoder,
Its okay - I have fixed it. My mistake(Doh!) - I was passing the update value of the date as a number.
The smallest thing, eh?
Thanks for your help.
Neil xxxxxxxxxxxxxx
| | Site Moderator | | Join Date: Nov 2006 Location: UK
Posts: 14,581
| | | re: Change of date when using an UPDATE query in CF
Yes, it usually is the smallest of things causing all the problem.
Glad you managed to solve it.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,327 network members.
|
