Using transactions in SQL Server 7.0

Do you perform any selects after you've updated tables? Don't.

Hi
How about some samle code? may be some mistake cannot guess right?

Regards

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()
{           ..............
}
}

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()
{           ..............
}
}

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

How do you make the recordset global ?

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

I solved it.  I had to use db->Execute(strSQL).  

Much thanks to all that responded.

Thanks a lot for all your response.