ResyncCommand and UniqueTable Properties Example Scenario

There are two SQL Server tables involved in these scenarios, Patients and Doctors, with a one-to-many relationship from Doctors to Patients. Both tables have an identity column as a primary key.

Patients table

Column Datatype Attributes
ID int identity, Primary Key
LastName varchar(30) not null
FirstName varchar(20) not null
PrimaryDoc int null Foreign Key references Doctors(DocID)

Doctors table

Column Datatype Attributes
DocID int identity, Primary Key
DocName varchar(30) not null Unique
DocPhone char(15) not null

The user wants to create an updatable datasheet view of data on patients and their doctors. The user wants the grid to show the following:

ID LastName FirstName PrimaryDoc DocName DocPhone
1 Jones Peter 10 Welby (206) 882-8080
2 Williams Jay 20 McCoy (206) 936-4321
3 Roberts George 20 McCoy (206) 936-4321
4 Crary Dave 10 Welby (206) 882-8080

The actual query underlying this form is logically:

SELECT P.ID, P.LastName, P.FirstName, P.PrimaryDoc, D.DocName, _
    D.DocPhone FROM Patients P JOIN Doctors D ON _
    ( P.PrimaryDoc = D.DocID)

User interface actions

  1. The user wants to change the assigned doctor for George Roberts to Dr. Gui (who is an existing doctor with no currently assigned patients). User types in Dr. GUI's DocID value of 30 in the PrimaryDoc column of the 3rd row. User now expects to see "Gui" under DocName and Dr. Gui's phone number in this row.

    Note   Normally the Microsoft Access user would want a Lookup column defined on PrimaryDoc so the user could selecta name from a dropdown list and wouldn't have to know the ID for Dr. GUI. In this case there wouldn't likely be a separate column for the DocName-- it would show up in the place of the Primary Doc column. But the user would still expect Dr. Gui's correct phone to appear after the update was made.

  2. The user clicks on * row, wants to add a new patient and assign him to Dr. Welby. Since the ID column is an Identity column for the Patients table, the UI should display something like "(AutoNumber)" in that first cell and not allow the user to enter a value. The user enters in a new patient first name and last name and an existing DocID value in the PrimaryDoc column. A new record is inserted in the Patients table, the ID column should be updated with the newly generated value and displayed on the grid, and the Doctor name and phone columns should be looked up from existing data.

  3. The user wants to delete Peter Jones. The user selects the row and selects Delete. Peter Jones' record is deleted from the Patients table, the row is removed from the cursor, but the cursor does not attempt to delete Dr. Welby's record from the Doctors table (this would generate a server error if DRI was enforced, but even if not enforced by the database, deleting Dr. Welby's record would invalidate the data in row 4, so it should not be attempted by the cursor.)