Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

CRecordset::Update() problem

Posted on 1998-03-20
7
764 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction This article is the first in a series of articles about the C/C++ Visual Studio Express debugger.  It provides a quick start guide in using the debugger. Part 2 focuses on additional topics in breakpoints.  Lastly, Part 3 focuses on th…
This article shows you how to optimize memory allocations in C++ using placement new. Applicable especially to usecases dealing with creation of large number of objects. A brief on problem: Lets take example problem for simplicity: - I have a G…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

856 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