DAO 3.6 delete problem

[MGS76]

Hi,

I use the following code with dao 3.5 and dao 3.6. It works with 3.5, but with 3.6 at the second deletion a exception is thrown (from DoFieldExchange a String deletion fails)

    pSet2->Open();
    while(!pSet2->IsEOF())
    {
      pSet2->Delete();
      pSet2->MoveFirst();
    }
    pSet2->Close();

Whats the proble?

Thanks

Michael

[MGS76]

with Close() and Open() instead of MoveNext() it works, but is very slow.

[RNEELY]

I'm not really sure, but I can suggest some things to try.

Is your recordset empty before your code executes?
If so perhaps first refreshing the EOF flag will help:

pSet2->Open();
pSet2->MoveLast();
pSet2->MoveFirst();
while(!pSet2->IsEOF())
{
    pSet2->Delete();
    pSet2->MoveFirst();
}
pSet2->Close();

Is there any chance it is deleting the same record twice?
If so, perhaps an Update will help. Though you might not be able to update a deleted record.

pSet2->Open();
pSet2->MoveLast();
pSet2->MoveFirst();
while(!pSet2->IsEOF())
{
    pSet2->Delete();
    pSet2->Update();
    pSet2->MoveFirst();
}
pSet2->Close();

Good luck.

[VictorN]

If so, perhaps an Update will help. Though you might not be able to update a deleted record.

code:--------------------------------------------------------------------------------
pSet2->Open();
pSet2->MoveLast();
pSet2->MoveFirst();
while(!pSet2->IsEOF())
{
pSet2->Delete();
pSet2->Update();
pSet2->MoveFirst();
}
pSet2->Close();
--------------------------------------------------------------------------------

I am sorry, but this suggestion to use Update is not correct. According to MSDN ("DAO Recordset: Recordset Operations"):

Deleting Records in DAO
For general information about deleting records in DAO, see the topic "Delete Method" in DAO Help.

To delete a record from a recordset

Determine whether your recordset is updatable.
Call the recordset'sCanUpdate member function.

Move to the record you want to edit.
Use any of the recordset's navigation mechanisms that take you to a specific record.

Call the recordset's Delete member function.
You don't call Update for a deletion.

Move to another record before you attempt any other recordset operations.
In table-type and dynaset-type recordset objects, Delete removes the current record and makes it inaccessible. Although you can't edit or use the deleted record, it remains current. Once you move to another record, however, you can’t make the deleted record current again. Subsequent references to a deleted record in a recordset are invalid and cause an exception to be thrown. For more information, see Delete. You can tell whether you’re on a deleted record by calling IsDeleted.

[RNEELY]

VictorN,
You are correct. Update() will not work after Delete().
So then how to correct MGS76's problem? Perhaps:

pSet2->Open();
pSet2->MoveLast();
pSet2->MoveFirst();
while(!pSet2->IsEOF())
{
    pSet2->Delete();
    pSet2->MoveLast();
    pSet2->MoveFirst();
}
pSet2->Close();

[VictorN]

I'd suggest to follow MS code from MSDN article "DAO Workspace: Managing Transactions" and to do as:

try
{
    pSet2->Open();
    while(!pSet2->IsEOF())
    {
              pSet2->Delete();
              pSet2->MoveNext();
    }
    pSet2->Close();
}
catch(CDaoException* e)
{
        AfxMessageBox( e->m_pErrorInfo->m_strDescription);
        e->Delete( );
        ...........
}

Another possibility - to use CDaoDatabase::Execute method: create SQL-delete query, do Execute, then call CDaoRecordset::Requery if necessary