detail update error

[Tbrondolo]

I'm running into the following list update error. The update works sometimes, but then I get the following other times.

The error is "The database could not find the data you requested"


This the debug sql error. the primary key field is called RECID and is


SQL Commands for Row: 3 (Primary key: 96484)
UPDATE clienthistory SET [PAYMENT] = :new_PAYMENT WHERE ([RECID] = :primaryKey_1) AND ((PAYMENT = :old_PAYMENT OR PAYMENT IS NULL AND :old_PAYMENT IS NULL))
:new_payment (N) 20
:old_payment (N) 0
:primarykey_1 (N) 96484

Database server reported an error:
The database could not find the data you requested

[Tbrondolo]

Re: detail update error

update on this.

So I recreated all my controls and limited the number of records returned to 10 on the list.
I also checked the back end MYsql table
The results are inconsistent, I can do two or three entries fine, then I get the error.

[GGandhi]

Re: detail update error

UPDATE clienthistory SET [PAYMENT] = :new_PAYMENT WHERE ([RECID] = :primaryKey_1) AND ((PAYMENT = :old_PAYMENT OR PAYMENT IS NULL AND :old_PAYMENT IS NULL))

to me it looks like here is a problem on combination
in mysql you may not need [] but won't hurt.
if the logic is

UPDATE clienthistory SET PAYMENT = :new_PAYMENT
WHERE (RECID = :primaryKey_1 AND PAYMENT = :old_PAYMENT)
OR (RECID = :primaryKey_1 AND PAYMENT IS NULL AND :old_PAYMENT IS NULL)

it should work.
i am just looking thru the code to see, definitely did not run thru mysql.

[Tbrondolo]

Re: detail update error

The code is a cRUD operation created by Alpha. What I posted is the debug which shows the code it was trying to execute.

I wonder if this is a portable sql issue. In other places I have written my own update code, but here I wanted to use the synchronization function.

It's not giving a code error, or an argument type error, it's saying it can't find the record.
I know the primary_key_1 is good as I can see the value. What I can't see is what value it is passing in on the [RECID] argument.

Thanks
Tom

[RichCPT]

Re: detail update error

The 'where' clause indicates it is trying to handle a NULL value in the [payment] field. If your database is indeed setup such that the [payment] field allows null then ensure:
1) the property for the [payment] field in the list build has the "blank is NULL" checkmarked, and
2) the corresponding control for the detail section has the "NULL if blank" checkmarked.

Otherwise, if your database does not allow NULL in the [payment] field then uncheck those two properties.

[Tbrondolo]

Re: detail update error

The 'where' clause indicates it is trying to handle a NULL value in the [payment] field. If your database is indeed setup such that the [payment] field allows null then ensure:
1) the property for the [payment] field in the list build has the "blank is NULL" checkmarked, and
2) the corresponding control for the detail section has the "NULL if blank" checkmarked.

Otherwise, if your database does not allow NULL in the [payment] field then uncheck those two properties.

Hi Rich,

So I was just focused on the RECID matching but now I see as you pointed out that it is matching on the payment. But the statement being created makes no sense, even if I change null.

Below is the JSON that is created and the SQL update statement.

After the RECID the first part PAYMENT = :old_PAYMENT would only be true if there were no changes to the PAYMENT field. The second part PAYMENT IS NULL AND :old_PAYMENT IS NULL would only be true if both field were NULL. Neither is the case. It would seem strange that this was a bug, but I don't see how the generated statements could find the requested data.

[
    {
        "MRN": "XXXXXX336",
        "PAYMENT_DATE": null,
        "RECID": "91196",
        "CLIENT_ID": "CXXXX",
        "ACTIONDATE": "05/25/2015",
        "SESSION_TYPE": "Individual 45min",
        "SESSION_CODE": "90834",
        "SESSION_FEE": "258",
        "PAYMENT": "33",
        "PAYTYPE": "Check",
        "PAYTYPEREFNUMBER": null,
        "NOTES": null,
        "diagnosiscode": null,
        "__primaryKey": "91196",
        "__a5crc": "4294935899",
        "*key": 3,
        "*renderIndex": 3,
        "*value": "91196",
        "_isNewRow": false,
        "_oldData": {
            "MRN": "1000xxxx",
            "PAYMENT_DATE": null,
            "RECID": "91196",
            "CLIENT_ID": "CXXXX",
            "ACTIONDATE": "05/25/2015",
            "SESSION_TYPE": "Individual 45min",
            "SESSION_CODE": "90834",
            "SESSION_FEE": "258",
            "PAYMENT": "",
            "PAYTYPE": "Check",
            "PAYTYPEREFNUMBER": null,
            "NOTES": null,
            "diagnosiscode": null
        },
        "_isDirty": true,
        "*rowGUID": "DB63FA3C-F984-4347-9E2A-435F3E7B1511",
        "__displayStyle": "dirtyRow"
    }
]

UPDATE clienthistory SET [PAYMENT] = :new_PAYMENT WHERE ([RECID] = :primaryKey_1) AND ((PAYMENT = :old_PAYMENT OR PAYMENT IS NULL AND :old_PAYMENT IS NULL)) 


:new_payment (N) 33 
:old_payment (N) 0 
:primarykey_1 (N) 91196 

Database server reported an error:
The database could not find the data you requested

[RichCPT]

Re: detail update error

"PAYMENT = :old_PAYMENT " is checking to see if another user change the value of PAYMENT, since the time the data was first retrieved for the current user making the change.

Since NULL cannot be compared to NULL in SQL the second part, "PAYMENT IS NULL AND :old_PAYMENT IS NULL", is checking to see if the original PAYMENT was null in the database and that it was read as NULL by the current user. The second part is "OR"d with the first part. Hopefully, when Alpha setup the ":old_PAYMENT" sql argument it checked for the "" string (value in oldData) and passed in NULL for the argument value. I would imagine this all works in alpha correctly as long as the two checkboxes I mentioned ("blank is NULL" and "NULL if blank") are set properly. Otherwise, if it was not working then this board would be full of problems.

If you want to have Alpha completely omit comparing the "old" value to the database in the WHERE clause then uncheck the "check for write conflict" option for the payment field.

The "The database could not find the data you requested" error is one that I, too, was receiving the other day. It turned out that my ButtonList control was somehow changing my original value of zero or negative one to a blank string. It took a while to find because it only happened on the first record of the list and my ButtonList is only conditionally visible and the first record in my list never shows the ButtonList.