Changing the value of where clause field

[azaleas]

Why I can't change the value of the column used as a where clause?

Example: I'm searching the table via where clause USERNAME=&&USERNAME;

When I use resource.RESOURCE_NAME.update;

All fields in the row get updated, except for the one which was used in where clause.

Any ideas? How to change the value of that field in DB?

[Jon]

All columns listed in the Resource Fields section at the bottom of the resource will be included in the update SQL if the Persistent checkbox is checked and the Read only checkbox is unchecked for the resource field. If you check the debug option (top right-hand corner) you will see the generated SQL in the execution log. If you don't like this generated SQL, you can always use the Native SQL tab.

[azaleas]

All columns listed in the Resource Fields section at the bottom of the resource will be included in the update SQL if the Persistent checkbox is checked and the Read only checkbox is unchecked for the resource field. If you check the debug option (top right-hand corner) you will see the generated SQL in the execution log. If you don't like this generated SQL, you can always use the Native SQL tab.

I've checked the persistent and read only conditions. It seems ok but I can't update the value of one column.

[Jon]

What SQL do you see in the log with the debug option turned on? It's a bit unusual to update a column value which is also in the where clause. What do you expect the database to do - find the record with the original value, then change it to the new value?

[azaleas]

What SQL do you see in the log with the debug option turned on? It's a bit unusual to update a column value which is also in the where clause. What do you expect the database to do - find the record with the original value, then change it to the new value?

I have a row with Username, Age and Tax number. In where clause I have:
TAXNUMBER=&&TAXNUMBER. I search the db to find a row by using TAXNUMBER. Once the search finds the row all the column values are copied to the profile page. There, I want to update the values if necessary. I can update Username and Age..But on Tax number, Even though the update is running without any errors, the value doesn't change. I want to be able to change the Tax number as well.

[Jon]

I suspect that the generated SQL is:

Code: Select all

update xxx where TAXNUMBER=NewValue set TAXNUMBER=NewValue...

which won't update any records.

To fix this you will need to add an additional field to the Database Resource and the form, something like TAXNUMBER_ORIGINAL, and use this in the lookup i.e. the where clause will be TAXNUMBER=&&TAXNUMBER_ORIGINAL. Uncheck the persistent flag for this new field in the resource. Leave the existing TAXNUMBER field as it is. You then need to populate TAXNUMBER_ORIGINAL before you do the lookup.

Then the generated SQL will be:

Code: Select all

update xxx where TAXNUMBER=OldValue set TAXNUMBER=NewValue...

which is what you want.

I mention again that you can see the generated SQL by clicking the debug checkbox in the Database Resource, running the form and then viewing the SQL in the log (View > Execution Log). This is the easiest way to debug these problems.

[azaleas]

Thanks Jon.

Appreciate your help:)