Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

CRecordset::Update() problem

Posted on 1998-03-20
7
Medium Priority
?
787 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
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.

 
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

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

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
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 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 viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
Suggested Courses

609 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