[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

ADO Filter

I need to how to filter an ADO recordset for this value -
Field X ="test'11111'"
0
omrir
Asked:
omrir
  • 8
  • 4
  • 4
  • +2
1 Solution
 
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
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.

 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now