ADO Recordset Filter using IN Clause

Hi
I am using Microsoft ADO Object library 2.5 in my Visual Basic project. I am facing the problem using filter method when i am specifying IN clause. If ADO filter supports IN clause then can u please send some examples.

   my code is something like this:
   
    rs.Filter = "ID in (22,23,25)"

where ID is number field.

LVL 1
s_monaniAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
_lv_Connect With a Mentor Commented:
First you should carefuly read ADO documentation (Filter property).
There you will find, that 'Filter' clause is in the form FieldName-Operator-Value, where:

  - FieldName must be a valid field name from the Recordset. If the field name contains spaces, you must enclose the name in square brackets.

  - Operator must be one of the following: <, >, <=, >=, <>, =, or LIKE.

(according to ADO2.8)

Guess this is the answer. The answer is NO (YES, "Filter property does not support IN clause").
Consider using suggested ordinary SQL query.
0
 
Bob LambersonSoftware EngineerCommented:
rs.Filter = "(((ID)=22 Or (ID)=23 Or (ID)=24))"

Bob
0
 
s_monaniAuthor Commented:
The list which i want to compare is very long. Not possible with an OR operator which is y i want to use an IN clause
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DhaestCommented:
Perhaps you should consider then closing your recordset and rebuild the sql-query

rs.close
strSql = "Select * from YourTable where ID in (22,23,25)"
rs.Open strSql, YourConnection, adOpenDynamic, adLockPessimistic
0
 
s_monaniAuthor Commented:
I dont want a where clause. i want to incorporate IN clause using recordset.filter
0
 
Bob LambersonSoftware EngineerCommented:
Mine and Dhaest are the only two options. I would use Dhaest's idea. The filter option is slow by comparison to the sql option. especially if you have a lot of conditions.
Is there some reason you are not stating that you don't want to use the sql method?

Bob
0
All Courses

From novice to tech pro — start learning today.