?
Solved

CRecordset::Update() problem

Posted on 1998-03-20
7
Medium Priority
?
781 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
[X]
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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

What is C++ STL?: STL stands for Standard Template Library and is a part of standard C++ libraries. It contains many useful data structures (containers) and algorithms, which can spare you a lot of the time. Today we will look at the STL Vector. …
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…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Suggested Courses

762 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