Solved

CRecordset::Update() problem

Posted on 1998-03-20
7
759 Views
Last Modified: 2008-02-01
I am using VC++ 5.0 with MS Access 97. I have created a table in a database that will store courier jobs. I can Add, Edit and Delete jobs from this table.  Jobs may be added either through a dialog box or directly from within the code. When using the dialog box, I get an Update error AFX_SQL_ERROR_ILLEGAL_MODE occasionally. I have noticed a time dependency. The faster I try to add jobs the more likely the error will occur. The error indicates that I have not performed an AddNew() or Edit() call prior to the Update(), but this is not true in my case. I wrapped a CanUpdate() around this code and it will return false sometimes. My database/recordset is not readonly. The only explanation I have is time related. But I can not believe it can take that long to update. It is a single user application and the size of the job never exceeds 200 bytes. Any suggestions? I can be reached at domenic@dtrac.com if source code is required. Thanks.
0
Comment
Question by:domenic
7 Comments
 
LVL 6

Expert Comment

by:jpk041897
ID: 1183674
Its time related all right. but its the Access DB engiene thats causing the problem, not your app.

The code Access uses for updating the index Btrieve file is one that does not reuse the space of deleted records (thus the need for the compress utility).

As a result of this, evry now and then, Access needs to reorganize the tree. The frequency of the problem will decrease as a function of the number of records, but the time it takes to reorganize wil increase at the same rate.

Simply stated, there is nothing you can do in your code to fix the problem, you can only avoid it by making a call to CanUpdate() before you insert/update a record and then sleep and loop untill you can:

while (!rs.CanUpdate())
   sleep(200);
...



0
 
LVL 2

Expert Comment

by:lucidity
ID: 1183675
it helps to have :
      m_nDefaultType = dynaset;

I used to have the same problem of my database appearing to be readonly and I changed that value and all was fine.

and never ever start AddNew or Edit then start jumping in and
out of functions. if you start a new record in a function you should Update() before leaving that function or calling any other functions.
0
 
LVL 1

Author Comment

by:domenic
ID: 1183676
The m_nDefaultType is set to dynaset.  I am using a dialog box therefore I must leave the function in order for my fields to be set. for example,

m_pSet->AddNew();
JobsDlg.pSet = m_pSet;
JobsDlg.Insert = TRUE;

if ( JobsDlg->DoModal() == IDOK )
{
     if ( ! m_pSet->CanUpdate() )
        ......Complain about not being able to update
     else
         try{
                 if (  m_pSet->Update() )
                     .....Good...
                else
                    ....Complain
         }
        catch( CDBException* e )
       {
                output error .....  e->m_strError, e->m_nRetCode ...
        }

Also this application  is  multithreaded. What are the criterias other than ReadOnly that causes CanUpdate() to return false? And again I emphasize the fact that it is intermittent.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:lucidity
ID: 1183677
First off CanUpdate should always be called BEFORE AddNew(), this could account for the erratic behavior.

You might want to consider doing the record manipulation in the dialog class itself. Also, MAKE SURE that all of your control paths call update() to end the AddNew(). If you don't have one add an else to this line

if ( JobsDlg->DoModal() == IDOK )
       {blah blah}
else
       make_sure_the_damned_record_is_closed();
------------
try something like this

if ( ! m_pSet->CanUpdate() )
    ......Complain about not being able to update
    ....exit function before going any further
m_pSet->AddNew();
JobsDlg.pSet = m_pSet;
JobsDlg.Insert = TRUE;

if ( JobsDlg->DoModal() == IDOK )
{
        if (  m_pSet->Update() )
                     .....Good...
        else
                    ....Complain
}
else {
     m_pSet->Update();
     m_pSet->Delete();
}
 


0
 
LVL 1

Author Comment

by:domenic
ID: 1183678
I introduced the CanUpdate() after I was experiencing the problem. Nonetheless, I inserted the CanUpdate before my call to AddNew(). I Close() immediately after each record manipulation. Now I am back to the original symptom. Intermittently  the Updates will fail (despite the fact CanUpdate was true) with the error "Update or Delete failed" m_nRetCode = 1005. The piece of code is found below:


if ( !m_pSet->CanUpdate() )
{
      Complain ....
      m_pSet->Close();
      return();
}
TRY
{
      m_pSet->AddNew();
}
CATCH( CDBException, e )
{
      CString strErrorMsg = CString("Couldn't prepare recordset for adding                                                                                                                                     record --Exception: ") + e->m_strError;
      AfxMessageBox( strErrorMsg, MB_OK );
      e->Delete();
}
END_CATCH

JobsDlg.pSet = m_pSet;
JobsDlg.Insert = TRUE;
      
if( JobsDlg.DoModal() == IDOK )
{
      if( UpdateMoreData( m_pSet, false, false ) )
     {
           try
          {
      m_pSet->m_RetransmitCount = 0;
      m_pSet->m_LastSeqNo = 0;
      m_pSet->m_TimeOfOrigin = (long)time(0);
      m_pSet->m_MessageType = "JOB";
      success = m_pSet->Update();
      if( !success )
      {
              Complain .....                                                                                                                       }
      else
      {
             m_pSet->Close();
      }
         }
        catch( CDBException* e )
        {
              success = 0;
             Complain ......  (THIS IS WHERE IT HITS)
         }
}
else
{
        m_pSet->CancelUpdate();
}
if( m_pSet->IsOpen() )
    m_pSet->Close();

0
 

Accepted Solution

by:
gianapa earned 100 total points
ID: 1183679
I would like you to try the foll 3 suggestions:

1. The usual timeout is 15 sec, for a test purpose, use the CDatabase::SetQueryTimeout(x) after the database is opened and before you do any operation on the database.
Set the value of x to a reasonably large value or even 0, i.e no timeout. See if there is any difference.
2. Also try using transactions Update functionality, after using the CanTransact() to check if the database supports transactions.
3. Since the errors messages you are getting do not take you close to the problem, I suggest you keep DB Tracing ON. You will get detail messages in the output window. These messages will give you a clear picture of the problem. The technical note for DB tracing is very well documented in the HELP..
0
 
LVL 1

Author Comment

by:domenic
ID: 1183680
My problem ended up being linked to multiple threading. The threads would change the status of the database. That is, a Close() was being triggered while aan Edit() was still pending. Thanks anyway for all the feedback.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Unlike C#, C++ doesn't have native support for sealing classes (so they cannot be sub-classed). At the cost of a virtual base class pointer it is possible to implement a pseudo sealing mechanism The trick is to virtually inherit from a base class…
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

896 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now