473,695 Members | 1,941 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert into validation rule violation

I have a routine to copy data to new versions of my app via insert into
sql statements. Unfortunately, due to evolution of my app, sometimes
the new version has more restrictive editing than an older version that
I am updating. Thus I get this message. It tells me only how many
records have errors, not which errors or which records.

Anyone have a nice solution to identifying the specific records involved?

Maybe even the specific field?

One way I have come up with so far is to do a duplicate insert into a
version of the table with no validation rules. Then do a query joining
the two, UNRESTRICTED ----> RESTRICTED and see what records are
missing.

Another would be to write a query with the equivalent selection as the
reverse of the validation criteria, such that any record that did not
meet the validation is selected. The down side here is the query would
have to be maintained.

I am hopeful there is a more elegant way?

Bob
Jan 27 '06 #1
3 6332
I don't quite understand about updating multiple versions... I'd say the
solution may be to organize and clean up so that you don't have quite so
complex an environment. Other than that, if the same updates are applied to
the data for all the versions, apply them to the most restrictive first.

What is it that you do if it fails the validation rules? Do you change the
data, change the validation rules, or ???

Larry Linson
Microsoft Access MVP
"Bob Alston" <tu************ ****@cox.net> wrote in message
news:C0uCf.1405 1$JT.12037@fed1 read06...
I have a routine to copy data to new versions of my app via insert into sql
statements. Unfortunately, due to evolution of my app, sometimes the new
version has more restrictive editing than an older version that I am
updating. Thus I get this message. It tells me only how many records have
errors, not which errors or which records.

Anyone have a nice solution to identifying the specific records involved?

Maybe even the specific field?

One way I have come up with so far is to do a duplicate insert into a
version of the table with no validation rules. Then do a query joining
the two, UNRESTRICTED ----> RESTRICTED and see what records are
missing.

Another would be to write a query with the equivalent selection as the
reverse of the validation criteria, such that any record that did not meet
the validation is selected. The down side here is the query would have to
be maintained.

I am hopeful there is a more elegant way?

Bob

Jan 27 '06 #2
Larry Linson wrote:
I don't quite understand about updating multiple versions... I'd say the
solution may be to organize and clean up so that you don't have quite so
complex an environment. Other than that, if the same updates are applied to
the data for all the versions, apply them to the most restrictive first.

What is it that you do if it fails the validation rules? Do you change the
data, change the validation rules, or ???

Larry Linson
Microsoft Access MVP
"Bob Alston" <tu************ ****@cox.net> wrote in message
news:C0uCf.1405 1$JT.12037@fed1 read06...
I have a routine to copy data to new versions of my app via insert into sql
statements. Unfortunately, due to evolution of my app, sometimes the new
version has more restrictive editing than an older version that I am
updating. Thus I get this message. It tells me only how many records have
errors, not which errors or which records.

Anyone have a nice solution to identifying the specific records involved?

Maybe even the specific field?

One way I have come up with so far is to do a duplicate insert into a
version of the table with no validation rules. Then do a query joining
the two, UNRESTRICTED ----> RESTRICTED and see what records are
missing.

Another would be to write a query with the equivalent selection as the
reverse of the validation criteria, such that any record that did not meet
the validation is selected. The down side here is the query would have to
be maintained.

I am hopeful there is a more elegant way?

Bob


The issue is that I have software at nonprofit clients. It has been
installed for just over a year now. Some of the earliest versions were
missing some appropriate validation rules since added.

I had previously built a process of comparing the table structures and
issuing VBA table edit commands. However, when addressing new tables,
new fields, deleted fields, etc. that got complex. Once I thought I had
it all working, tried it at a client and had problems. so before
returning to what I found to be complex logic I took a new approach.

Yes, I saw the response of COMPARE'EM, a free utility to do this, but
this was after I was well along on my new process.
http://home.gci.net/~mike-noel/CompareEM.htm
I am using a new process to upgrade individual client versions. I take
the complete new version, delete all records from the demo data and then
INSERT INTO the new version tables from the clients existing database.
MY logic includes deleting table content in the correct sequence and
loading table content in the correct sequence.

A nice advantage of this is that I KNOW that each client now has exactly
the same software except for data. Relations, indices, etc are all
exactly the way I want it.

The issue as I see it is I get a generic error message that does not
even tell me which table is the problem. I currently can determine
which table by responding to the generic error message's option NO and
selecting cancel and debug. I can then print the table name out in the
immediate window. Then I resume the VBA and this time select YES and it
continues on - minus the problem records. That way in one pass I can
tell which tables have issues and how many issues per table. But it
does nothing to tell me which rows/records or which fields.

After I built this (generating SQL INSERT INTO statements) I realized I
should have handled the read/write via vba code; much slower but at
least I could trap the error and tell which record was causing the error.

So I am looking for a better way to resolve these issues.

Bob
Jan 27 '06 #3
Once you have appended the data to the more restrictive table, create a
query joining the unrestricted table with the restrictive one, bringing down
all the fields from the unrestricted and only the unique id from the
restricted, using an outer join on the unique ids and a criteria of Null on
the id from the restricted table.

You should then be able to convert that to a Make Table query, make a table
just of the rejected records, edit them, and then append those values.
Repeat until the comparison query does not return values.

That's about as simple as I see.

Larry Linson
Microsoft Access MVP

"Bob Alston" <tu************ ****@cox.net> wrote in message
news:wnwCf.1406 5$JT.2978@fed1r ead06...
Larry Linson wrote:
I don't quite understand about updating multiple versions... I'd say the
solution may be to organize and clean up so that you don't have quite so
complex an environment. Other than that, if the same updates are applied
to the data for all the versions, apply them to the most restrictive
first.

What is it that you do if it fails the validation rules? Do you change
the data, change the validation rules, or ???

Larry Linson
Microsoft Access MVP
"Bob Alston" <tu************ ****@cox.net> wrote in message
news:C0uCf.1405 1$JT.12037@fed1 read06...
I have a routine to copy data to new versions of my app via insert into
sql statements. Unfortunately, due to evolution of my app, sometimes the
new version has more restrictive editing than an older version that I am
updating. Thus I get this message. It tells me only how many records
have errors, not which errors or which records.

Anyone have a nice solution to identifying the specific records involved?

Maybe even the specific field?

One way I have come up with so far is to do a duplicate insert into a
version of the table with no validation rules. Then do a query joining
the two, UNRESTRICTED ----> RESTRICTED and see what records are
missing.

Another would be to write a query with the equivalent selection as the
reverse of the validation criteria, such that any record that did not
meet the validation is selected. The down side here is the query would
have to be maintained.

I am hopeful there is a more elegant way?

Bob


The issue is that I have software at nonprofit clients. It has been
installed for just over a year now. Some of the earliest versions were
missing some appropriate validation rules since added.

I had previously built a process of comparing the table structures and
issuing VBA table edit commands. However, when addressing new tables, new
fields, deleted fields, etc. that got complex. Once I thought I had it
all working, tried it at a client and had problems. so before returning
to what I found to be complex logic I took a new approach.

Yes, I saw the response of COMPARE'EM, a free utility to do this, but this
was after I was well along on my new process.
http://home.gci.net/~mike-noel/CompareEM.htm
I am using a new process to upgrade individual client versions. I take
the complete new version, delete all records from the demo data and then
INSERT INTO the new version tables from the clients existing database. MY
logic includes deleting table content in the correct sequence and loading
table content in the correct sequence.

A nice advantage of this is that I KNOW that each client now has exactly
the same software except for data. Relations, indices, etc are all
exactly the way I want it.

The issue as I see it is I get a generic error message that does not even
tell me which table is the problem. I currently can determine which table
by responding to the generic error message's option NO and selecting
cancel and debug. I can then print the table name out in the immediate
window. Then I resume the VBA and this time select YES and it continues
on - minus the problem records. That way in one pass I can tell which
tables have issues and how many issues per table. But it does nothing to
tell me which rows/records or which fields.

After I built this (generating SQL INSERT INTO statements) I realized I
should have handled the read/write via vba code; much slower but at least
I could trap the error and tell which record was causing the error.

So I am looking for a better way to resolve these issues.

Bob

Jan 27 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5182
by: Sudip Chakraborty | last post by:
Is there a way to see constraint validation errors while loading xml into a DataSet ? I'm interested in the line number in the xml file which is causing the error. I've enclosed the relevant stack trace below. at System.Data.DataSet.FailedEnableConstraints() at System.Data.DataSet.EnableConstraints() at System.Data.DataSet.set_EnforceConstraints(Boolean value)
2
2685
by: Joey P | last post by:
Hi all, I am doing a project for university whereby i have to implement a simple database related to a frozen foods company. I am having some trouble though creating a validation rule for one of my fields. I have a table called "Product" and two of the fields included in this table are "Cost Price" and "Retail Price". I need to create a validation rule so that the Cost Price is always less than the Retail Price. I have tried...
5
3192
by: Jeremy | last post by:
I have a vb .net 2003 app that uses access 2k as a backend. The main table has some validation rules (legacy stuff brought forward inadvertantly, and now we're stuck with 'em). The problem is, I do a dataadaptor.update, and an exception is thrown. I would prefer to validate before the update & tell the user to fix the data. dataset.hasErrors does not detect the validation violation. When the exception is thrown I can of course catch...
5
3821
by: Stephen D Cook | last post by:
I'm trying to clear a form after the user clicks the Insert button so the textboxes are empty and the checkboxes are unchecked. When I try to put the code in the Click part of the Insert button, I get an error telling me the Serial Number text box is empty. If I put the code in the MouseUp part, it doesn't do anything. please direct me to what I am doing wrong.
8
15965
by: petebeatty | last post by:
I have created a SQL string the properly inserts a record in the table. However, the insert does not occur at the end of the table. Instead it inserts a record after the last record that I viewed. This would be OK, except it assigns a autonumber to be one greater than the last viewed record. This causes a duplicate autonumber. I know I can change the autonumber index (Primary Key) to not allow duplicates. How can I force the insert...
1
3758
by: billa856 | last post by:
Hi, I am trying to insert Null value in column(ShipDate) in my table.That column(ShipDate)'s type id date/time and format is short date. I am using "" to insert Null in that column(ShipDate) but it shows warning that customer can't append all the records in the append query. Customer set 1 field(s) to Null due to a type conersion failure,and it didn't add 0 record(s) to the table due to key violation, 0 record(s) due to lock...
7
5968
by: sharsy | last post by:
Hi guys, I would like to setup a validation rule for a database in microsoft access that restricts data entry so that a certain field can only be filled in if another field has a specific answer (that is selected via a drop down list). Example Field1 - options are "In" or "Out" Field2 - options are "Join" or "Not Joining"
1
2080
by: MLH | last post by:
Anyone remember if A97 append query failure would ever report data breaking validation rule when such was not the case. I have an old SQL statement - several years old now. I've encountered a case in which the append fails and the reported error is validation rule. The table being appended to (tblClusters) has only 1 field with a validation rule: , with Byte Field Size property setting. The validation rule is >0. The field's default...
18
6222
by: ChipR | last post by:
I have a text box with a validation rule and validation text. When entering a new record, if I put in invalid text, the validation text is displayed in a message box, but after clicking OK, another message box is displayed with: ----------- myApplication (i bubble icon) The value violates the validation rule for the field or record. For example, you might have changed a validation rule without verifying whether the existing data matches...
0
8625
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9113
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8977
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8822
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5838
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4339
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4577
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2997
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2269
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.