Solved

CRecordset::Update() problem

Posted on 1998-03-20
7
749 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Expert Comment

by:lucidity
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

772 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

13 Experts available now in Live!

Get 1:1 Help Now