Problem With String Searching Having Apostophi In VB6

Belal

Hi Dk,

I need your help again. In VB6 apostophi is used to make a statement comment. So, if I like to search a word like "John's pen", It creates an error for apostophy sign. How can I get rid from it. Or, I should not allow user to enter apostophy sign in search text box (I think it won't be great idea). If I like to separate my string depending on apostophi will it work. What will be better idea from your point of view. You know "Cox's Bazar" string contains aphostophi. So, to search something on this area is causing trouble for me.

Waiting for your quick reply.

belal
_________________
we've lot of things to think abt curr probs
so, i don't have time to think abt religion or wonder of sceice .......... how ppl can waste their time like this?

hasnut · Expert User Joined: 28 Aug 2004 Posts: 201

It should not be a problem in only VB string manipulation. But if you are querying in database then you can use Chr(39). I hope you are having problem in sql statement.
_________________
Sarder Hasnut
MCSD, CIW A

Need Low Cost Prefessional Hosting Contact me

Belal

Thanks hasnut for your reply.

Would you plese clearify the following line in detail.

Quote:

But if you are querying in database then you can use Chr(39).

Hope get you again.
_________________
we've lot of things to think abt curr probs
so, i don't have time to think abt religion or wonder of sceice .......... how ppl can waste their time like this?

dinangkur

How to search for a name like "Cox's Bazar" from within SQL? The problem is that the single quote character is used to delimit strings within SQL. The solution is to search for two single quotes. If you put this in your query, your database will translate those two single quotes into a single quote that it needs to find. This is a common problem in SQL Server, Oracle, and many other databases.

Have fun.

-DK.
_________________
...we too are stardust...

dinangkur

I also like to say few other things which is also important. Allowing free text fields to be written directly to databases is a potential minefield due to the opportunity for these fields to contain illegal characters such as single quotes, double quotes, percentages, asterisks, etc. This is because these characters can effect the context of the query. To guard against this problem, the easiest solution is to strip out all occurrences of such characters. The function below searches for all these characters and by replacing them with nothing, removes them.

Code:

Function strReplaceSQL(strTxt)

Dim strIllegalChar
Dim strChar
Dim intPos
Dim intCount

If strTxt = "" then Exit Function

'double quote, percentage, single quotes, asterisks
strChars = chr(34) & chr(37) & chr(39) & chr(42)

'loop through illegal chars
For intCount = 1 to Len(strChars)
strIllegalChar = Mid(strChar, intCount, 1)
intPos = Instr(strText, strIllegalChar)
Do While intPos > 0
'remove illegal chars
strTxt = Replace(strTxt, strIllegalChar, "")
intPos = Instr(strText, strIllegalChar)
Loop
Next

strReplace = strTxt

End Function

-DK.
_________________
...we too are stardust...

hasnut · Expert User Joined: 28 Aug 2004 Posts: 201

Belal wrote:

Thanks hasnut for your reply.

Would you plese clearify the following line in detail.

Quote:

But if you are querying in database then you can use Chr(39).

Hope get you again.

DK answered with code. look at his reply.
_________________
Sarder Hasnut
MCSD, CIW A

Need Low Cost Prefessional Hosting Contact me

Belal

Thanks Dk for your nice suggesstion. And also for helpful code. I will let you know about your solution withing few days if I still feel any problem.

Right now enjoy...

_________________
we've lot of things to think abt curr probs
so, i don't have time to think abt religion or wonder of sceice .......... how ppl can waste their time like this?