Cannot update 1 record because the conflict of effectiveness of the rules occurs.

[youyiyang]

Hi all!

I use ASP webpage to update records in background access table and get problems.

The SQL statement is just as common as others.

UPDATE table1 SET contact_status='new', contact_date='2009-11-9', company='someone', quote='0' WHERE ID=763

I have omitted certain SQL statements since I use Chinese character in it.

The problem is nothing happened. Then I use above SQL statement to execute just in the view of the background access database and get the following error message:

Cannot update 1 record because the conflict of effectiveness of the rules occurs.

Since the design of the table1 is very simple: all the fields are Text type except contact_date is Date_Time and quota is Currency and ID is Autonumber key.

And I also checked all the field types and the effectiveness of the rules is blank.

I just don't know the reason why this error occurs.

By the way, the table1 once was used to replicate to other tables and I have stopped replication and re-generated the ID since the old one is random number.

Is this the reason?

Any clues would be very appreciated!

 

[youyiyang]

I have solved the problem.
The solution is to make the rule of allowing blank string to YES.
As there are many blank strings which are needed to be updated, so it violates the rule of not allowing blank string.
Actually the problem was solved several days ago after I posted both in this site and a Chinese BBS. It was quickly responded since I am in day time in China while here is night time.

 

[The_Doc_Man]

Glad you found it.

When any of the underlying tables in a query have validation criteria AND your query doesn't provide all of the possible values AND you don't have default values defined for all fields (in the underlying table) that have "Required" = Yes, you will find that you cannot do the update due to validation constraints. However, your message (probably because it was translated from Chinese) was a bit confusing. Which might be why you had so few answers. (That is not intended to be harsh, just stating a fact.)

 

[youyiyang]

Thank you The Doc Man.
Yes, the error message was translated from Chinese that made confusing. Hehe.

 

[dfenton]

Setting ALLOW ZLS to YES is problematic in that you can no longer just check for Is Null to find empty fields -- you have to check for Is Null or ="". It would be better to make sure that your appends and updates convert any ZLS to a Null.

 

[youyiyang]

Thank you dfenton. In doing so really may generate new problems. The design of table fields needs to be considered thoroughly.

 

[The_Doc_Man]

Where a query calls out a subset of the fields in a table (or JOIN of two tables), a valid consideration is whether your use of that query defines all of the required fields. This is why there are multiple "types" of fields - those that have default values, those that can be empty or null, and those that have neither attribute. I believe that in order to support updates, your queries must someone take care of all fields that lack defaults or allow zero-length strings. Even if the function using that query doesn't normally touch one of the fields. The obvious problem, of course, is that it isn't the query that causes the problem, but rather it is the constraints on the underlying table.

Which means, as you pointed out, that table design must be considered in many ways, not just as raw data storage, but also as a way to facilitate whatever your business process is trying to do. I fully agree with you - there is no substitute for careful analysis.