Reference a deleted recordset

Question

Is it possible to reference a recordset (that is, a single feature) after deleting it? I have features that I have linked via matching attribute fields, and when the “parent” feature gets updated, I have code that auto-updates the linked “child” feature. Now I’m trying to figure out if there is a way to delete the “child” feature if the user deletes the “parent” feature by calling a script using the OnDelete event of the parent feature. But I am finding that once the parent feature is deleted, I no longer know how to reference it.

Here is what I am trying (which is not working). The Console.Print yields “False”.

Sub ManageFeatureDeleted()

'++ get the modified record

Dim pRS, lBookmark

Set pRS = Layer.Records

lBookmark = ThisEvent.Bookmark

pRS.Bookmark = lBookmark

Console.Print pRS.IsDeleted

Dim pBufferLyr

Set pBufferLyr = GetLayerByName(BUFFER_LAYER)

'++ find associated pt buffer feature based on common attribute

Dim iCountB

Dim iCountBNotUpdated

iCountB = 0

iCountBNotUpdated = 0

If (Not pBufferLyr Is Nothing) Then

Dim pBufferRS

Set pBufferRS = pBufferLyr.Records

pBufferRS.MoveFirst()

Do While Not pBufferRS.EOF

If (IsEmptyOrBlank(pBufferRS.Fields("LINK").Value) Or Not(pBufferRS.Fields("LINK").Value=pRS.Fields("LINK").Value)) Then

iCountBNotUpdated = iCountBNotUpdated + 1

Else

'Make the buffer feature layer editable

Map.Layers(BUFFER_LAYER).Editable = True

'Delete the buffer feature

pBufferRS.Delete

'Update the RecordSet

pBufferRS.Update

'Refresh the Map

Application.Map.Refresh(True)

'Make the buffer feature layer not editable

Map.Layers(BUFFER_LAYER).Editable = False

'Make the Poly_Spartina_16 layer editable

Map.Layers("Poly_Spartina_16").Editable = False

iCountB = iCountB + 1

End If

pBufferRS.MoveNext()

Loop

End If

End Sub

IngridHogle · Answer

Thank you so much for this information. Our data is in an axf, and we are running ArcPad 10.2.2. We do not have a relationship class. Instead, I just have unique attributes "linking" the features in a 1:1 relationship. It looks like, from your explanation, that perhaps I need to create a relationship class. Is this the case?

All of the settings in INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, including the DELETE_RULE, are set to their default, nvarchar(128). Double clicking INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS in ArcPad Studio to open this in the SQL Command window, I see that the DELETE_RULE is set to CASCADE for the two rows with

CONSTRAINT_NAME: AXF_LAYERS and AXF_TABLE_CONSTRAINTS_CONSTRAINT_TYPE and

UNIQUE_CONSTRAINT_NAME: PK_GEOMETRY_COLUMNS and PK_AXF_RANGE_CONSTRAINTS.

The DELETE_RULE is set to NO ACTION for all remaining 430 rows.

Is this what you would expect to see? I am not very familiar with SQL, so although I follow your explanation above, I am not sure how to use this information. Given the information I've provided, do you think that I need to try to alter any DELETE_RULE settings within the axf, and/or create a relationship class (rather than using my ad hoc method of a common, unique attribute), in order to accomplish what I am after...which is that if a point feature is deleted, it's pre-existing, linked point buffer feature will also be deleted.

StephenQuan1 · Answer

Hi Ingrid,

Firstly, is it a Shapefile or AXF? The two can be different.

In the case of a Shapefile, there's a delete flag on deleted records, so, technically, we can still locate the records using MoveFirst, MoveNext, or getting and setting the Bookmark. All the attributes are still accessible.

In the case of an AXF, we would need to consult the DELETE RULE from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS for your specific case. In general terms, it can be of one of 3 values "NO ACTION", "CASCADE" or "SET NULL". In the case of ArcPad, it really should be set to "NO ACTION".

I'll attempt to explain why.

Consider the following POLES -> LIGHT relationship class in the pseudo SQL fragment:

CREATE TABLE POLES (PID INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE LIGHTS (LID INTEGER NOT NULL PRIMARY KEY, PID INTEGER NULL);

-- ALTER TABLE LIGHTS ADD CONSTRAINT FK_LIGHTS FOREIGN KEY (PID) REFERENCES POLES (PID) ON DELETE CASCADE;

-- ALTER TABLE LIGHTS ADD CONSTRAINT FK_LIGHTS FOREIGN KEY (PID) REFERENCES POLES (PID) ON DELETE SET NULL;

ALTER TABLE LIGHTS ADD CONSTRAINT FK_LIGHTS FOREIGN KEY (PID) REFERENCES POLES (PID) ON DELETE NO ACTION;

INSERT INTO POLES VALUES (1);

INSERT INTO LIGHTS VALUES (101, 1);

DELETE FROM POLES WHERE PID = 1;

SELECT * FROM LIGHTS;

When ACTION = "DELETE CASCADE", SQL CE will automatically delete the related LIGHT record when the parent POLE record is deleted. However, this is bad, since ArcPad's AXF_DELETED_ROWS is not being updated to reflect that the LIGHT record is deleted, and, that information will be omitted from synchronization. So, eventhough it looks correct in the AXF, when you check in the changes, the GeoDatabase will have dangling light records.

When ACTION = "SET NULL", SQL CE will automatically edit the related LIGHT record changing the PID to NULL. This gives your script the ability to locate the dangeling LIGHT records and to post process them. But, again, such changes will be omitted by synchronization.

When ACTION = "NO ACTION", SQL CE will block you from deleting the parent POLE record since there's a related LIGHT record. i.e. the only way to delete the record, is to delete the related LIGHT records first before deleting the POLE record. This is the correct approach, and, will ensure that ArcPad's AXF_DELETED_ROWS will have complete history for synchronization.

Stephen