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 ;)
sllsglAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sllsglAuthor Commented:
Edited text of question
0
piano_boxerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sllsglAuthor Commented:
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
piano_boxerCommented:
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
sllsglAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.