error in Delete statement

[shaminda]

I have two tables that have one to many relationships (say TableA - 1 and TableB - many). Then I run a join query to view the data(recordset - rst) . When I try to delete a record from this query using rst.Delete statement it gives me the following error,

-2147900, [Microsoft][ODBC SQL Server Driver][SQL Server]DELETE statement conflicted with COLUMN REFERENCE constraint ‘FK_TableB_TableA’. The conflict occurred in database ‘dbBooks’, table ‘TableB’, column ‘UserID’.

Does anybody know how to fix this problem? I tried refresh and requery after the delete statement but didn’t work. It deletes the record from TableB but I could go beyond that becouse of the error.

[TH1]

The reason you are getting this problem is that the delete statement is causing referential integrity to fail.Basically you are trying to delete a parent record which has children. To fix this delete the child records before you delete the parent record

[Cakkie]

What's really happening is that you are trying to delete a record from tableA instead of tableB. Say you have this situation:
tableA
UserID UserName
1 Cakkie
2 Shaminda
3 Chris Eastwood
4 Homer Simpson

tableB
UserID Quote
1 It's me, Cakkie
1 Pigs can't look up
2 I have a problem
2 Still having this problem
2 I'm calling Houston
3 vbcodelibrary sucks
3 I'm getting married 1st of july
4 D'oh!


So we have our two tables, one with the user id's, the other with famous quotes. When we join them, it looks like this
Cakkie It's me, Cakkie
Cakkie Pigs can't look up
...
Chris Eastwood vbcodelibrary sucks
...


Say you want to delete the record from Chris Eastwood, because it's a lie, you will actually be deleting both the records in tableA and tableB, the one in tableB is fine, but the one in tableA isn't because you still have another record in tableB refferencing Chris, namely 'I'm getting married...'. This is why you are getting the error. If you want to delete, you will need to delete only from tableB, not from the result you get from the JOIN.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[shaminda]

Say TableA has following records
1123 John Smith ACC
1129 Cris May Eng

And TableB has
Gold 2000
Silver 250
Iron 34
Mold 22
Rum 67
Gold 2000

result of my join query will be
1123 John Smith ACC Gold 2000
1123 John Smith ACC Silver 250
1123 John Smith ACC Iron 34
1129 Cris May Eng Mold 22
1129 Cris May Eng Rum 67
1129 Cris May Eng Gold 2000
How can I delete the last record. Without the join query the user will see two records that has 'Gold 2000' how does the user know which one to delete?