Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

CDaoRecordset & SQL execution???

Posted on 1998-04-14
4
Medium Priority
?
338 Views
Last Modified: 2013-11-20
I have already had a CDaoRecordset TableSet using Access97 in VC++5 (WinNT).  How can I execute a SQL SELECT statement in this tableset?  I know that it is impossible to execute one in a tableset so can anyone guide me (in detail) on how to setup another Dynaset 'mirror' of the original tableset database so that I am able to execute SQL in?

Also, after executing a SQL statement, where can I access the extracted records data?   I simply wants to display & print the result after SQL execution, how should I do it? (If possible, using Access97 to do the display & print task, but must be able to be controlled in my application.)

Thanks alot :)
0
Comment
Question by:sllsgl
  • 2
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
naveenkohli earned 150 total points
ID: 1317689
Hi sllsgl,
I don't see any reason that you can not execute SELECT with ur DaoRecordSet.

CDaoRecordSet::Open ("*.mdb", SELECT * from yourdatatable,..) would do this for you. You can see the syntax in help.

As for as seeing and printing the data goes, thats not straight forward with DaoRecordset.
Follwing is a snipet from my code which i use to get parameters of a drawing object. The field value is returned in COleVariant. Then you have to translate that variable depending on the type returned by the GetFieldValue () function. e.g in the code below i am getting the field value of first filed in my datatable m_HoleParams.
The StrVARIANT () function translate the field value to CString format which i can use to print the results. I have also included a part of the translation code.
I hope this shuld solve your problem.
If you need complete code for this translation and stuff, let me know. I will be more than happy to help you out.
Have Fun Buddy.......


//
//
COleVariant varValue;

m_HoleParams->GetFieldValue (short (0), varValue);

// Depending on the type of hole, stuff the parameter
// dialog box entries

strValue = DaoTranslate::StrVARIANT (varValue);
//
//
CString
DaoTranslate::StrVARIANT(const COleVariant& var)
{
      CString strRet;
      strRet = _T("A3DS");
      switch(var.vt){
            case VT_EMPTY:
            case VT_NULL:
                  strRet = _T("NULL");
                  break;
            case VT_I2:
                  strRet.Format(_T("%hd"),V_I2(&var));
                  break;
            case VT_I4:
                  strRet.Format(_T("%d"),V_I4(&var));
                  break;
            case VT_R4:
                  strRet.Format(_T("%e"),(double)V_R4(&var));
                  break;
            case VT_R8:
                  strRet.Format(_T("%e"),V_R8(&var));
                  break;
            case VT_CY:
                  strRet = COleCurrency(var).Format();
                  break;
            case VT_DATE:
                  strRet = COleDateTime(var).Format(_T("%m %d %y"));
                  break;
            case VT_BSTR:
                  strRet = V_BSTRT(&var);
                  break;
            case VT_DISPATCH:
                  strRet = _T("VT_DISPATCH");
                  break;
            case VT_ERROR:
                  strRet = _T("VT_ERROR");
                  break;
            case VT_VARIANT:
                  strRet = _T("VT_VARIANT");

}
//not complete code
0
 

Author Comment

by:sllsgl
ID: 1317690
The database is already opened upon starting the program, do I have to close it first then open again with the SELECT SQL Statement?  
    m_pSet->Close();                             // Is there other param?
    m_pSet->Open("*.mdb", SQLStr);    

Does that means the data fields currently under my m_pSet handle is now the extracted records' data?   The current recordset is already the selected records database, no need to perform a "ReQuery"?

To move from record to record, I still have to move record manually to extract one field after the other, is that correct?  Or do I have an alternative to read the whole record in one go?

Thanks alot for your prompt help  ;)
I will grade u as soon as I have tried them out :)

PS. I will appreciate if u can email me your complete code for reference ;)
(xSeeLLence@singpost.com)
0
 
LVL 23

Expert Comment

by:naveenkohli
ID: 1317691
Regarding reading the whole data at one time....hmm.... thats not possible.
int size = m_pSet->GetFieldCount ();
for (int i=0; i < size; i++){
 m_pSet->GetFieldInfo (short (i), COleVariant value);
}

You don't have to close the data base. Just requery it. But make sure to check if you can requery it.

if( !m_pSet->CanRestart( ) ) {
            TRACE ("Can't Restart the data base!\n");
}
else {
      m_pSet->Requery( );
}

One thing is very imp. in Dao database use.
If your app. is DLL then make sure that you terminate the Dao worksoace before you ExitInstance () of DLL.

AfxDaoTerm ();

I can send you the complete code tomm. after making changing some stuff in that. Its proprietry code, so we have to take care of some technical issues in that.

Have FUn Buddy!!

0
 

Author Comment

by:sllsgl
ID: 1317692
I am now able to create a new dynaset to store the extracted records using the below:-
    CMsgSet result(m_pSet->m_pDatabase);  // Create another recordset from my original
    result.Open(dbDynaSet, SQLStr);   // SQLStr is my SQL SELECT/WHERE statement
    ...... processing records ....
    result.Close();       // After I have read all records

Do u think I am right?  I am able to extract the records I wanted.

A shocking news from my superior is that in WinNT, Access97 database is assigned a Administrator Privillege now, but after I have completed my project, he wants to assign the database to some user's privillege where only users with a valid password can get access to it.

 I never thought of that when I created the program, what should I do to allow his implementation?  I know something about passing a password in the Open() functions if the database is protected but my applications automatically opens the database access upon starting, I don't know where should I provide the password?  I don't seems to find any Open() functions besides the one I have added as stated above.  If I cannot provide the password, the database would have denied any access & my program would have crashed.

Help ... Help ... ;)  

Also, he seems to prefer ODBC very much as compared to DAO, stating that using ODBC, the program will not have to be changed when in the future he is going to replace Access database with Orcale Database.  Is he correct?  And is ODBC really simpler?

He seems wanted me to convert my DAO application to an ODBC ones, can I do that, how should I do it, is it very complex so much so that it will be easier to just rewrite the whole app?

Thanks for any info :)

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
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.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

885 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