ColdFusion function returns an error “The system has attempted to use an undefined value”?

[pirlo89]

I have function that should save some form fields. This function is able to process multiple insert rows. So far I was able to create logic to handle this but the only issue so far I have once user tries to save the record. The server response looks like this:

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code. Null Pointers are another name for undefined values. coldfusion

At first I was looking and trying to find which value is not defined/missing in my function but so far I couldn't detect the issue. The next thing I did was commenting out the cfquery. After that I was getting message Form successfully saved.. This tells me that my code is breaking inside of the cfquery tag. Here is example of my code:

<cffunction name="saveRecords" access="remote" output="false" returnformat="JSON"> <cfargument name="formID" type="string" required="true" default=""> <cfargument name="status1" type="string" required="true" default="0"> <cfargument name="status2" type="string" required="true" default="0"> <cfargument name="status3" type="string" required="true" default="0"> <cfargument name="status4" type="string" required="true" default="0"> <cfargument name="status5" type="string" required="true" default="0"> <cfargument name="comment1" type="string" required="true" default=""> <cfargument name="comment2" type="string" required="true" default=""> <cfargument name="comment3" type="string" required="true" default=""> <cfargument name="comment4" type="string" required="true" default=""> <cfargument name="comment5" type="string" required="true" default="">  <cfset local.fnResults = structNew() /> <cfset local.runProcess = true /> <cfset local.arrStatus = arrayNew(1) />  <cfloop collection="#arguments#" item="i"> <cfif findNoCase(left(i,6), "status") and arguments[i] eq 1> <cfset arrayAppend(local.arrStatus, right(i,1)) /> </cfif> </cfloop>  <cfset local.frmValidation = { formID : len(arguments.formID), status1 : ArrayContains([0,1], trim(arguments.status1)), status2 : ArrayContains([0,1], trim(arguments.status2)), status3 : ArrayContains([0,1], trim(arguments.status3)), status4 : ArrayContains([0,1], trim(arguments.status4)), status5 : ArrayContains([0,1], trim(arguments.status5)) }/>  <cfloop collection="#frmValidation#" item="i"> <cfif !frmValidation[i] or !arrayLen(arrStatus)> <cfset local.runProcess = false /> <cfset local.fnResults = {status: 400, message: "Form data is either missing or incorrect."}> <cfbreak> </cfif> </cfloop>  <cfif runProcess> <!--- <cfquery name="saveRec" datasource="testDB"> <cfloop array="#arrStatus#" index="i"> INSERT INTO formDetails ( formid, refid, status, comment, userid, lastupdate ) SELECT <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.formID)#">, #i#, <cfqueryparam cfsqltype="cf_sql_bit" value="#trim(arguments["status"&i])#">, <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="8000" value="#trim(arguments["comment"&i])#" null="#!len(trim(arguments["comment"&i]))#">, <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="6" value="#trim(session.userid)#" null="#!len(trim(session.userid))#">, #now()# WHERE NOT EXISTS ( SELECT refid FROM formDetails WHERE formid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.formID)#"> AND refid = #i# ) </cfloop> </cfquery> ---> <cfset local.fnResults = {status: 200, message: "Form successfully saved!"}> </cfif>  <cfreturn fnResults> </cffunction>

One thing that I forgot to mention is that before I commented out cfquery, I tried to save the records and error will occur that I mentioned above but at the same time record is saved in database. That is weird since I didn't expect query to execute successfully because of the error. I use Sybase database and here are the details about formDetails table:

column name    data type   length recid          numeric      18      PK formid         numeric      10      FK refid          numeric      18      FK status         bit          1 comment        varchar      8000 userid         varchar      6 lastupdate     datetime     23

I'm not sure where my code is breaking and why I'm getting an error message about undefined value. If anyone can help or provide some useful resource on how to fix this issue please let me know.

[BKBK]

You could

• simplify your query code significantly, as below, inserting single quotes within strings
• check for the existence of the session variable

<cfloop array="#arrStatus#" index="i">

    <cfquery name="getRefId" datasource="testDB">

        SELECT refid

        FROM formDetails

        WHERE formid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.formID)#">

        AND refid = #i#

    </cfquery>

    <cfif getRefId.recordcount eq 0>

        <cfquery name="saveRec" datasource="testDB">

            INSERT INTO formDetails ( formid, refid, status, comment, userid, lastupdate )

            VALUES (<cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.formID)#">,

                            #i#,

                            <cfqueryparam cfsqltype="cf_sql_bit" value="#trim(arguments['status'&i])#">,

                            <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="8000" value="#trim(arguments['comment'&i])#" null="#!len(trim(arguments['comment'&i]))#">,

                            <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="6" value="#trim(session.userid)#" null="#!isDefined('session.userid') OR len(trim(session.userid)) EQ 0#">,

                            #now()#)

        </cfquery>

    </cfif>                              

</cfloop>