Searching using ADO

How do I search a database with ADO? It seems that there are no methods for searching when an assigned recordset is an ADO. Will I just do a looping statement and manually search for a particular record or something? Pls help...thanks! =)

[preeti]

Hi,

Ado Recordsets have a find method:

Find (criteria, SkipRows, searchDirection, start)

Look in the MSDN Library for more information on this method.

Or you could even use a query to return the record that you want:

SELECT * FROM tablename WHERE field=criteria.

HTH,

Preeti

[scottr]

To continue if u do write sql statements the format is usually as follows. Also if you are using dynamic sql make sure to put the variable in quotation marks or you might get an error message from the database. If you want a list of books i would be happy to give it to u.


SQLStmt = "select distinct SOMETHING "
SQLStmt = SQLStmt & "from DATABASE_TABLE"
SQLStmt = SQLStmt & " where SOMETHING = " & "'" & VARIABLE & "'"

[Blue Ghost]

The only problem you have is that if you're looking for data that has a ' character in the string, then you run into a problem. If you put quotes (chr$(34)) around your variable, this will work on the SQL SELECT only if you're using a Jet Database. If you're using SQL Server, it will fail. It won't work in wither case with the Find method. At least not on my system. I really would like to know a way around this problem. I have a database (Access '97) that has fields that have MULTIPLE ' characters and I can't look for those fields becuase of it.

There is no problem using ADO to read the data, as long as I don't try to "Find" a record with the ' chars in it.

[JHausmann]

Blue ghost, have you tried

sTmp = """" & variable & """"

sSQL = "select * from table where search=" & sTmp

[Peter Tribe]

Here follows a function that will solve all your quote problems...

Public Function SingleQuotes(InString As String) As String

Dim X As Long
Dim Y As Long
Dim work As String

work = InString
Y = 1

Do
X = InStr(Y, work, "'")
If X = 0 Then
Exit Do
Else
work = Left(work, X) & "'" & Mid(work, X + 1)
Y = X + 2
End If
Loop

SingleQuotes = work

End Function

Use the function like so...

SQLStmt = "select distinct SOMETHING "
SQLStmt = SQLStmt & "from DATABASE_TABLE"
SQLStmt = SQLStmt & " where SOMETHING = " & "'" & SingleQuotes(VARIABLE) & "'"

Well hope this is of use to you.

Back to work

[Baco]

I need to use the find method to navigate through the records. I therefor use the adodc.recordset.find "field >" & "'" & md(Text1.Text) & "'".

This works fine if I use all kinds of symbols like ' , " , #, & BUT it doesn't work for searching textstrings! Note : the md function is a function I made to get rid of the problems with ' and " symbols.

The output I get when I type 'a' (without quotes, of course) as text1 is the record which begins with 'a. How can I get rid of the ' ?

Thanks in advance,

Bart