Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

DataAdapter Update and unchanging PKs

I'm working with two tables in Access (i.e. Jet): Courses and CourseOfferings (which holds semester/year/etc. when a course is offered).

Courses has KeyCourse, a GUID, as the primary key, and KeyCourse is also a foreign key in CourseOfferings.  Relations are enforced by Jet and cascade delete is turned on, but cascade update is off since the GUID PKs shouldn't need to be changed.

But when I autogenerate a DataAdapter with all the fields from Courses, the CommandText in the UpdateCommand is set to update KeyCourse as well as the other fields in Courses.  Since cascade updates is off, this naturally causes an exception for any KeyCourse that's also a FK in CourseOfferings.  And I can't seem to come up with a good way around it.

I've tried:
1)  Enabling cascade updates in Access.  This works but it shouldn't be necessary; for an Update I shouldn't have to update the PK.
2)  Reconfiguring the UpdateCommand and removing the update for KeyCourse.  This doesn't work because it doesn't reconfigure the parameters properly, and then it tells me I have a missing parameter when I try to update.
3)  Regenerating the whole DataAdapter with optimistic updates off, and then reconfiguring the UpdateCommand not to update KeyCourse.  This works...but then why doesn't it work with optimistic updates turned _on_?

I feel that I must be missing something.  Any ideas?

Tuesday, November 30, 2004

I personally don't like using the Data Adapter to do updates. I always do ExecuteNonQuery and pass the exact update sql I want. Works every time.

SongSing Writer
Thursday, December 9, 2004

*  Recent Topics

*  Fog Creek Home