Using transactions in SQL Server 7.0

I have a series of records to add to different tables in a SQL Server 7.0 database.

The first thing I did was create a new workspace and opened the database.  Then I used the WorkSpace.BeginTrans to start recording the transactions. The first set records okay but it hangs on the second set of transaction.  The transactions are using DAO recordset and the AddNew method.  Does anyone have any advice ??



AnnaWangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KangaRooCommented:
Do you perform any selects after you've updated tables? Don't.
0
inprasCommented:
Hi
How about some samle code? may be some mistake cannot guess right?

Regards
0
AnnaWangAuthor Commented:
Even if I took out the select in the second insert, I still get an dao error.

Please help ....


Here is the sample code:



#include <afxdao.h>

//Global vars
CDaoDatabase    *db;
CDaoWorkspace      *wsSQLWork;

//main
extern __declspec(dllexport) long __stdcall ssrtf_updatedb( ...some arguments){


.......
.........

AfxDaoInit();

//open database
if (!opendb())
{
fprintf(fplog, "\nError opening the database. Aborting !");
ret_stat = 1;            
return(ret_stat);
}


wsSQLWork->BeginTrans();

//first insert
if (insertfirstOrdVault() == 0)
{
                              err = true;
}

//***THIS IS WHERE IT HANGS****!
if (insertsecondOrdVault() == 0)
{
                                    err = true;
}

//rollback transaction
if (err == true)
{
      wsSQLWork->Rollback();
}
//commit transaction
else
{
      wsSQLWork->CommitTrans();
}
      AfxDaoTerm();
        return 0;

}




//function to open database

bool openDMSdb()
{

 // Only call for regular MFC DLL's
   #ifndef _AFXDLL
   AFX_MANAGE_STATE (AfxGetStaticModuleState());
      #endif

wsSQLWork = new CDaoWorkspace;
db = new CDaoDatabase;

try
{
// open default  work space
wsSQLWork->Open(NULL);

// open the database
//Sql database
db->Open(...database path etc...);
}            
catch () // catch Dao database exceptions
{
..............
...............
}
}


//function to insert first set of records
//**** THIS PART WORKS FINE
int insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}

//function to insert first set of records
//**** THIS IS WHERE IT HANGS insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}



0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

AnnaWangAuthor Commented:
Even if I took out the select in the second insert, I still get an dao error.

Please help ....


Here is the sample code:



#include <afxdao.h>

//Global vars
CDaoDatabase    *db;
CDaoWorkspace      *wsSQLWork;

//main
extern __declspec(dllexport) long __stdcall ssrtf_updatedb( ...some arguments){


.......
.........

AfxDaoInit();

//open database
if (!opendb())
{
fprintf(fplog, "\nError opening the database. Aborting !");
ret_stat = 1;            
return(ret_stat);
}


wsSQLWork->BeginTrans();

//first insert
if (insertfirstOrdVault() == 0)
{
                              err = true;
}

//***THIS IS WHERE IT HANGS****!
if (insertsecondOrdVault() == 0)
{
                                    err = true;
}

//rollback transaction
if (err == true)
{
      wsSQLWork->Rollback();
}
//commit transaction
else
{
      wsSQLWork->CommitTrans();
}
      AfxDaoTerm();
        return 0;

}




//function to open database

bool openDMSdb()
{

 // Only call for regular MFC DLL's
   #ifndef _AFXDLL
   AFX_MANAGE_STATE (AfxGetStaticModuleState());
      #endif

wsSQLWork = new CDaoWorkspace;
db = new CDaoDatabase;

try
{
// open default  work space
wsSQLWork->Open(NULL);

// open the database
//Sql database
db->Open(...database path etc...);
}            
catch () // catch Dao database exceptions
{
..............
...............
}
}


//function to insert first set of records
//**** THIS PART WORKS FINE
int insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}

//function to insert first set of records
//**** THIS IS WHERE IT HANGS insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}



0
NullTerminatorCommented:
Have you tried passing the WorkSpace pointer to the db constructor after opening the wksp.  Also I notice you don't close your recordsets.

You might try an ptrDB->Execute(strSQL) where strSQL = "Insert into myTable field1, field2 values (10, 20)"

'\0'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
inprasCommented:
How about making rstDocVault global
I think the problem is U R opening it twice under the same workspace and since UR using Begin TRans and Commit trans the records U update till U commit or roll back will be locked try using the global var of CDaoRecordSet

Hope this helps

Regards


0
AnnaWangAuthor Commented:
How do you make the recordset global ?
0
inprasCommented:
Hi AnnaWang
declaration of function and variables
bool openDMSdb() ;
int insertsecondOrdVault(CTryDaoSet&);
int insertfirstDocVault(CTryDaoSet&) ;
CDaoDatabase    *db;
CDaoWorkspace *wsSQLWork;
Not: Here CTryDaoSet is my recordset  class derived from CDaoRecordset
In my function where I call to update
      CTryDaoSet rstDocVault(db);
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM NUm", dbSeeChanges);
      wsSQLWork = new CDaoWorkspace;
      db = new CDaoDatabase;
      wsSQLWork->Open(NULL);
      wsSQLWork->BeginTrans();
      bool err = false;
      if (insertfirstDocVault(rstDocVault) == 0)
      {
            err = true;
      }

      //***THIS IS WHERE IT HANGS****!
      if (insertsecondOrdVault(rstDocVault) == 0)
      {
            err = true;
      }
      if (err == true)
      {
            wsSQLWork->Rollback();
      }
      else
      {
      wsSQLWork->CommitTrans();
      }

the other functions will remain same except opening the recordset
int insertfirstDocVault(CTryDaoSet& rs)
{
            rs.AddNew();
        rs.m_Number = 112;
            rs.m_Name = "PrasannaIV";
        rs.Update();
            return 1;
}

int insertsecondOrdVault(CTryDaoSet& rs)
{
            rs.AddNew();
        rs.m_Number = 111;
            rs.m_Name = "PrasannaI";
        rs.Update();
            return 1;
}

Hope this helps
Regards
0
AnnaWangAuthor Commented:
I solved it.  I had to use db->Execute(strSQL).  

Much thanks to all that responded.
0
AnnaWangAuthor Commented:
Thanks a lot for all your response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C++

From novice to tech pro — start learning today.