I'm passing a date from SelectPerfDate.cfm via drill down box as follows:
<SELECT NAME="dateSelected" SIZE="3">
<CFOUTPUT QUERY="GetMonth">
<CFSET choice = #mth_name# & #year#>
<OPTION VALUE="#trim(mth_name)# #trim(year)#">#mth_name# #year#
</CFOUTPUT>
</SELECT>
TO an UpdateDetail.cfm form page (parsing the date info from the above template) as follows:
<CFSET parsedPerfDate = ParseDateTime(dateSelected)>
<CFSET ODBCSelectedDate = CreateODBCDateTime(parsedPerfDate)>
UpdateDetail.cfm records must be updated or bypassed and posted to the UpdatePerf.cfm action page as follows:
<input type="checkbox" name="BypassMthCheck" VALUE="#RowIndex#" CHECKED></FONT></TD>
<CFELSE>
<input type="checkbox" name="BypassMthCheck" VALUE="#RowIndex#"></FONT></TD>
</CFIF>
<INPUT TYPE="text" NAME="actl_perf_amt" SIZE="10" MAXLENGTH="10" VALUE="#trim(NumberFormat(perfArray[RowIndex][2],"999,999,999.00__"
)#"></FONT></TD>
UpdatePerf.cfm Action Page code is as follows:
<CFTRANSACTION>
<CFLOOP Index="ListRead" From ="1" To="#FormrecCount#">
<CFIF NOT parameterExists (BypassMthCheck)>
<CFSET ignore_month = "N">
<CFSET msur_actl_perf_amt = #ListGetAt(form.actl_perf_amt, ListRead, ","#>
<CFELSEIF ListContains(BypassMthCheck, ListRead, "," IS 0>
<CFSET ignore_month = "N">
<CFSET msur_actl_perf_amt = #ListGetAt(form.actl_perf_amt, ListRead, ","#>
<CFELSE>
<CFSET ignore_month = "Y">
<CFSET msur_actl_perf_amt = 0>
</CFIF>
<CFSET mth_pog = 0>
<CFSET ytd_pog = 0>
<CFSET ytd_perf = 0>
<CFSET actl_perf_amt = #VAL(msur_actl_perf_amt)#>
<CFSET msur_freq = ListGetAt(form.msur_freq, ListRead, ",">
<CFSET msur_fmla_oper = ListGetAt(form.msur_fmla_oper, ListRead, ",">
<CFSET expc_perf_amt = ListGetAt(form.expc_perf_amt, ListRead, ",">
<CFSET msur_id = ListGetAt(form.msur_id, ListRead, ",">
<CFSET dlvr_id = ListGetAt(form.dlvr_id, ListRead, ",">
<CFSET Vmsur_freq = ListGetAt(form.msur_freq, ListRead, ",">
<CFSET perf_date = #ParseDateTime(ODBCSelectedDate)#>
<CFSET ODBCSelectedDate = #ParseDateTime(perf_date)#>
<!-- Get perfRecords -->
<CFQUERY NAME="GetPerf" DATASOURCE="PGM">
SELECT *
FROM Performance
WHERE Performance.dlvr_id = #ListGetAt(form.dlvr_id, ListRead, ","#
AND Performance.msur_id = #ListGetAt(form.msur_id, ListRead, ","#
AND Performance.perf_date = #ODBCSelectedDate#
</CFQUERY>
<CFIF GetPerf.RecordCount IS 0>
<CFQUERY NAME="InsertPerf" DATASOURCE="PGM">
INSERT INTO Performance(perf_date, actl_perf_amt, dlvr_id, msur_id, ignore_month)
VALUES (#ODBCSelectedDate#, '#msur_actl_perf_amt#', #dlvr_id#, #msur_id#, '#ignore_month#')
</CFQUERY>
<CFELSE>
<CFQUERY NAME="UpdtPerf" DATASOURCE="PGM">
UPDATE Performance
SET actl_perf_amt = '#msur_actl_perf_amt#',
ignore_month = '#ignore_month#'
WHERE dlvr_id = #ListGetAt(form.dlvr_id, ListRead, ","#
AND msur_id = #ListGetAt(form.msur_id, ListRead, ","#
AND perf_date = '#perf_date#'
</CFQUERY>
</CFIF>
<CFINCLUDE TEMPLATE="CalcPGM.cfm">
<CFQUERY NAME="UpdtPerf" DATASOURCE="PGM">
UPDATE Performance
SET mth_pog = #mth_pog#,
ytd_perf = #ytd_perf#,
ytd_pog = #ytd_pog#
WHERE dlvr_id = #ListGetAt(form.dlvr_id, ListRead, ","#
AND msur_id = #ListGetAt(form.msur_id, ListRead, ","#
AND perf_date = #ODBCSelectedDate#
</CFQUERY>
</CFLOOP>
</CFTRANSACTION>
<CFINCLUDE TEMPLATE="UpdateDetail.cfm">
I'm getting the following error message which appears the date is not parsing correctly. Also, note the column of "N"'s .....the values should be passed as N,Y,N,N,Y,N,Y,Y,Y --- What, in the code, is causing that to happen? Any held would be greatly appreciated. I'm at the drop-kicking point with this!!!!!
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
{ts '2001-08-01 00:00:00'} 25.3333,0.0000,22.00,59.00,0.0000,98.00,0.0000,0.0000,0.0000 617,617,617,617,617,618,618,618,618 1610,1611,1612,1613,1614,1615,1616,1617,1618 N,N,N,N,N,N,N,N,N
--------------------------------------------------------------------------------
Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 6: Incorrect syntax near '2001'.
SQL = "UPDATE Performance SET actl_perf_amt = '25.3333', ignore_month = 'N' WHERE dlvr_id = 617 AND msur_id = 1610 AND perf_date = '{ts '2001-08-01 00:00:00'}'"
<SELECT NAME="dateSelected" SIZE="3">
<CFOUTPUT QUERY="GetMonth">
<CFSET choice = #mth_name# & #year#>
<OPTION VALUE="#trim(mth_name)# #trim(year)#">#mth_name# #year#
</CFOUTPUT>
</SELECT>
TO an UpdateDetail.cfm form page (parsing the date info from the above template) as follows:
<CFSET parsedPerfDate = ParseDateTime(dateSelected)>
<CFSET ODBCSelectedDate = CreateODBCDateTime(parsedPerfDate)>
UpdateDetail.cfm records must be updated or bypassed and posted to the UpdatePerf.cfm action page as follows:
<input type="checkbox" name="BypassMthCheck" VALUE="#RowIndex#" CHECKED></FONT></TD>
<CFELSE>
<input type="checkbox" name="BypassMthCheck" VALUE="#RowIndex#"></FONT></TD>
</CFIF>
<INPUT TYPE="text" NAME="actl_perf_amt" SIZE="10" MAXLENGTH="10" VALUE="#trim(NumberFormat(perfArray[RowIndex][2],"999,999,999.00__"
)#"></FONT></TD>UpdatePerf.cfm Action Page code is as follows:
<CFTRANSACTION>
<CFLOOP Index="ListRead" From ="1" To="#FormrecCount#">
<CFIF NOT parameterExists (BypassMthCheck)>
<CFSET ignore_month = "N">
<CFSET msur_actl_perf_amt = #ListGetAt(form.actl_perf_amt, ListRead, ","
#><CFELSEIF ListContains(BypassMthCheck, ListRead, ","
IS 0><CFSET ignore_month = "N">
<CFSET msur_actl_perf_amt = #ListGetAt(form.actl_perf_amt, ListRead, ","
#><CFELSE>
<CFSET ignore_month = "Y">
<CFSET msur_actl_perf_amt = 0>
</CFIF>
<CFSET mth_pog = 0>
<CFSET ytd_pog = 0>
<CFSET ytd_perf = 0>
<CFSET actl_perf_amt = #VAL(msur_actl_perf_amt)#>
<CFSET msur_freq = ListGetAt(form.msur_freq, ListRead, ","
><CFSET msur_fmla_oper = ListGetAt(form.msur_fmla_oper, ListRead, ","
> <CFSET expc_perf_amt = ListGetAt(form.expc_perf_amt, ListRead, ","
><CFSET msur_id = ListGetAt(form.msur_id, ListRead, ","
><CFSET dlvr_id = ListGetAt(form.dlvr_id, ListRead, ","
><CFSET Vmsur_freq = ListGetAt(form.msur_freq, ListRead, ","
><CFSET perf_date = #ParseDateTime(ODBCSelectedDate)#>
<CFSET ODBCSelectedDate = #ParseDateTime(perf_date)#>
<!-- Get perfRecords -->
<CFQUERY NAME="GetPerf" DATASOURCE="PGM">
SELECT *
FROM Performance
WHERE Performance.dlvr_id = #ListGetAt(form.dlvr_id, ListRead, ","
#AND Performance.msur_id = #ListGetAt(form.msur_id, ListRead, ","
#AND Performance.perf_date = #ODBCSelectedDate#
</CFQUERY>
<CFIF GetPerf.RecordCount IS 0>
<CFQUERY NAME="InsertPerf" DATASOURCE="PGM">
INSERT INTO Performance(perf_date, actl_perf_amt, dlvr_id, msur_id, ignore_month)
VALUES (#ODBCSelectedDate#, '#msur_actl_perf_amt#', #dlvr_id#, #msur_id#, '#ignore_month#')
</CFQUERY>
<CFELSE>
<CFQUERY NAME="UpdtPerf" DATASOURCE="PGM">
UPDATE Performance
SET actl_perf_amt = '#msur_actl_perf_amt#',
ignore_month = '#ignore_month#'
WHERE dlvr_id = #ListGetAt(form.dlvr_id, ListRead, ","
#AND msur_id = #ListGetAt(form.msur_id, ListRead, ","
#AND perf_date = '#perf_date#'
</CFQUERY>
</CFIF>
<CFINCLUDE TEMPLATE="CalcPGM.cfm">
<CFQUERY NAME="UpdtPerf" DATASOURCE="PGM">
UPDATE Performance
SET mth_pog = #mth_pog#,
ytd_perf = #ytd_perf#,
ytd_pog = #ytd_pog#
WHERE dlvr_id = #ListGetAt(form.dlvr_id, ListRead, ","
#AND msur_id = #ListGetAt(form.msur_id, ListRead, ","
#AND perf_date = #ODBCSelectedDate#
</CFQUERY>
</CFLOOP>
</CFTRANSACTION>
<CFINCLUDE TEMPLATE="UpdateDetail.cfm">
I'm getting the following error message which appears the date is not parsing correctly. Also, note the column of "N"'s .....the values should be passed as N,Y,N,N,Y,N,Y,Y,Y --- What, in the code, is causing that to happen? Any held would be greatly appreciated. I'm at the drop-kicking point with this!!!!!
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
{ts '2001-08-01 00:00:00'} 25.3333,0.0000,22.00,59.00,0.0000,98.00,0.0000,0.0000,0.0000 617,617,617,617,617,618,618,618,618 1610,1611,1612,1613,1614,1615,1616,1617,1618 N,N,N,N,N,N,N,N,N
--------------------------------------------------------------------------------
Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 6: Incorrect syntax near '2001'.
SQL = "UPDATE Performance SET actl_perf_amt = '25.3333', ignore_month = 'N' WHERE dlvr_id = 617 AND msur_id = 1610 AND perf_date = '{ts '2001-08-01 00:00:00'}'"