Solved

CDaoRecordset Query

Posted on 1998-04-08
5
500 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
  • 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ADO Memory leak with DELPHI 2007 37 155
WinWaitActive parameters 12 31
Smart Camera scanning and reading information 3 86
Problem to event 3 50
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…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now