ADO Filter

I need to how to filter an ADO recordset for this value -
Field X ="test'11111'"
omrirAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mark2150Commented:
select from {table} where X='test''1111'

You have to double up on 's that appear as literals.

M

0
omrirAuthor Commented:
I know, but if I have more then one quotes then i does not work.

PS

I'm working with a local Recordset with the Filter mehtod.

0
tkuppinenCommented:
Make sure that record set is of type dynaset and then execute the follwing loop.

rs.MoveFirst
    Do Until rs("fieldname") = var
        rs.Move (1)
            If rs.EOF = True Then
                               
            Exit Do
            Exit Sub
            End If
    Loop
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

omrirAuthor Commented:
This is not the solution...

Thanks any way

Omri
0
Éric MoreauSenior .Net ConsultantCommented:
Use this:
    rs.Filter "x = '" & Replace("test'1111", "'", "''") & "'"
0
omrirAuthor Commented:
emoreau

This is very true, if you have only one quotes in your string, and does not work if you have more :)
0
Éric MoreauSenior .Net ConsultantCommented:
Do you have a function that is called Replace?

Here is what I get when running it from the Immediate Window.
? replace("1'2'3","'","''")
1''2''3

Maybe you can try to use VBA.Replace to be sure to use this function.
0
omrirAuthor Commented:
The problem is not the replace but the Filter method in ADO , it returns an error for this kind of string
0
Éric MoreauSenior .Net ConsultantCommented:
Could you paste here your Filter line so that we can see!
0
mark2150Commented:
You have to double up on 's in your string. If you have two, you need FOUR, etc.

M

0
10pointCommented:
Use this:
 rs.Filter="X="+Chr(34)+"test"+Chr(39)+"11111"+Chr(39)+Chr(34)


0
mark2150Commented:
Code a function like this:

Public Function UnQuote(txt as String) as String
'
if len(txt) < 1 then exit function
'
dim work as string
dim achar as string
dim ndx as integer
'
work = ""
for ndx = 1 to len(txt)
  achar = mid(txt, ndx, 1)
  if achar = "'" then achar = achar & "'"
  work = work & achar
next ndx
'
UnQuote = work
'
End Function

Use this when building your SQL:

sql = "select from {table} where X='" & _
       unquote("test''1111") & _
       "'"

M

0
omrirAuthor Commented:
10Points

The problem is not VB syntax but ADO, ADO returns an error if you try to filter a string that has more then one quotes.


0
omrirAuthor Commented:
This is an ADO question!!!

0
mark2150Commented:
Code given works for me.

M

0
omrirAuthor Commented:
what version of ado are you using?
0
Éric MoreauSenior .Net ConsultantCommented:
Mark answer will work very well for regular query string to build your recordset.

But what you want from the beginning is to use the Filter property.

After some tests, I finally got it. You are right, Filter can't accept more than one quote in the searched expression. So I wrote this small function:
Private Function ReplaceForFilter(ByVal pstrField As String, pstrFilter As String) As String
Dim intPos As Integer
Dim strTemp As String

    intPos = InStr(1, pstrFilter, "'")
    If intPos = 0 Then
        ReplaceForFilter = pstrField & " = '" & pstrFilter & "'"
    Else
        strTemp = Left$(pstrFilter, intPos) & Mid$(pstrFilter, intPos)
        intPos = InStr(intPos + 2, strTemp, "'")
        If intPos = 0 Then
            ReplaceForFilter = pstrField & " = '" & strTemp & "'"
        Else
            ReplaceForFilter = pstrField & " LIKE '" & Left$(strTemp, intPos - 1) & "%'"
        End If
    End If
End Function

The first parameter is the field you want to filter for. The second parameter is the text you are looking for.

Use it this way (copied from the Immediate Window):
? frmdataenv.ReplaceForFilter("X","Test123")
X = 'Test123'
? frmdataenv.ReplaceForFilter("X","Test1'23")
X = 'Test1''23'
? frmdataenv.ReplaceForFilter("X","Test1'2'3")
X LIKE 'Test1''2%'

This is the nearest you can have. Since you can't have more than one quote in your searched text, use the Like operator instead of the = operator in those cases!


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omrirAuthor Commented:
emoreau

First of all I'm glade that someone got the problem.
Your answer, I already know, I thought maybe this was not a
Microsoft bug. I guess I underestimated myself.

I will give the points for the effort :)

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.