• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

ExecuteSQL statment.

CDatabase db;

db.Open("db1", FALSE, FALSE, "ODBC;",
TRUE );

db.ExecuteSQL("INSERT INTO [table2] SELECT * FROM [table1] WHERE [table1].[Students_named] = 'Anjing'");
      
db.ExecuteSQL("DELETE FROM [table1] WHERE [table1].[Students_named] = 'Anjing'");

db.Close();


My question is :

Is it possible for me to to get the number of records from a table based on a criteria and then use that value to updata the record found in another table. Is it possible to do that like the sample above using ExecuteSQL statement.

Example :

Table A has 10 records.

Table B has 1 row that keeps track of the number of record in Table A within a single row.


Regard

Eugene

0
eugene007
Asked:
eugene007
1 Solution
 
vachoohoCommented:
CRecordset rs(&db);
rs.Open(dbOpenSnapshot, "SELECT Count(*) FROM Table1 WHERE Field1='Value1'");
CString strValue;
rs.GetFieldValue(0, strValue);
rs.Close();

COleVariant var(strValue);
var.ChangeType(VT_I4);
long nCount = var.lVal;

// you have nCount = count of records with specified criteria

0
 
pagladasuCommented:
To be very specific, you cannot do this using the ExecuteSQL command. The online help has this to say:
'ExecuteSQL does not return data records. If you want to operate on records, use a recordset object instead.'

0
 
V_BapatCommented:
db.ExecuteSQL("INSERT INTO [table2] SELECT Count(*) FROM [table1] WHERE [table1].[Students_named] = 'Anjing'");

I am not sure but should work.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
eugene007Author Commented:
Hi vachohoo...this part of the program keps giving me an error that is
ambiguous call to overloaded function.
How do i rectify this problem.

rs.GetFieldValue(0, strValue);
0
 
eugene007Author Commented:
Reminder :

I am using ODBC as my interface and not DAO..:)
0
 
vachoohoCommented:
GetODBCFieldValue(long(0), strValue);
0
 
eugene007Author Commented:
Thanks you vachooho....i am able to solve the problem with the simple tip GetODBCFieldValue(long(0),StrValue);.
I would also like to thank the rest of you who has helped me in solving the problem as well as adding my knowledge too.

Regards

Eugene
 

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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