DAO multiple Addnew's requires closing and reopening of Recordset 
Author Message
 DAO multiple Addnew's requires closing and reopening of Recordset

I have a VC++ program that requires the adding of multiple new records to a
DAO Recordset.  I find that after the first Addnew/Update cycle, I must
close and reopen the recordset or else I throw a "Update(/Cancel Update)
without Addnew(/Edit)" CDaoException.  Is the constant reopening of the
recordset just something I have to do or am I doing something wrong?  Maybe
more importantly, is the reopening of a recordset much of a performance hit
or am I worrying about nothing?   I hope the following code snippet provides
the necessary information on what it is that I am trying to do:

////////////////////////////////////////////////////////////////////////////
/
// CDAOAddNewApp initialization

BOOL CDAOAddNewApp::InitInstance()
{
//  the DAO constructs

 CDaoDatabase myDB;
 CDaoRecordset *pmyRS=NULL;

 //  The test database has one table, TestTable with one field Fld1
(exciting naming, huh?)

 LPCTSTR lpszDBName = ".\\TestDB.mdb";
 BOOL bExclusiveMode = false;
 BOOL bReadOnlyMode = false;

 //   initialize the PC side file or db/recordset for use
 //   open the MS Jet (ADO) Database for use
 try {
  myDB.Open(lpszDBName, bExclusiveMode, bReadOnlyMode);
 }
 catch (CDaoException *e) {
  AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
  // delete e;
 }
 //   and then the recordset
 try {
  pmyRS = new CDaoRecordset(&myDB);
  pmyRS->Open(dbOpenDynaset, "SELECT * from TestTable", dbAppendOnly);

  //  I have also tried the below open instead and both approaches with and
  //  without the dbAppendOnly option

//  pmyRS->Open(dbOpenTable, "[TestTable]", dbAppendOnly);

 }
 catch (CDaoException *e) {
  AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
  // delete e;
 }

 //   create my records and add them to the database

 int i;
 char bigBuf[1024];
 int numRecs = 10;
 char startTime[50];
 time_t timeNow;

//  the time stamp is just so I can distiguish test runs

 time(&timeNow);
 strftime( startTime, sizeof(startTime), "starting at %j-%H:%M:%S ",
localtime(&timeNow));

 for (i = 0; i < numRecs; i++) {

  strcpy(bigBuf, startTime);
  sprintf(bigBuf + strlen(bigBuf), "test record %i", i);

  // write the data out into the db

  try {
   pmyRS->AddNew();
   pmyRS->SetBookmark(pmyRS->GetLastModifiedBookmark()); // suggested by
tutorial lesson
   COleVariant myVar;
   myVar.SetString((const char *)bigBuf, VT_BSTRT);
   pmyRS->SetFieldValue("Fld1", myVar);
   pmyRS->Update();

   //  on second and subsequent loops through this code the SetFieldValue
call above throws a
   //  CDaoException indicating that I am trying an Update without an
intervening
   //  AddNew.  However, if the following two lines are uncommented, the
code
   //  runs without error.

//   pmyRS->Close();
//   pmyRS->Open(dbOpenDynaset, "SELECT * from TestTable");

  }
  catch (CDaoException *e) {
   AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
  }
 }

 //  everything is done, time to clean up

 try {
  pmyRS->Close();
  delete pmyRS;
  myDB.Close();
  AfxDaoTerm();
 }
 catch (CDaoException *e) {
  AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
 }
 return false;

Quote:
}

//////////////////////////////////////////////////////////

TIA,

Marty Packer



Mon, 30 Dec 2002 03:00:00 GMT  
 DAO multiple Addnew's requires closing and reopening of Recordset

I think this line unnecessary.

Quote:
>    pmyRS->SetBookmark(pmyRS->GetLastModifiedBookmark()); // suggested by
> tutorial lesson

I have tried to add thousands records with AddNew in loop successfully.
But my problems arise later.
When loop ends and I try set bookmark into the end of recordset, i get
CDaoException
3167.But if i add only  ~<200 records everything seems ok.Requery helps
but is very awkward, because from recordset I populate virtual listview cash
and user may have some records marked (I store bookmarks of marked items
(records))


Quote:
> I have a VC++ program that requires the adding of multiple new records to
a
> DAO Recordset.  I find that after the first Addnew/Update cycle, I must
> close and reopen the recordset or else I throw a "Update(/Cancel Update)
> without Addnew(/Edit)" CDaoException.  Is the constant reopening of the
> recordset just something I have to do or am I doing something wrong?
Maybe
> more importantly, is the reopening of a recordset much of a performance
hit
> or am I worrying about nothing?   I hope the following code snippet
provides
> the necessary information on what it is that I am trying to do:

////////////////////////////////////////////////////////////////////////////
Quote:
> /
> // CDAOAddNewApp initialization

> BOOL CDAOAddNewApp::InitInstance()
> {
> //  the DAO constructs

>  CDaoDatabase myDB;
>  CDaoRecordset *pmyRS=NULL;

>  //  The test database has one table, TestTable with one field Fld1
> (exciting naming, huh?)

>  LPCTSTR lpszDBName = ".\\TestDB.mdb";
>  BOOL bExclusiveMode = false;
>  BOOL bReadOnlyMode = false;

>  //   initialize the PC side file or db/recordset for use
>  //   open the MS Jet (ADO) Database for use
>  try {
>   myDB.Open(lpszDBName, bExclusiveMode, bReadOnlyMode);
>  }
>  catch (CDaoException *e) {
>   AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>   // delete e;
>  }
>  //   and then the recordset
>  try {
>   pmyRS = new CDaoRecordset(&myDB);
>   pmyRS->Open(dbOpenDynaset, "SELECT * from TestTable", dbAppendOnly);

>   //  I have also tried the below open instead and both approaches with
and
>   //  without the dbAppendOnly option

> //  pmyRS->Open(dbOpenTable, "[TestTable]", dbAppendOnly);

>  }
>  catch (CDaoException *e) {
>   AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>   // delete e;
>  }

>  //   create my records and add them to the database

>  int i;
>  char bigBuf[1024];
>  int numRecs = 10;
>  char startTime[50];
>  time_t timeNow;

> //  the time stamp is just so I can distiguish test runs

>  time(&timeNow);
>  strftime( startTime, sizeof(startTime), "starting at %j-%H:%M:%S ",
> localtime(&timeNow));

>  for (i = 0; i < numRecs; i++) {

>   strcpy(bigBuf, startTime);
>   sprintf(bigBuf + strlen(bigBuf), "test record %i", i);

>   // write the data out into the db

>   try {
>    pmyRS->AddNew();
>    pmyRS->SetBookmark(pmyRS->GetLastModifiedBookmark()); // suggested by
> tutorial lesson
>    COleVariant myVar;
>    myVar.SetString((const char *)bigBuf, VT_BSTRT);
>    pmyRS->SetFieldValue("Fld1", myVar);
>    pmyRS->Update();

>    //  on second and subsequent loops through this code the SetFieldValue
> call above throws a
>    //  CDaoException indicating that I am trying an Update without an
> intervening
>    //  AddNew.  However, if the following two lines are uncommented, the
> code
>    //  runs without error.

> //   pmyRS->Close();
> //   pmyRS->Open(dbOpenDynaset, "SELECT * from TestTable");

>   }
>   catch (CDaoException *e) {
>    AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>   }
>  }

>  //  everything is done, time to clean up

>  try {
>   pmyRS->Close();
>   delete pmyRS;
>   myDB.Close();
>   AfxDaoTerm();
>  }
>  catch (CDaoException *e) {
>   AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>  }
>  return false;
> }
> //////////////////////////////////////////////////////////

> TIA,

> Marty Packer



Tue, 31 Dec 2002 03:00:00 GMT  
 DAO multiple Addnew's requires closing and reopening of Recordset

Eduardas,

Thanks very much, that did the trick.

Marty


Quote:
> I think this line unnecessary.
> >    pmyRS->SetBookmark(pmyRS->GetLastModifiedBookmark()); // suggested by
> > tutorial lesson



Tue, 31 Dec 2002 03:00:00 GMT  
 DAO multiple Addnew's requires closing and reopening of Recordset
Sounds like you're doing something else wrong.

The error says you are performing a CRecordset::Update without preceding it
with either a
CRecordset::Edit or CRecordset::AddNew. An update must come after one of
these two operations.
Check your program logic to see that you aren't doing ..

AddNew()
Update()
Update()


Quote:
> I have a VC++ program that requires the adding of multiple new records to
a
> DAO Recordset.  I find that after the first Addnew/Update cycle, I must
> close and reopen the recordset or else I throw a "Update(/Cancel Update)
> without Addnew(/Edit)" CDaoException.  Is the constant reopening of the
> recordset just something I have to do or am I doing something wrong?
Maybe
> more importantly, is the reopening of a recordset much of a performance
hit
> or am I worrying about nothing?   I hope the following code snippet
provides
> the necessary information on what it is that I am trying to do:

////////////////////////////////////////////////////////////////////////////
Quote:
> /
> // CDAOAddNewApp initialization

> BOOL CDAOAddNewApp::InitInstance()
> {
> //  the DAO constructs

>  CDaoDatabase myDB;
>  CDaoRecordset *pmyRS=NULL;

>  //  The test database has one table, TestTable with one field Fld1
> (exciting naming, huh?)

>  LPCTSTR lpszDBName = ".\\TestDB.mdb";
>  BOOL bExclusiveMode = false;
>  BOOL bReadOnlyMode = false;

>  //   initialize the PC side file or db/recordset for use
>  //   open the MS Jet (ADO) Database for use
>  try {
>   myDB.Open(lpszDBName, bExclusiveMode, bReadOnlyMode);
>  }
>  catch (CDaoException *e) {
>   AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>   // delete e;
>  }
>  //   and then the recordset
>  try {
>   pmyRS = new CDaoRecordset(&myDB);
>   pmyRS->Open(dbOpenDynaset, "SELECT * from TestTable", dbAppendOnly);

>   //  I have also tried the below open instead and both approaches with
and
>   //  without the dbAppendOnly option

> //  pmyRS->Open(dbOpenTable, "[TestTable]", dbAppendOnly);

>  }
>  catch (CDaoException *e) {
>   AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>   // delete e;
>  }

>  //   create my records and add them to the database

>  int i;
>  char bigBuf[1024];
>  int numRecs = 10;
>  char startTime[50];
>  time_t timeNow;

> //  the time stamp is just so I can distiguish test runs

>  time(&timeNow);
>  strftime( startTime, sizeof(startTime), "starting at %j-%H:%M:%S ",
> localtime(&timeNow));

>  for (i = 0; i < numRecs; i++) {

>   strcpy(bigBuf, startTime);
>   sprintf(bigBuf + strlen(bigBuf), "test record %i", i);

>   // write the data out into the db

>   try {
>    pmyRS->AddNew();
>    pmyRS->SetBookmark(pmyRS->GetLastModifiedBookmark()); // suggested by
> tutorial lesson
>    COleVariant myVar;
>    myVar.SetString((const char *)bigBuf, VT_BSTRT);
>    pmyRS->SetFieldValue("Fld1", myVar);
>    pmyRS->Update();

>    //  on second and subsequent loops through this code the SetFieldValue
> call above throws a
>    //  CDaoException indicating that I am trying an Update without an
> intervening
>    //  AddNew.  However, if the following two lines are uncommented, the
> code
>    //  runs without error.

> //   pmyRS->Close();
> //   pmyRS->Open(dbOpenDynaset, "SELECT * from TestTable");

>   }
>   catch (CDaoException *e) {
>    AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>   }
>  }

>  //  everything is done, time to clean up

>  try {
>   pmyRS->Close();
>   delete pmyRS;
>   myDB.Close();
>   AfxDaoTerm();
>  }
>  catch (CDaoException *e) {
>   AfxMessageBox( e->m_pErrorInfo->m_strDescription, MB_ICONEXCLAMATION);
>  }
>  return false;
> }
> //////////////////////////////////////////////////////////

> TIA,

> Marty Packer



Wed, 01 Jan 2003 03:00:00 GMT  
 DAO multiple Addnew's requires closing and reopening of Recordset


Quote:
> Sounds like you're doing something else wrong.

> The error says you are performing a CRecordset::Update without preceding
it
> with either a
> CRecordset::Edit or CRecordset::AddNew. An update must come after one of
> these two operations.
> Check your program logic to see that you aren't doing ..

> AddNew()
> Update()
> Update()

Oddly, it was the SetBookmark call that was somehow fouling up the works for
reasons that are not clear to me.  I had checked to see make sure that I was
not doing what it is that you suggested with a slew of debugging print
statements and also just trying things out in the de{*filter*}.  Both debug
approaches showed the error being thrown in the SetFieldValue call and not
in the Update call as one would have expected from the error message.  Go
figure.

MP



Wed, 01 Jan 2003 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. DAO recordset AddNew and Update

2. DAO AddNew Recordset SQL Server not equal Access attached tables

3. can't reopen the Toolbar after it closed

4. AddNew on an empty Recordset fails in MS C++ AddNew Example

5. Why Recordset can't AddNew()

6. ADO Recordset.Addnew() call throws an error that doesn't make any sense

7. Problem with closing DAO recordsets

8. Does ANYONE understand multiple recordsets/views using DAO???

9. CDaoRecordView doesn't close recordset

10. ReOpen ADO Recordset with a current Connection

11. CDaoRecordView doesn't close recordset

12. Can a process close and reopen stdin

 

 
Powered by phpBB® Forum Software