DB updates from a website

[Dale]

Hello,

I have a small DB with four tables.

The parent table (table1) has the foreign keys defined
for the other tables (table2,3,4).

When I try to update these tables froma web page (using
an INSERT statement), I get the error of:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] You cannot add
or change a record because a related record is required
in table 'table3'.
/calendar/coe_update.asp, line 455

My INSERT statements are setup so that the "child" tables
(table2,3,4) are updated before the "parent" (table1) is.

If I turn off Referenctial Integrity enforcement, I can
write to the DB all day but the PK numbers don't follow
to the "parent" table.

Any ideas?

Thanks in advance,
Dale

 

[Sal Rosario]

If you enforce referential integrity, you must have a record in the master
table before you can add related records in a child table. That is the whole
purpose of referential integrity. So you have to make a decision as to
whether you need it or not. Is there anything I'm not getting?

Sal
www.cedrostec.com

 

[Dale]

Right. When the child records are created and the ID
numbers are generated, the parent table will not
recognize the ID numbers from the child tables. These ID
numbers in the child tables are the foreign keys from the
parent table.

Here is my layout:

tblMonth
--------
MonthID (PK)
Month
Day
Year
EventID (FK)
ContactID (FK)
LocID (FK)
***********************

tblEvent
--------
EventID (PK)
EventDesc
Time
Status
***********************

tblContact
----------
ContactID (PK)
Name
Phone
Email
***********************

tblLoca
--------
LocID (PK)
Location
***********************

So, when I try to insert a record from the web page, I
have the INSERT statements update "tblLoca", "tblContact"
and "tblEvent" before the "tblMonth" table.

When those records are added to the prospective tables,
it works just fine until I get to the INSERT
with "tblMonth", then it fails saying it needs the ID
numbers from the other child tables (as in the error
message refered to earlier).

After getting that error I can open the DB and look at
what I just entered into it form the web page. The child
tables work just fine but the parent table (tblMonth)
doesn't have a record anywhere in it.

I hope this explains it better. Now, any ideas?

Thanks,
Dale

-----Original Message-----
If you enforce referential integrity, you must have a record in the master
table before you can add related records in a child table. That is the whole
purpose of referential integrity. So you have to make a decision as to
whether you need it or not. Is there anything I'm not getting?

Sal
www.cedrostec.com

Hello,

I have a small DB with four tables.

The parent table (table1) has the foreign keys defined
for the other tables (table2,3,4).

When I try to update these tables froma web page (using
an INSERT statement), I get the error of:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] You cannot add
or change a record because a related record is required
in table 'table3'.
/calendar/coe_update.asp, line 455

My INSERT statements are setup so that the "child" tables
(table2,3,4) are updated before the "parent" (table1) is.

If I turn off Referenctial Integrity enforcement, I can
write to the DB all day but the PK numbers don't follow
to the "parent" table.

Any ideas?

Thanks in advance,
Dale

.