ExecuteSQL statment.

CDatabase db;

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

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


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.



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.

CRecordset rs(&db);
rs.Open(dbOpenSnapshot, "SELECT Count(*) FROM Table1 WHERE Field1='Value1'");
CString strValue;
rs.GetFieldValue(0, strValue);

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

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

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.'


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
db.ExecuteSQL("INSERT INTO [table2] SELECT Count(*) FROM [table1] WHERE [table1].[Students_named] = 'Anjing'");

I am not sure but should work.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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);
eugene007Author Commented:
Reminder :

I am using ODBC as my interface and not DAO..:)
GetODBCFieldValue(long(0), strValue);
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.



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.