Solved

CDaoRecordset & SQL execution???

Posted on 1998-04-14
4
289 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 50 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
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.
This video discusses moving either the default database or any database to a new volume.

707 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

11 Experts available now in Live!

Get 1:1 Help Now