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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

CDaoRecordset with SetParamValue()

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
agomes
Asked:
agomes
  • 3
  • 2
2 Solutions
 
Roshan DavisCommented:
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
 
agomesAuthor Commented:
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
 
Roshan DavisCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DanRollinsCommented:
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
 
Roshan DavisCommented:
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
 
agomesAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now