Using ADO recordset Find method

[gorthims]

Hi VB gurus

How can i use Find method of Recordset object in ADO.
I can do that through DAO using FindFirst,FindNext,FindLast and FindPrevious methods.
These methods are not there in ADO.
Please help me

[Nina]

try using something like this:


Dim SearchNumber as Integer
Dim SearchString as String

With Recordset
.MoveFirst

'if you're searching for a number
.Find "ColumnName=#" & SearchNumber & "#"

'or for a string:
'.Find "ColumnName=' & SearchString & "'"

If Not .EOF Then

'a record was found
'do something

End If
End With


hope this helps!

[gorthims]

ThanQ Nina

What you specified is for simple searching.

suppose i am having a table Orders. which contains a field Customer_name.In that field there is the repetetion of each customer name since one customer can place many orders.
Now i want searh for records based on aparticular customer name. According to you i will move to the first record and
execute
rs.find customer_name='xxxxx'.

The record pointer moves to the first existance of the customername.
now i want to move to the second existance of the same customer.(in DAO i can achieve this using
rs.findnext cust_name='xxxxx')
how can i do this using ADO.

also if the record pointer is at third existance of the customer_name 'xxxxx'.Now i want to move to the second existance.(Using DAO i can do this using
Rs.FindPrevious customer_name='xxxxx')
How can i do this Usin ADO.

Please find the solution for this
Thanks in advance.

[Edneeis]

If you use
ADO.recordset.Filter="CustomerName LIKE '" & varCusName & "'"
it will return all the records that met the criteria if you need more criteris just use AND in it. varCusName would just be a variable or control with the string of what to search for.
Or change the recordset to be a SQL statement
Set Recordset="Select * From CustomerOrders WHERE CustomerName=". ... yada yada
Read the help topics for Filter and SQL and see if it is what you are looking for.

[Nina]

If you want to get several records as a result I would create a new recordset with just these records ...
I think that's the easiest way ...

e.g.
StrSQL = "SELECT * FROM Table WHERE Customer_Name=" & SearchString

[gorthims]

That's OK.
You are all sugesting alternatives.
But There comes a situation where you have to scroll through the recordset that contains all the record inthe table.
I don't want to fire a recordset for each criteria every time.

Please find the solution for my problem( No alternatives please).

[Nina]

Well, if you want to go through all records in a recordset you can use

movefirst
movelast
movenext
moveprevious

is that what you are looking for?

[gorthims]

No Nina

You know FindFirst,FindNext, FindPrevious, FindLast in DAO. Right?
I want the Equivalents in ADO.

Now tell me the solution

[Kevin Swanson]

I think what you want to do is possible in ADO and pretty simple.


FindFirst:
rs.MoveFirst
rs.Find Criteria, 0, adSearchForward

FindNext:
rs.Find Criteria, 1, adSearchForward ' The 1 tells it to skip the current record.

FindPrevious:
rs.Find Criteria, 1, adSearchBackward

FindLast:
rs.MoveLast
rs.Find Criteria, 0, adSearchBackward


Look at the MSDN article on Find Method (ADO), but in the syntax section ignore the parentheses.

I haven't used this much, but I think it will work for you.

[Wally Pipp]

can't you use the .index and .seek methods ?

set the index first and then seek with parameters.

Or filter the results out as stated above.

[Kevin Swanson]

.index and .seek are DAO Methods not ADO.

You could use .Filter then use the .MoveFirst, .MoveNext, .MovePrevious, .MoveLast to move around in the records that are selected.

I prefer to use .Find if I am looking for 1 record and searching only one field. For anything complicated .Filter works better.