Change of date when using an UPDATE query in CF

[ndeeley]

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

[acoder]

Use cfqueryparam for the data input and set the CFSQLType attribute to use the CF_SQL_DATE type.

[ndeeley]

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

[acoder]

It has to be within the query, so that would be the action page.

[ndeeley]

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!)

[acoder]

No problem. If you get stuck, post your code and I'll see what I can do.

[ndeeley]

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

[ndeeley]

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

[acoder]

Yes, it usually is the smallest of things causing all the problem.

Glad you managed to solve it.