[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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

0
eugene007
Asked:
eugene007
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now