MFC CRecordset::m_strFilter: Can I use SQL "LIKE" [%] in here?

[Denzil]

Hi,

Can I use SQL LIKE condition in the m_strFilter of my RecordSet?

I am setting the filter datamember as

<code>
rsSampleRS.m_strFilter = "Number = '123'";
</code>

But what I intend is to have a SQL "LIKE" in the statement as;

All records from the table in where "123" is part of the value in the Number column.

Any pointers/ help will be appreciated.

Thanks for your time.

Cheerz,
Denzil

[KS]

If you could ... I don't think you'd want to. From what I remember, the
filter only gets applied after the query is run. So if your table has
100,000 rows, it queries all 100,000 and loops through to apply the filter.
So if you can, I would just make it part of the "WHERE" clause.

KS

"Denzil" <denzil...@lycos.com> wrote in message
news:298554ad.04061...@posting.google.com...

[Roy Fine]

Denzil,

you can not usethe m_strFilter - because the CRecordset prepends the WHERE
keyword to your string, then append the lot to the end of the SQL Select
statement.

there is nothing to prevent you from replacing the entire SQL Select
statment that the recordset uses, and include the WHERE ... LIKE 'xx?xx_'
clause - you just have to keep the columns selected in your SQL Select
statement the same as what the RFX macros are expecting

regards
roy fine

p.s. The WHERE clause is applied a rowset selection in the Engine that is
processing the SQL Statement - the recordset sees only the filtered results,
not all of the row, then to apply the filter to!.

"Denzil" <denzil...@lycos.com> wrote in message
news:298554ad.04061...@posting.google.com...

[Tim]

Hi,

Firstly the filter is not applied after the recordset is opened as per KS'
comments. As Roy points out the CRecordset class composes an SQL string
using GetDefaultSQL, the DataExchange routines, and the m_strSort and
m_strFilter values that are current at the tim e of Open or Requery.

Its basically like this - it is worth stepping through all the code to see
how it goes.

sql = "SELECT "
for each column in DoFieldExchange
sql += columName + _T(", ")
end for

get rid of trailing comma

if (m_strFilter <> "") then
sql += "WHERE "
sql += m_strFilter
end if

if (m_strSort <> "") then
sql += "ORDER BY "
sql += m_strSort
end if

it then uses the composed sql string.

So, to use a LIKE clause your filter statement has to be valid when appended
onto "WHERE "

IE something like this would work

m_strFilter = _T("Surname LIKE Smith%");

but this wouldn't

m_strFilter = _T("LIKE Smith%");

The functionality is richer than that of course, so again it is worth
looking through the code.

- Tim

"Denzil" <denzil...@lycos.com> wrote in message
news:298554ad.04061...@posting.google.com...

[Tim]

Tut tut, I forgot some embedded quotes...
- Tim

"Tim" <Tim@NoSpam> wrote in message
news:O9eHG0N...@TK2MSFTNGP09.phx.gbl...

[Denzil]

Dear Tim, Roy and TS,

Thanks a ton for your replies. Taking the lead from there, I got what
I set out to do with the "LIKE".

Cheerz,
Denzil

[KS]

Tim,

Actually it does ... depending on which objects you use ... If you're using
the ADO recordset object and open a table, then apply the filter, it does
indeed query twice ... Not sure if CRecordset behaves the same way ...

See this article:

http://17.webmasters.com/caspdoc/html/ado_recordset_object_filter_property.htm

KS

"Tim" <Tim@NoSpam> wrote in message
news:O9eHG0N...@TK2MSFTNGP09.phx.gbl...

[KS]

Unless maybe the getrecordcount is forcing it to get all the data ... dunno
...

"KS" <k...@blah.com> wrote in message
news:u8mVxTUV...@TK2MSFTNGP09.phx.gbl...