[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]

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

[acoder]

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

[ndeeley]

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

[acoder]

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

[ndeeley]

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:

Expand|Select|Wrap|Line Numbers

1.  
2.     <cfquery datasource="repairmdb" name="GetClientNo">
3.     select         ID, 
4.             WorkshopFK, 
5.             ClientFK,
6.             SiteFK,
7.             WORecdate,
8.             ResponseLevelFK,
9.             ClientWONO,
10.             WorkshopWONO,
11.             WorkshopJOBNO,
12.             AssetNO,
13.             AssetClassFK,
14.             LocalID,
15.             ManufacturerFK,
16.             SerialNO,
17.             BuildYear,
18.             ModelFK,
19.             Frame,
20.             PowerRating,
21.             Volts,
22.             Rmin,
23.             Amps,
24.             Ph,
25.             ImpellerDetails,
26.             Hz,
27.             ExRated,
28.             AssetNotes,
29.             QuoteP,
30.             QuotePDate,
31.             Price,
32.             QuoteAccepted,
33.             QuoteACDate,
34.             TargetCDate,
35.             UQuoteNotes,
36.             ActualCDate,
37.             DeliveryDate,
38.             FailureReasonFK,
39.             FRNotes,
40.             RSLoggedDate,
41.             RSInspectDate,
42.             InspectedByFK,
43.             RSQuotedDate,
44.             RSApprovedDate,
45.             RSWIPDate,
46.             AssignedToFK,
47.             RSDelDate,
48.             RSCompDate
49.     from        tblWorkshops
50.     where        1=1
51.             <cfif #form.csearch# is not "">
52.                    and ClientWONO like '%#form.csearch#%'
53.                 </cfif>
54.  

And this is the update query code:

Expand|Select|Wrap|Line Numbers

1. <cftransaction>
2.  
3. <cfparam name="form.ExRated" default="0">
4. <cfparam name="form.QuoteP" default="0">
5. <cfparam name="form.QuoteAccepted" default="0">
6.  
7.  
8. <cfquery name="updatedatabase" datasource="repairmdb">
9.  
10. update     tblWorkshops
11. set    WorkshopFK = '#form.WorkshopFK#',    
12.     ClientFK = '#form.ClientFK#',
13.     SiteFK = '#form.SiteFK#',
14.     WORecdate = #form.WORecdate#,
15.     ResponseLevelFK = '#form.ResponseLevelFK#',
16.     ClientWONO = '#form.ClientWONO#',
17.     WorkshopWONO = '#form.WorkshopWONO#',
18.     WorkshopJOBNO = '#form.WorkshopJOBNO#',
19.     AssetNO = '#form.AssetNO#',
20.     AssetClassFK = '#form.AssetClassFK#',
21.     LocalID = '#form.LocalID#',
22.     ManufacturerFK = '#form.ManufacturerFK#',
23.     SerialNO = '#form.SerialNO#',
24.     BuildYear = '#form.BuildYear#',
25.     ModelFK ='#form.ModelFK#',
26.     Frame = '#form.Frame#',
27.     PowerRating = '#form.PowerRating#',
28.     Volts = '#form.Volts#',
29.     Rmin = '#form.Rmin#',
30.     Amps = '#form.Amps#',
31.     Ph = '#form.Ph#',
32.     ImpellerDetails = '#form.ImpellerDetails#',
33.     Hz = '#form.Hz#',
34.     ExRated = #form.ExRated#,
35.     AssetNotes = '#form.AssetNotes#',
36.     QuoteP = #form.QuoteP#,
37.     QuotePDate = '#form.QuotePDate#',
38.     Price = '#form.Price#',
39.     QuoteAccepted = #form.QuoteAccepted#,
40.     QuoteACDate = '#form.QuoteACDate#',
41.     TargetCDate = '#form.TargetCDate#',
42.     UQuoteNotes = '#form.UQuoteNotes#',
43.     ActualCDate = '#form.ActualCDate#',
44.     DeliveryDate = '#form.DeliveryDate#',
45.     FailureReasonFK = '#form.FailureReasonFK#',
46.     FRNotes = '#form.FRNotes#',
47.     RSLoggedDate = '#form.RSLoggedDate#',
48.     RSInspectDate = '#form.RSInspectDate#',
49.     InspectedByFK = '#form.InspectedByFK#',
50.     RSQuotedDate = '#form.RSQuotedDate#',
51.     RSApprovedDate = '#form.RSApprovedDate#',
52.     RSWIPDate = '#form.RSWIPDate#',
53.     AssignedToFK = '#form.AssignedToFK#',
54.     RSDelDate = '#form.RSDelDate#',
55.     RSCompDate = '#form.RSCompDate#'
56. where     ID = #form.ID#
57.  
58. </cfquery>
59. </cftransaction>
60.  
61.  

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.