asked on

CDaoRecordset Query

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

sllsgl

ASKER

Edited text of question

ASKER CERTIFIED SOLUTION

piano_boxer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sllsgl

ASKER

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

piano_boxer

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.

sllsgl

ASKER

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