Solved

CDaoRecordset Query

Posted on 1998-04-08
5
533 Views
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 :-
      m_pSet->Close();
      m_pSet->Open();
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 ;)
0
Comment
Question by:sllsgl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:sllsgl
ID: 1317565
Edited text of question
0
 
LVL 4

Accepted Solution

by:
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:

catch
{
    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());
 
    set.m_pDatabase->Execute(strSQL);
    long nDeletedCount = set.m_pDatabase->GetRecordsAffected();
    TRACE("%d records was deleted\n", nDeletedCount);
}
catch(CException* pe)
{
    pe->ReportError();
    pe->Delete();
}

--
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 );

0
 

Author Comment

by:sllsgl
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 ;)
0
 
LVL 4

Expert Comment

by:piano_boxer
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");
set.Open(dbOpenSnapshot);
[....]

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.

Ex:
// Open readonly table recordset
CMyRecordset set;
set.Open(dbOpenTable, NULL, dbReadOnly);
set.SetCurreentIndex("age");

// 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);
    set.MoveNext();
}
// Close recordset.
set.Close();

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);

[....]

set.Close();


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

0
 

Author Comment

by:sllsgl
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 :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
In this post we will learn different types of Android Layout and some basics of an Android App.
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.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

687 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