Search Crashing 1

[Trevahaha]

I was noticing that I have search dialogs throughout a database and if someone enters single-quote mark, ' , it crashes the code. Is there a way to maintain the search ability but prevent crashing?

I think it involves split and replacing ' with \' .. but are there other things I should be aware of (other ways that user may break it)?

Trevor

 

[RickSpr]

You wouldn't replace ' with \' - that's a C or C++ convention. In Visual Basic, you replace ' with '' (That's two apostrophes, not one quote).

You get this problem when the value of the field into which the users enter data is incorporated into a text literal in an SQL statement, where the text literal is enclosed in apostrophes. One solution is to enclose the literal in quotes instead of apostrophes (either is accepted in Jet SQL), but that just switches you to having a problem if the user keys a quote character.

Doubling apostrophes within the text entered by the user is the reliable solution. I usually use a function like the following:

    Public Function DoubleCh(Value As String, Char As String) As String
        Dim i As Integer, result As String

        result = Value
        i = 1
        Do 
            i = InStr(i, result, Char)
            If i = 0 Then Exit Do
            result = Left$(result, i) & Mid$(result, i)
            i = i + 2
        Loop
        DoubleCh = result
    End Function

Use this function when building your SQL statement. For example,
Const Apost = "'" ' An apostrophe in quotes
SQL = ... & "WHERE CustName=" & Apost & DoubleCh([field], Apost) & Apost

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Trevahaha]

Thanks Rick! Great explanation... yeah got confused w/ my ole' C++ days Thanks for the great function.

 

[Trevahaha]

ooohh.. yeah, but the second part. Let's say someone enters a " into the field... ?

 

[Trevahaha]

n/m - i guess " " work.. hehe

 

[RickSpr]

Whoa! If you are using the apostrophes as the literal delimiters, quotes within the literal have no syntactic significance, and vice versa. So don't double the quotes.

I wasn't sure if you thought you needed to double both apostrophes and quotes.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Trevahaha]

Oh no... I don't. I was thinking that using a " in the string (if a user put that in for some reason) caused it to crash as well, but I'm wrong, it doesn't. The DoubleCH(string, "'&quot works great - no more crashing!

Trevor