Solved

CDaoRecordset with SetParamValue()

Posted on 2003-10-27
6
463 Views
Last Modified: 2013-11-20
I would like to use a parameterized query with CDaoRecordset, using the members m_nParam and SetParamValue(). I have a query like

select * from TabX where FieldX = [ValueX] And FieldY = [ValueY]

where [ValueX] and [ValueY] are the parameters I would like to change at run time.

I am currently instantiating a CDaoRecorset child and using this query (with the actual values instead of parameter names) by calling CDaoRecordset::Open().

How do I write a parameterized query? How do I use SetParamValue() to set the actual values of the parameters?

Many thanks for your help.
0
Comment
Question by:agomes
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9632033
Try this

    CDaoDatabase db;
    CDaoQueryDef query( &db );

    try
    {
        db.Open( "C:\\DB1.mdb" );
        query.Open(NULL, "select * from TabX where FieldX = [ValueX] And FieldY = [ValueY]");
        query.SetParamValue( "FieldX", COleVariant( (long)nFieldX, VT_I4 ) );
      query.SetParamValue( "FieldY", COleVariant( (long)nFieldY, VT_I4 ) );

       query.Execute();
    }
    catch( CDaoException* e )
    {
        AfxMessageBox( e->m_pErrorInfo->m_strDescription,
            MB_ICONEXCLAMATION );
        e->Delete();
    }
    query.Close();
    db.Close();

Good Luck
0
 
LVL 1

Author Comment

by:agomes
ID: 9633363
Thanks roshmon, I will try it, but I need to use a CDaoRecordset. Can I just CDaoRecordset::Open( query ) and not query.Execute()?

Anyway, we are using CDaoQueryDef, and not the methods in CDaoRecordset. What these methods are for (m_nParam, SetParamValue(), GetParamValue())?

Thanks again.

0
 
LVL 23

Accepted Solution

by:
Roshan Davis earned 200 total points
ID: 9633400
You can use

CDaoRecordset::Open( CDaoQueryDef*,...)

like

        db.Open( "C:\\DB1.mdb" );
        query.Open(NULL, "select * from TabX where FieldX = [ValueX] And FieldY = [ValueY]");
        query.SetParamValue( "FieldX", COleVariant( (long)nFieldX, VT_I4 ) );
        query.SetParamValue( "FieldY", COleVariant( (long)nFieldY, VT_I4 ) );

CDaoRecordset oRS;
oRS.Open(&query);

Rosh :)

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

Assisted Solution

by:DanRollins
DanRollins earned 50 total points
ID: 9638524
The simplest way is to not use SQL parameters.  Just create the desired SELECT string at runtime:

        CString sSQL;
        sSQL.Format("SELECT * FROM TabX WHERE FieldX=%d AND FieldY= %d", nX, nY );
        crs.Open( AFX_DAO_USE_DEFAULT_TYPE, sSQL );

You can also place the WHERE clause separately into the m_strFilter variable:

        crs.m_strSort.Format( "FieldX=%d AND FieldY= %d", nX, nY );
        crs.Open();

-- Dan
0
 
LVL 23

Expert Comment

by:Roshan Davis
ID: 9639203
Yes thats the simplest. But parameter is needed when the query is executing several times with different condition values.
In that case Query parsing will occur once.
0
 
LVL 1

Author Comment

by:agomes
ID: 9819860
Many thanks roshmon, DanRollins, and sorry for the delay.

I wanted to use parameters to optimize the use of my CDaoRecordset, once I needed to change only the parameters from time to time, but I could not make it work, neither using MFC example, nor using roshmon's (maybe because of DAO version?), so I did opt to close and open the CDaoRecordset again (ugly, but it works).

It seems roshmon's answer is on the right direction, maybe it needs some adjustment or I am using a wrong DAO version.

Many thanks for your time.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
set up trigger (or something) to rename files (starting with ATL) in a linux directory 6 53
sameEnds challenge 3 134
zeroFront challenge 7 80
canBalance challenge 34 70
Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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