ColdFusion update to Access Database "Too Few Parameters"

0
votes

I have added a new field to my Access table called AssignedDate, data type date/time. I then added it to the existing update query and I get this error:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]
Too few parameters. Expected 1.

UpdatedDate = #Now()#, AssignedDate = #Now()# 503 : WHERE RequestID = '#arguments.RequestID#'
504 : </cfquery>
505 : </cffunction>
SQL: UPDATE Requests_Main SET CADOperatorID = '', EngineerID = 'EAGLE', RequestStatusID = '1', AssingedDate = {ts '2014-11-26 12:50:02'}, UpdatedDate = {ts '2014-11-26 12:50:02'}, AssignedDate = {ts '2014-11-26 12:50:02'} WHERE RequestID = '13999'
DATASOURCE XXX
VENDORERRORCODE -3010
SQLSTATE 07002

Everything is spelt correctly and the data types match, but it will not update.

If somebody can help explain why, I would be very appreciative.

This is the query without any change other than adding the new field. I have tried adding single quotes around the variables, where they are missing, but that didn't work either.

`<cffunction name="UpdateRequest_Assignments" access="public" output="false">
    <cfargument name="RequestID" type="numeric" required="true">
        <cfargument name="CADOperatorID" type="string" required="true">
        <cfargument name="EngineerID" type="string" required="true">
        <cfargument name="RequestStatusID" type="numeric" required="true">

            <cfquery datasource="#request.DSN#">
                UPDATE Requests_Main
                SET CADOperatorID = '#arguments.CADOperatorID#',
                EngineerID = '#arguments.EngineerID#',
                RequestStatusID = #arguments.RequestStatusID#,
                AssingedDate = #Now()#,
                UpdatedDate = #Now()#,
                AssignedDate = #Now()#
                WHERE RequestID = #arguments.RequestID#
            </cfquery>
2
ms-accesscoldfusion
A misspelled field name will be interpreted to be a parameter. Is there an issue between AssingedDate and AssignedDate? - HansUp
@Kstomp - For next time, error messages belong in the question, so they are more visible. FYI, error message moved into the question. Please delete the redundant comment. - Leigh
@HansUp - Good spot. That is probably the issue. Kstomp, as an aside always use cfqueryparam on all variable query parameters. - Leigh
What is suspicious about an empty string? Care to elaborate? - Leigh
If you meant "ID" suggests a numeric data type, rather string/text - maybe. However, if that were the case it should generate a different error, something about data type mismatch. - Leigh

2 Answers

1
votes

I think @hansup is right here. You probably did NOT name your new column "asingeddate." Meanwhile if you meant it to be asigneddate, then why would you have that column listed twice in your update? That would also cause an error right?

1
votes

I think problem is you are updating same column 2 times in single query. Check your update statement same column 'AssingedDate' is listed twice. Remove it and try it again. Moreover, I would use cfqueryparam when updating table. Best practices :).

                <cfquery datasource="#request.DSN#">
                    UPDATE Requests_Main
                    SET   CADOperatorID = <cfqueryPARAM value = "#arguments.CADOperatorID#" CFSQLType = 'CF_SQL_VARCHAR'>  ,
                          EngineerID = <cfqueryPARAM value = "#arguments.EngineerID#" CFSQLType = 'CF_SQL_VARCHAR'>,
                          RequestStatusID = <cfqueryPARAM value = "#arguments.RequestStatusID#" CFSQLType = 'CF_SQL_NUMERIC'> ,
                          AssingedDate = <cfqueryPARAM value = "#Now()#" CFSQLType = 'CF_SQL_TIMESTAMP'>,
                          UpdatedDate = <cfqueryPARAM value = "#Now()#" CFSQLType = 'CF_SQL_TIMESTAMP'>,
                    WHERE RequestID = <cfqueryPARAM value = "#arguments.RequestStatusID#" CFSQLType = 'CF_SQL_NUMERIC'>
                </cfquery>