CDaoRecordset Query

Posted on 1998-04-08
Last Modified: 2013-11-20
(1)  m_pSet is my application CDaoRecordset handle in {CMsgSet}.
     m_pSet->m_pQueryDef=NULL // ??? How to set this up ???

     I noticed I always get an assertion error due to this m_pQueryDef being NULL, how do I make it valid?

"    COleVariant pSrc;
     pSrc=m_pSet->GetParamValue("[MMS Event Logging].TagID");   // Assertion Error m_pQueryDef=NULL !!!
     pSrc.ChangeType(VT_BSTR);   // Is the way to change COleVarient type to character strings ???
     fputs(fsql, pSrc);                              "

(2) To requery a Table Type CDaoRecordset, I put the WHERE statement of the SQL statement in  m_pSet->strFilter, & the ORDER BY statement in m_pSet->strSort.  Correct ???  
      (m_pSet->m_strFilter)="(([MMS Event Logging].TagID)=\"BBC\")";
      (m_pSet->m_strSort)="[MMS Event Logging].Date";
I cannot use Requery() coz I am using a Table Type Recordset, so I simply do this :-
Is that enough?  Do I need to do something like CanRestart() to determine if I can close & open the database connection?

How can I check if the SQL is executed correctly?  Does GetRecordCount() tell, since after SQL execution, not all records are extracted, right???

Initially before I set m_strFilter & m_strSort up, they are both empty fields.  Does that means all records are selected without sorting order ???

(3) Is there any way to use Access Active-X control in VC++?  Like in VB, we can simply use GetObject() to obtain the Access Object handle & then we are able to use the exisiting Access functions like printing & querying, etc.
Pls give me examples for printing for illustration if u know how.

Thanks alot ;)
Question by:sllsgl
  • 3
  • 2

Author Comment

ID: 1317565
Edited text of question

Accepted Solution

piano_boxer earned 40 total points
ID: 1317566
Well I'm not sure that i understand what you a trying to do, but here are som hints:

When using a tabledef recordset records are not filtered/sorted based on m_strfilter/m_strSort. You will need to use a index, which will determine the sort order. And note: Tabledef recordset gives you ALL THE RECORDS in the table, but are the fastest way to read/write records.

You should use Seek() to find a specific record and the MoveNext() to get to the next record (sorted based on the index selected). To select a index use the function SetCurrentIndex().

Using table recordsets to count records is not a good practice (They uses a estimated count of records, and are only 100% correct after a compacting the database). Use a dynaset when counting records.

To execute a SQL build your SQL command string and call CDaoDatabase::Execute(strSQL). If the SQL command(s) fails a CDaoException will be raised.

Ex: To delete alle records older than 1-1-97 in table 'data' and get the number of records actually deleted:

    CTime tm(1997, 1,1, 0,0,0);
    CString strSQL;
    strSQL.Format("DELETE data.* FROM data WHERE (time < #%d/%d/%d#)", tm.GetMonth(),tm.GetDay(),tm.GetYear());
    long nDeletedCount = set.m_pDatabase->GetRecordsAffected();
    TRACE("%d records was deleted\n", nDeletedCount);
catch(CException* pe)

Another thing:
There is no need to requery a table recordset it is updated automatecly when new records are added or deleted. To make sure all changed has been read do the following:

set.m_pWorkspace->Idle( dbRefreshCache );


Author Comment

ID: 1317567
Thanks Piano_boxer,  I need more explanation, so hope u don't mind I reopen this question :)

First, u say that m_strFilter & m_strSort do not filter & sort in a tabledef recordset, so what's their uses?  Aren't them the substitute for SQL clauses?  

I have built right from the start using AppWizard, & selected Access as the DAO, & I am assigned Tabledef, so how do I execute this "set.m_pDatabase->Execute(strSQL); " ?
This Execute() is the class member of CDaoDatabase, not CDaoRecordset, how can I use it?

Also I don't have a handle for CDaoDatabase (or  perhaps I may have, right under several covers of CDaoRecordset handle m_pSet, pls guide ?)

And if I can execute, so how can I get to the selected (affected) records, how & where are they returned?  Do I have to extract individual field from individual record, one by one? Most importantly, I noticed that GetFieldValue() always return COleVariant type, how can I change it to CString or COleDateTime for display?

U also mentioned that there is no need for refresh in tableset database, but how about after a SQL query?

Million Thanks ;)

Expert Comment

ID: 1317568
1. m_strFilter and m_strSort are used to filter and sort records in a dynaset or snapshotset (readonly).

Ex: If u want to open a recordset with only records where [age] > 10 you could use m_strfilter to select only those records:
set.m_strFilter = _T("[age] > 10");

Internally MFC actually builds a SQL select string based on GetDefaultSQL() and m_strFilter and m_strSort.

2. To get to the CDaoDatabase object from your CDaoRecordset class use the member variable m_pDatabase in the CDaoRecordset. (MFC automaticly create a database object for you when you opens a recordset, without specifying you own CDaoDatabase).
So when i write set.m_pDatabase->Execute(strSQL), set is your CDaoRecordset derived object.

3. Table recordset are a kind of 'raw' access to all the records i a table. It gives you fast indexed scrolling via the Seek() function, but dont give you any way of filtering out unwanted records.

// Open readonly table recordset
CMyRecordset set;
set.Open(dbOpenTable, NULL, dbReadOnly);

// Seek to first record where [age] < 10
COleVariant varAge((int)10);
set.Seek("<", &varAge);

// Walk through and print all records where age < 10
while(!set.IsEOF() && set.m_nAge < 10)
    TRACE("Record found. (age = %d)\n", set.m_nAge);
// Close recordset.

4. If you whant to get a result from executing a SELECT SQL query, construct your SQL command and pass it to CDaoRecordset::Open();

CMyRecordset set;
set.Open(dbOpenDynaset, strSQL);



5. You cant run SQL querys on a tableset type recordsets !!!. Use snapshot or dynaset instead.


Author Comment

ID: 1317569
Thanks again for your prompt reply ;)
I will try out your suggestions as soon as possible.  BTW, do u mean that I must definitely open another recordset(dynaset) from my current tableset, in order for me to execute SQL?  Then the extracted records will be in the newly opened dynaset?  And I still have to extract the fields of the selected record one by one?

Can u guide me along on how to open another dynaset from my current tableset (m_pSet)?

Million Thanks :)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Question regarding Copy/Paste 16 98
viewing source code from eclipse 13 110
zeroMAx challenge 20 127
sumHeights  challenge 17 80
This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question