[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


CDaoRecordset Query

Posted on 1998-04-08
Medium Priority
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 120 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
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.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

873 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