[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

in DAO, how to detect records deleted by a 'cascade delete', without requerying the recordset ?

Posted on 2004-11-24
5
Medium Priority
?
349 Views
Last Modified: 2008-03-17
(this is a repost  of a question posted in MFC area, i had no answer, perhaps i will be more successful here)

I'm working on Access databases with CDao classes; i have a recordset A, which is linked with a recordset B, via a relation with the dbRelationDeleteCascade attribute, so that when one deletes a record in B, it automatically deletes a linked record in A.
So some records can be automatically deleted in recordset A, and because i keep it always open (without requery), i have to be cautious about that when i browse in recordset A. I tried 2 methods :

1st method : I naively believed that CDaoRecordSet::IsDeleted() would detect deleted records :
--------------

while (! m_pRst->IsEOF())
{
  if (! m_pRst->IsDeleted())
 {
   m_pRst->GetFieldValue(Info.pchFieldTag, tmp);
   ...
 }
 m_pRst->MoveNext();
}

IsDeleted() returns a value not TRUE or FALSE but = -842150451 ! after examinig the MFC code (daocore.cpp),  i see that m_bDeleted (the value returned by IsDeleted) was not initialized by MFC code in my case, because i don't use the DoFieldExchange mechanism (i use rather Get/SetFieldValue). So i initialized myself m_bDeleted to FALSE in the creation of the recordset (luckily it was not protected).
After that, i deleted a record in B (--> deletes also a record in A) but m_bDeleted stays = FALSE ! Apparently the MFC code set it to TRUE only when one explicitly calls Delete(). So the MFC implementation seems buggy.


2nd method : in  dbdaoint.h  i found this DAORecordset method :
---------------
STDMETHOD(get_RecordStatus)      (THIS_ short FAR* pi) PURE;
and status is :
typedef enum RecordStatusEnum {
    dbRecordUnmodified = 0,
    dbRecordModified = 1,
    dbRecordNew = 2,
    dbRecordDeleted = 3,
    dbRecordDBDeleted = 4
} RecordStatusEnum;

so i tried  :

short status = 0;
m_pRst->m_pDAORecordset->get_RecordStatus(&status);
if (status != dbRecordDeleted && status != dbRecordDBDeleted)
m_pRst->GeFieldValue(...)
...

but status stays = 0 even if the current record has been deleted; i'm sure the record is deleted because m_pRst->GeFieldValue
causes an assert "Record is deleted". The DAO errorcode is 3167.

Is there a way to detect records deleted by a 'cascade delete', without requerying the recordset ?
0
Comment
Question by:JP_Goblet
  • 3
  • 2
5 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 12673117
The properties you are using are used for batch updates, before commiting the transaction. For example, the help for dbRecordDeleted is: "The record has been deleted, but not yet deleted in the database."

IsDeleted means -- I believe --: deleted here (no longer valid) and deleted from the table.

To my knowlege, there is no way to detect a deleted record other than catching the error. This is partly because the record can be deleted *after* you have tested if it was... :) Also note the deleted records are frequent in dynasets (keysets) but much less in tyble-type recordsets. For those, you actually have to locate a record before it is deleted in order to get an error. Perhaps this is what you need?

So it's rather simple: put every record operation within error-cating structures.

Good Luck
0
 

Author Comment

by:JP_Goblet
ID: 12673406
"put every record operation within error-cating structures"

lin fact it's what i already do (i didn't show the try catch block in my 1st post for simplicity)

try
{
  while (! m_pRst->IsEOF())
   {
    if (! m_pRst->IsDeleted())
   {
     m_pRst->GetFieldValue(Info.pchFieldTag, tmp);
     ...
   }
   m_pRst->MoveNext();
}
catch(CDaoException* e)
{
 if (exception error code = record deleted)
 {
   ... ?
 }
}

but when an exception occurs, it's too late, i have no way to come back in the loop where the execution was stopped ?
0
 
LVL 58

Accepted Solution

by:
harfang earned 1500 total points
ID: 12675546
Based on the above, you need some thing like this:

try
{
  while (! m_pRst->IsEOF())
   {
    try { m_pRst->GetFieldValue(Info.pchFieldTag, tmp); }
    catch(CDaoException* e)
   {
    if (exception error code = record deleted) { ... no value ... }
   }
   m_pRst->MoveNext();
}
catch(CDaoException* e)
{
 if (exception error code = ... other error ...)
 {
   ... ?
 }
}

IsEOF() will not return an error, only getting the field information will.

Good Luck
0
 

Author Comment

by:JP_Goblet
ID: 12679342
Thanks. Not quite satisfactory, but if there is no more elegant solution, i have to do that sort of trick.

As i have to do this sort of error catching in numerous places in my code, i will rather implement overloaded versions of MoveFirst() and MoveNext(), versions that care of detecting deleted records and hiding them, so that the caller function only sees valids records.
But as more & more records are deleted, it will more & more hinder the browsing in the recordset. Perhaps, after all, would it be better to requery the recordset, as soon a deleted record as been detected ?
0
 
LVL 58

Expert Comment

by:harfang
ID: 12682392
Either that or use a table-type recordset, yes.
Keysets are not meant to stay open very long...
Good Luck!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question