Deleting Disconnected Shaped Recordset Problem 
Author Message
 Deleting Disconnected Shaped Recordset Problem

I have a disconnected shaped recordset from a business
object with parent, child, grandchild, and great-
grandchild hierarchical recordsets.  If any record is
deleted the client program deletes all the sub-children
records to the "end" of the hierarchy.  So the sub-child
recordset, and all hierarchical levels below, will
contain a zero record count.

When the shaped recordset is passed back to the business
object the save routine looks for chapter fields and
recursively calls itself to batch update the children
recordsets.  Now when the save routine comes across one
of the "empty" child recordsets the save routine has a
problem.  It cannot assign a recordset to the chapter
field because there are no records (this causes a "BOF
or EOF" error) so any hierarchical levels below do not
get updated.  To avoid the error the routine can stop
the recursion when finding an empty recordset, but then
any hierarchical levels below are obviously not updated.

Example: If I have several parent records and delete
one of them, then the child, grandchild, and great-
grandchild hierarchical recordsets have all their
records deleted.  After being processed through the
save routine, the parent and child tables reflect the
delete changes but the grandchild and great-grandchild
tables do not.

How do I get the sub-child recordsets to update their
tables?  Am I missing something?

Code was left out to make the posting smaller.  Code
snippets can be supplied on request.

Thanks for any help.

Randy



Tue, 04 May 2004 05:24:18 GMT
 Deleting Disconnected Shaped Recordset Problem

Hi Randy,

Could you post or email code? I think that problem in
updating part of code. It shold work, but without code it
will be more difficult to find out problem.

Thanks,

Val

Quote:
>-----Original Message-----
>I have a disconnected shaped recordset from a business
>object with parent, child, grandchild, and great-
>grandchild hierarchical recordsets.  If any record is
>deleted the client program deletes all the sub-children
>records to the "end" of the hierarchy.  So the sub-child
>recordset, and all hierarchical levels below, will
>contain a zero record count.

>When the shaped recordset is passed back to the business
>object the save routine looks for chapter fields and
>recursively calls itself to batch update the children
>recordsets.  Now when the save routine comes across one
>of the "empty" child recordsets the save routine has a
>problem.  It cannot assign a recordset to the chapter
>field because there are no records (this causes a "BOF
>or EOF" error) so any hierarchical levels below do not
>get updated.  To avoid the error the routine can stop
>the recursion when finding an empty recordset, but then
>any hierarchical levels below are obviously not updated.

>Example: If I have several parent records and delete
>one of them, then the child, grandchild, and great-
>grandchild hierarchical recordsets have all their
>records deleted.  After being processed through the
>save routine, the parent and child tables reflect the
>delete changes but the grandchild and great-grandchild
>tables do not.

>How do I get the sub-child recordsets to update their
>tables?  Am I missing something?

>Code was left out to make the posting smaller.  Code
>snippets can be supplied on request.

>Thanks for any help.

>Randy

>.



Tue, 04 May 2004 07:02:27 GMT
 Deleting Disconnected Shaped Recordset Problem
Val:

Here's the code and thanks for the help.

Client delete record(s) code:

************************************************************************
Private Sub DeleteRecord(rsRecSet as ADODB.Recordset, lngRecNum As Long, _
                Optional blnDeleteAll As Boolean = False)

    With rsRecSet
        If .BOF And .EOF Then
            'Continue, there are no records to delete
        Else
            If blnDeleteAll Then
                'Delete all the records in the recordset
                .MoveFirst
                Do Until .EOF
                    Call DeleteChildRecords(rsRecSet)
                    .Delete
                    .MoveNext
                Loop
            Else
                'Delete only the specific record
                .AbsolutePosition = lngRecNum
                Call DeleteChildRecords(rsRecSet)
                .Delete
            End If
        End If
    End With

End Sub

Private Sub DeleteChildRecords(rsRecSet As ADODB.Recordset)
    'Called by DeleteRecord
    Dim rsChild As ADODB.Recordset
    Dim fld As ADODB.Field

    'Check the recordset Fields collection for child recordsets (chapters)
    For Each fld In rsRecSet.Fields
        If fld.Type = adChapter Then
            Set rsChild = fld.Value
            'Delete all the records in the Child record set
            With rsChild
                If .BOF And .EOF Then
                    'Continue, there are no records to delete
                Else
                    .MoveFirst
                    Do Until .EOF
                        'Delete any Grand Children
                        Call DeleteChildRecords(rsChild)
                        .Delete
                        .MoveNext
                    Loop
                End If
            End With
            Set rsChild = Nothing
        End If
    Next fld

End Sub
************************************************************************

Business Object (DLL) Save code:

************************************************************************
Public Function SaveRecSet(ByVal objRecSet As ADODB.Recordset, _
                    Optional ByVal blnParentConn As Boolean = True) As
Variant
    ' Save changes from the recordset
    Dim rsChild As ADODB.Recordset
    Dim i As Long

    With objRecSet
        .ActiveConnection = mobjConn
        If .State = adStateOpen Then
            On Error Resume Next
            .UpdateBatch adAffectAllChapters
    '*** IF STATEMENT INSERTED TO AVOID ERROR BELOW ***
            If .BOF And .EOF Then
                'Continue there are no records to process
            Else
                'Update each of the child recordsets ... call
                'recursively to handle all of the children
                For i = 0 To .Fields.Count - 1
                    If (.Fields(i).Type = adChapter) Then
                        Set rsChild = New ADODB.Recordset
    '*** NEXT LINE ERRORS WHEN THE RECORDSET IS EMPTY ***
                        Set rsChild = .Fields(i).Value
                        SaveRecSet rsChild, False
                        Set rsChild = Nothing
                    End If
                Next i
                If .State = adStateOpen Then
                    Do    'Cycle through the recordset until there are no
conflicts
                        .Filter = adFilterConflictingRecords
                        If .EOF And .BOF Then    'No conflicting records
                            Exit Do
                        Else    'There are conflicting records
                            .Resync adAffectGroup, adResyncUnderlyingValues
                            .UpdateBatch adAffectAllChapters
                        End If
                    Loop
                End If
            End If
        End If
    End With

    If blnParentConn = True Then
        Set objRecSet.ActiveConnection = Nothing
    End If

    SaveRecSet = Err.Description

End Function
************************************************************************


Quote:
> Hi Randy,

> Could you post or email code? I think that problem in
> updating part of code. It shold work, but without code it
> will be more difficult to find out problem.

> Thanks,

> Val
> >-----Original Message-----
> >I have a disconnected shaped recordset from a business
> >object with parent, child, grandchild, and great-
> >grandchild hierarchical recordsets.  If any record is
> >deleted the client program deletes all the sub-children
> >records to the "end" of the hierarchy.  So the sub-child
> >recordset, and all hierarchical levels below, will
> >contain a zero record count.

> >When the shaped recordset is passed back to the business
> >object the save routine looks for chapter fields and
> >recursively calls itself to batch update the children
> >recordsets.  Now when the save routine comes across one
> >of the "empty" child recordsets the save routine has a
> >problem.  It cannot assign a recordset to the chapter
> >field because there are no records (this causes a "BOF
> >or EOF" error) so any hierarchical levels below do not
> >get updated.  To avoid the error the routine can stop
> >the recursion when finding an empty recordset, but then
> >any hierarchical levels below are obviously not updated.

> >Example: If I have several parent records and delete
> >one of them, then the child, grandchild, and great-
> >grandchild hierarchical recordsets have all their
> >records deleted.  After being processed through the
> >save routine, the parent and child tables reflect the
> >delete changes but the grandchild and great-grandchild
> >tables do not.

> >How do I get the sub-child recordsets to update their
> >tables?  Am I missing something?

> >Code was left out to make the posting smaller.  Code
> >snippets can be supplied on request.

> >Thanks for any help.

> >Randy

> >.



Wed, 05 May 2004 00:04:42 GMT
 Deleting Disconnected Shaped Recordset Problem
Hi,

I have got your code. Give me some time.

Val

Quote:
>-----Original Message-----
>Val:

>Here's the code and thanks for the help.

>Client delete record(s) code:

>**********************************************************
**************
>Private Sub DeleteRecord(rsRecSet as ADODB.Recordset,

lngRecNum As Long, _
Quote:
>                Optional blnDeleteAll As Boolean = False)

>    With rsRecSet
>        If .BOF And .EOF Then
>            'Continue, there are no records to delete
>        Else
>            If blnDeleteAll Then
>                'Delete all the records in the recordset
>                .MoveFirst
>                Do Until .EOF
>                    Call DeleteChildRecords(rsRecSet)
>                    .Delete
>                    .MoveNext
>                Loop
>            Else
>                'Delete only the specific record
>                .AbsolutePosition = lngRecNum
>                Call DeleteChildRecords(rsRecSet)
>                .Delete
>            End If
>        End If
>    End With

>End Sub

>Private Sub DeleteChildRecords(rsRecSet As
ADODB.Recordset)
>    'Called by DeleteRecord
>    Dim rsChild As ADODB.Recordset
>    Dim fld As ADODB.Field

>    'Check the recordset Fields collection for child

recordsets (chapters)

- Show quoted text -

Quote:
>    For Each fld In rsRecSet.Fields
>        If fld.Type = adChapter Then
>            Set rsChild = fld.Value
>            'Delete all the records in the Child record
set
>            With rsChild
>                If .BOF And .EOF Then
>                    'Continue, there are no records to
delete
>                Else
>                    .MoveFirst
>                    Do Until .EOF
>                        'Delete any Grand Children
>                        Call DeleteChildRecords(rsChild)
>                        .Delete
>                        .MoveNext
>                    Loop
>                End If
>            End With
>            Set rsChild = Nothing
>        End If
>    Next fld

>End Sub
>**********************************************************
**************

>Business Object (DLL) Save code:

>**********************************************************
**************
>Public Function SaveRecSet(ByVal objRecSet As
ADODB.Recordset, _
>                    Optional ByVal blnParentConn As
Boolean = True) As
>Variant
>    ' Save changes from the recordset
>    Dim rsChild As ADODB.Recordset
>    Dim i As Long

>    With objRecSet
>        .ActiveConnection = mobjConn
>        If .State = adStateOpen Then
>            On Error Resume Next
>            .UpdateBatch adAffectAllChapters
>    '*** IF STATEMENT INSERTED TO AVOID ERROR BELOW ***
>            If .BOF And .EOF Then
>                'Continue there are no records to process
>            Else
>                'Update each of the child recordsets ...
call
>                'recursively to handle all of the children
>                For i = 0 To .Fields.Count - 1
>                    If (.Fields(i).Type = adChapter) Then
>                        Set rsChild = New ADODB.Recordset
>    '*** NEXT LINE ERRORS WHEN THE RECORDSET IS EMPTY ***
>                        Set rsChild = .Fields(i).Value
>                        SaveRecSet rsChild, False
>                        Set rsChild = Nothing
>                    End If
>                Next i
>                If .State = adStateOpen Then
>                    Do    'Cycle through the recordset
until there are no
>conflicts
>                        .Filter =

adFilterConflictingRecords
Quote:
>                        If .EOF And .BOF Then    'No
conflicting records
>                            Exit Do
>                        Else    'There are conflicting
records
>                            .Resync adAffectGroup,

adResyncUnderlyingValues

- Show quoted text -

Quote:
>                            .UpdateBatch
adAffectAllChapters
>                        End If
>                    Loop
>                End If
>            End If
>        End If
>    End With

>    If blnParentConn = True Then
>        Set objRecSet.ActiveConnection = Nothing
>    End If

>    SaveRecSet = Err.Description

>End Function
>**********************************************************
**************



>> Hi Randy,

>> Could you post or email code? I think that problem in
>> updating part of code. It shold work, but without code
it
>> will be more difficult to find out problem.

>> Thanks,

>> Val
>> >-----Original Message-----
>> >I have a disconnected shaped recordset from a business
>> >object with parent, child, grandchild, and great-
>> >grandchild hierarchical recordsets.  If any record is
>> >deleted the client program deletes all the sub-children
>> >records to the "end" of the hierarchy.  So the sub-
child
>> >recordset, and all hierarchical levels below, will
>> >contain a zero record count.

>> >When the shaped recordset is passed back to the
business
>> >object the save routine looks for chapter fields and
>> >recursively calls itself to batch update the children
>> >recordsets.  Now when the save routine comes across one
>> >of the "empty" child recordsets the save routine has a
>> >problem.  It cannot assign a recordset to the chapter
>> >field because there are no records (this causes a "BOF
>> >or EOF" error) so any hierarchical levels below do not
>> >get updated.  To avoid the error the routine can stop
>> >the recursion when finding an empty recordset, but then
>> >any hierarchical levels below are obviously not
updated.

>> >Example: If I have several parent records and delete
>> >one of them, then the child, grandchild, and great-
>> >grandchild hierarchical recordsets have all their
>> >records deleted.  After being processed through the
>> >save routine, the parent and child tables reflect the
>> >delete changes but the grandchild and great-grandchild
>> >tables do not.

>> >How do I get the sub-child recordsets to update their
>> >tables?  Am I missing something?

>> >Code was left out to make the posting smaller.  Code
>> >snippets can be supplied on request.

>> >Thanks for any help.

>> >Randy

>> >.

>.



Wed, 05 May 2004 00:49:19 GMT
 Deleting Disconnected Shaped Recordset Problem
Randy,
Sorry, this is not an answer to your question.

But I was wondering if you can mail me portions of your code where you
delete the parent record.

I have a simple hierarchical recordset. When I try to delete the parent
record, the the child records
are not deleted. I know I am doing something wrong. But cannot identify
what.
Thanks for any help.
-- Manohar


Quote:
> I have a disconnected shaped recordset from a business
> object with parent, child, grandchild, and great-
> grandchild hierarchical recordsets.  If any record is
> deleted the client program deletes all the sub-children
> records to the "end" of the hierarchy.  So the sub-child
> recordset, and all hierarchical levels below, will
> contain a zero record count.

> When the shaped recordset is passed back to the business
> object the save routine looks for chapter fields and
> recursively calls itself to batch update the children
> recordsets.  Now when the save routine comes across one
> of the "empty" child recordsets the save routine has a
> problem.  It cannot assign a recordset to the chapter
> field because there are no records (this causes a "BOF
> or EOF" error) so any hierarchical levels below do not
> get updated.  To avoid the error the routine can stop
> the recursion when finding an empty recordset, but then
> any hierarchical levels below are obviously not updated.

> Example: If I have several parent records and delete
> one of them, then the child, grandchild, and great-
> grandchild hierarchical recordsets have all their
> records deleted.  After being processed through the
> save routine, the parent and child tables reflect the
> delete changes but the grandchild and great-grandchild
> tables do not.

> How do I get the sub-child recordsets to update their
> tables?  Am I missing something?

> Code was left out to make the posting smaller.  Code
> snippets can be supplied on request.

> Thanks for any help.

> Randy



Sun, 30 May 2004 02:01:53 GMT
 Deleting Disconnected Shaped Recordset Problem
What kind of database are you using ?
There is a much simplier solution for this kind of problem.
But it's database side solution
you can create a trigger with then event "before delete" that delete all
childs record before deleting the parent record..

ie :
this is an example of trigger i use and this work fine (ORACLE Database)

CREATE TRIGGER ADV_ADC_CASC_DEL

BEFORE DELETE

ON BANNERS

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

begin

delete from adviews

where (adv_bannerid=:old.ban_bannerid);

end ;

regards,
David



Quote:
> Randy,
> Sorry, this is not an answer to your question.

> But I was wondering if you can mail me portions of your code where you
> delete the parent record.

> I have a simple hierarchical recordset. When I try to delete the parent
> record, the the child records
> are not deleted. I know I am doing something wrong. But cannot identify
> what.
> Thanks for any help.
> -- Manohar



> > I have a disconnected shaped recordset from a business
> > object with parent, child, grandchild, and great-
> > grandchild hierarchical recordsets.  If any record is
> > deleted the client program deletes all the sub-children
> > records to the "end" of the hierarchy.  So the sub-child
> > recordset, and all hierarchical levels below, will
> > contain a zero record count.

> > When the shaped recordset is passed back to the business
> > object the save routine looks for chapter fields and
> > recursively calls itself to batch update the children
> > recordsets.  Now when the save routine comes across one
> > of the "empty" child recordsets the save routine has a
> > problem.  It cannot assign a recordset to the chapter
> > field because there are no records (this causes a "BOF
> > or EOF" error) so any hierarchical levels below do not
> > get updated.  To avoid the error the routine can stop
> > the recursion when finding an empty recordset, but then
> > any hierarchical levels below are obviously not updated.

> > Example: If I have several parent records and delete
> > one of them, then the child, grandchild, and great-
> > grandchild hierarchical recordsets have all their
> > records deleted.  After being processed through the
> > save routine, the parent and child tables reflect the
> > delete changes but the grandchild and great-grandchild
> > tables do not.

> > How do I get the sub-child recordsets to update their
> > tables?  Am I missing something?

> > Code was left out to make the posting smaller.  Code
> > snippets can be supplied on request.

> > Thanks for any help.

> > Randy



Sun, 30 May 2004 03:22:53 GMT
 Deleting Disconnected Shaped Recordset Problem
David:

Thanks for the reply.  You are correct in that I've had to "manually"
delete records in the child recordsets.  I was hoping there was a
setting or parameter I could set that would signal the child recordsets
to delete all their records when a parent record was deleted.

Randy


Quote:
> What kind of database are you using ?
> There is a much simplier solution for this kind of problem.
> But it's database side solution
> you can create a trigger with then event "before delete" that delete all
> childs record before deleting the parent record..

> ie :
> this is an example of trigger i use and this work fine (ORACLE Database)

> CREATE TRIGGER ADV_ADC_CASC_DEL

> BEFORE DELETE

> ON BANNERS

> REFERENCING NEW AS NEW OLD AS OLD

> FOR EACH ROW

> begin

> delete from adviews

> where (adv_bannerid=:old.ban_bannerid);

> end ;

> regards,
> David



Sun, 30 May 2004 22:07:13 GMT
 Deleting Disconnected Shaped Recordset Problem
Manohar:

The only solution I've found to delete the child recordset records
is to manually follow the child recordset heirarchy to the end
and then procede back up to the initial deleted parent while
deleteing all the records in all the child recordsets.

Randy


Quote:
> Randy,
> Sorry, this is not an answer to your question.

> But I was wondering if you can mail me portions of your code where you
> delete the parent record.

> I have a simple hierarchical recordset. When I try to delete the parent
> record, the the child records
> are not deleted. I know I am doing something wrong. But cannot identify
> what.
> Thanks for any help.
> -- Manohar



> > I have a disconnected shaped recordset from a business
> > object with parent, child, grandchild, and great-
> > grandchild hierarchical recordsets.  If any record is
> > deleted the client program deletes all the sub-children
> > records to the "end" of the hierarchy.  So the sub-child
> > recordset, and all hierarchical levels below, will
> > contain a zero record count.

> > When the shaped recordset is passed back to the business
> > object the save routine looks for chapter fields and
> > recursively calls itself to batch update the children
> > recordsets.  Now when the save routine comes across one
> > of the "empty" child recordsets the save routine has a
> > problem.  It cannot assign a recordset to the chapter
> > field because there are no records (this causes a "BOF
> > or EOF" error) so any hierarchical levels below do not
> > get updated.  To avoid the error the routine can stop
> > the recursion when finding an empty recordset, but then
> > any hierarchical levels below are obviously not updated.

> > Example: If I have several parent records and delete
> > one of them, then the child, grandchild, and great-
> > grandchild hierarchical recordsets have all their
> > records deleted.  After being processed through the
> > save routine, the parent and child tables reflect the
> > delete changes but the grandchild and great-grandchild
> > tables do not.

> > How do I get the sub-child recordsets to update their
> > tables?  Am I missing something?

> > Code was left out to make the posting smaller.  Code
> > snippets can be supplied on request.

> > Thanks for any help.

> > Randy



Sun, 30 May 2004 22:14:05 GMT
 Deleting Disconnected Shaped Recordset Problem
David,
Thanks for the idea. I am working on MSSql 7.0. As you suggested, I created
a trigger and it seems to work well.

Perhaps it was too far fetched to assume that the hierarchical recordset
works as I hoped it would.
-- Manohar


Quote:
> What kind of database are you using ?
> There is a much simplier solution for this kind of problem.
> But it's database side solution
> you can create a trigger with then event "before delete" that delete all
> childs record before deleting the parent record..

> ie :
> this is an example of trigger i use and this work fine (ORACLE Database)

> CREATE TRIGGER ADV_ADC_CASC_DEL

> BEFORE DELETE

> ON BANNERS

> REFERENCING NEW AS NEW OLD AS OLD

> FOR EACH ROW

> begin

> delete from adviews

> where (adv_bannerid=:old.ban_bannerid);

> end ;

> regards,
> David



> > Randy,
> > Sorry, this is not an answer to your question.

> > But I was wondering if you can mail me portions of your code where you
> > delete the parent record.

> > I have a simple hierarchical recordset. When I try to delete the parent
> > record, the the child records
> > are not deleted. I know I am doing something wrong. But cannot identify
> > what.
> > Thanks for any help.
> > -- Manohar



> > > I have a disconnected shaped recordset from a business
> > > object with parent, child, grandchild, and great-
> > > grandchild hierarchical recordsets.  If any record is
> > > deleted the client program deletes all the sub-children
> > > records to the "end" of the hierarchy.  So the sub-child
> > > recordset, and all hierarchical levels below, will
> > > contain a zero record count.

> > > When the shaped recordset is passed back to the business
> > > object the save routine looks for chapter fields and
> > > recursively calls itself to batch update the children
> > > recordsets.  Now when the save routine comes across one
> > > of the "empty" child recordsets the save routine has a
> > > problem.  It cannot assign a recordset to the chapter
> > > field because there are no records (this causes a "BOF
> > > or EOF" error) so any hierarchical levels below do not
> > > get updated.  To avoid the error the routine can stop
> > > the recursion when finding an empty recordset, but then
> > > any hierarchical levels below are obviously not updated.

> > > Example: If I have several parent records and delete
> > > one of them, then the child, grandchild, and great-
> > > grandchild hierarchical recordsets have all their
> > > records deleted.  After being processed through the
> > > save routine, the parent and child tables reflect the
> > > delete changes but the grandchild and great-grandchild
> > > tables do not.

> > > How do I get the sub-child recordsets to update their
> > > tables?  Am I missing something?

> > > Code was left out to make the posting smaller.  Code
> > > snippets can be supplied on request.

> > > Thanks for any help.

> > > Randy



Mon, 31 May 2004 01:01:32 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Deleting Disconnected Shaped Recordset Problem

2. problem with disconnected shaped recordset

3. problem with disconnected shaped recordset

4. Create a Shaped Disconnected Recordset from Code

5. Manually constructing disconnected shaped recordsets in 2.7

6. Disconnected SHAPE recordsets

7. Data shaping on disconnected recordsets

8. add record to disconnected shaped recordsets' child

9. Disconnected SHAPE recordsets

10. shape a disconnected recordset?

11. How to create a disconnected SHAPED recordset programmaticaly ?

12. SHAPE with disconnected recordsets


 
Powered by phpBB® Forum Software