SQL Update query

Ignition
1

I have the follow script on a button:

date1= event.source.parent.getComponent(‘Label ddMMyyyy’).text
site1 = str(‘CootehillWTW’)
weather = str(event.source.parent.getComponent(‘Weather’).text)
Elect_Day = event.source.parent.getComponent(‘Elect Day’).floatValue
Elect_Night = event.source.parent.getComponent(‘Elect Night’).floatValue
RW_Flow = event.source.parent.getComponent(‘RW Flow’).floatValue
BW_Flow = event.source.parent.getComponent(‘BW Flow’).floatValue
TW_Flow = event.source.parent.getComponent(‘TW Flow’).floatValue
Final_Flow = event.source.parent.getComponent(‘Final Water Flow’).floatValue
Flow_Comment = event.source.parent.getComponent(‘Flow Comment’).text
pH_RW = event.source.parent.getComponent(‘ph RW’).floatValue
pH = event.source.parent.getComponent(‘pH’).floatValue
pH_Final = event.source.parent.getComponent(‘pH Res Final Water’).floatValue
Colour_RW = event.source.parent.getComponent(‘Col RW’).floatValue
Color_Final = event.source.parent.getComponent(‘Col Final’).floatValue
Turbidity_RW = event.source.parent.getComponent(‘Turbidity RW’).floatValue
Turbidity_Final = event.source.parent.getComponent(‘Turbidity Final Water’).floatValue
Free_Res_Chlor = event.source.parent.getComponent(‘Free Residual Chlorine’).floatValue
Total_Chlor = event.source.parent.getComponent(‘Total Chlorine’).floatValue
Res_Alum = event.source.parent.getComponent(‘Residual Alum’).floatValue
Correct_Alum = event.source.parent.getComponent(‘Corrected Alum’).floatValue
Res_Fluoride = event.source.parent.getComponent(‘Residual Fluoride’).floatValue
Fluoride_Consumed = event.source.parent.getComponent(‘Fluoride Consumed’).floatValue
RW_Manganese = event.source.parent.getComponent(‘Manganese RW’).floatValue
Final_Manganese = event.source.parent.getComponent(‘Manganese Final Water’).floatValue
Lab_Comment = event.source.parent.getComponent(‘Lab Comment’).text
caretaker = event.source.parent.getComponent(‘Username’).text

ans = system.db.runPrepUpdate(“UPDATE REPORT_WTW SET SiteName=?, Weather=?, Elect_Day=?, Elect_Night=?,RW_Flow=?,BW_Flow=?,TW_Flow=?,Final_Flow=?,Flow_Comment=?,pH_RW=?,pH=?,pH_Final=?,Colour_RW=?,Color_Final=?,Turbidity_RW=?,Turbidity_Final=?,Free_Residual_Chlorine=?,Total_Chlorine=?,Res_Alum=?,Correct_Alum=?,Res_Fluoride=?,Fluoride_Consumed=?,RW_Manganese=?,Final_Manganese=?,Lab_Comment=?,Caretaker=? [color=#FF0000]WHERE DateofReport = ? [/color]”, [site1, weather,Elect_Day,Elect_Night,RW_Flow,BW_Flow,TW_Flow,Final_Flow,Flow_Comment,pH_RW,pH,pH_Final,Colour_RW,Color_Final,Turbidity_RW,Turbidity_Final,Free_Res_Chlor,Total_Chlor,Res_Alum,Correct_Alum,Res_Fluoride,Fluoride_Consumed,RW_Manganese,Final_Manganese,Lab_Comment,caretaker,date1])

My query isn’t throwing up any errors yet doesn’t update the database. if I force WHERE DateofReport = ‘02-03-2014’ then it updates record ok

2

I’m guessing that the update not happening is because the date doesn’t exactly match.
Without seeing the text in date1 I’m assuming you have it formatted as ddMMyyyy which would make it be interpreted as an integer i.e. 02-02-2014 => 2032014.
Have you tried formatting the date as dd-MMM-yyyy ? I/We always use the short Month name format as SQL often attempts to convert dates to M/D/Y.
Also you may need to wrap it in single-quotes if you are expecting a literal translation.
e.g.

WHERE DateOfReport='?'
3

emca_user,

   Have you resolved your issue?