ODBC

I am currently now designing an application that is using ODBC as an interface. My database is MS Access. And i'm using Visual C++ to developed my front end application. I developed my front end application using the app wizard and connect it to the data source through the ODBC.

The problem i am facing right now is how do i retrieve more than one record from the recorset that belong to a particular table and move them to another table automatically.

For intance table A and Table B.

In table A i want to retrive all those records that meets the criteria. For instance :

Between monday to friday.

I want to take all those records from monday - Friday in table A to table B.
After that i want to delete all those record in table A.


Regards
Eugene

eugene007Asked:
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.

Vinayak KumbarSr Program ManagerCommented:
Hi,

U can use MoveFirst(), MoveNext() functions for that. And to check the end of file U can put it in a while loop.

If the m_CCmd is the data table variable

//Open the table
m_CCmd.Open();

//Get the record count
if(!m_CCmd.GetRecordCount())
  return;

//Start looping
m_CCmd.MoveFirst();
while(!m_CCmd..IsEOF())
{
 //Retrieve the record
   m_CCmd.MoveNext();
}

OR

u can use the filter to get the only required recors.
Or u can pass the SQL query to Open() to get the records u want.

Try it out.
0
inprasCommented:
Hi euqene007

There are two ways basically U can do it
1.
Using SQL statement like SELECT * FROM tab1 WHERE cond1 AND cond2

Use this Recordset to add new Records to tab2 like
ExecuteSQL("INSERT INTO tab2
    SELECT *
        FROM tab1
            WHERE day = 'Friday'")

Or
2.U can use m_strFilter Variable for tab1 which filters the records as UR condition and use it in while(!tab1.IsEOF()){
//Add to tab2;
using tab2.AddNew();
}
I think the First method is better.
Regards
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:
Sorry I forgot

use the same recordset to delete the records

Or use the SQL statement
DELETE FROM tab1 WHERE day = 'Friday'
0
eugene007Author Commented:
You guys did your best...and i really appreciate your help...be blessed...i did not know that i could also grade you guys.heheh....:).
0
eugene007Author Commented:
void CODBC6View::OnButton1()
{
  m_pSet->m_pDatabase->ExecuteSQL
  ("INSERT INTO [table2] SELECT * FROM  
  [table1] WHERE [Students_named]  
  = 'Ali'");
     
  m_pSet->m_pDatabase->ExecuteSQL
  ("DELETE FROM [table1] WHERE  
  [Students_named] = 'Ali'");
}

In this program i am inserting a record from table1 to table2. I am performing two operation at the same time. But it seems that when i execute this portion of the program the record from the table 1 is deleted but no records is being inserted into table2. I am not sure why is this problem accuring.
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
System Programming

From novice to tech pro — start learning today.