SQL Query String


I have this problem.

when i made a SQL string query example:

set rst = new recordset
rst.open "select * from table where field = 'ABC'", ConnStr, adOpenStatic, adLockReadOnly

Note that the criteria "ABC" is retrieved from user input. The qns is, if the user input "AB'C", my query string will have error as the string will end till "AB" and the other statements will be invalid.

How am i going to resolve this?
Is there any other special characters which will caused this problem?

Pls help!
Who is Participating?
TimCotteeConnect With a Mentor Commented:
You just need to double the ' character:

MyString = "AB'C"
rst.Open "Select * From Table Where Field = '" & Replace(MyString,"'","''") & "'",ConnStr,adOpenStatic,adLockReadOnly

Ryan ChongCommented:
Hi edonasan, TimCottee is correct.

Here is an alternative sample , use it as you use VB6 lower:


Private Function Convert(ByVal Keyword As String) As String
   EmptyM = Keyword
   Do While InStr(EmptyM, "'") <> 0
       i = InStr(EmptyM, "'")
       EmptyM = Right$(Keyword, Len(Keyword) - i)
       Keyword = Left(Keyword, i) & "'" & EmptyM
   Convert = Keyword
End Function

Use like this:
MyString = "AB'C"
rst.Open "Select * From Table Where Field = '" & Convert(MyString) & "'",ConnStr,adOpenStatic,adLockReadOnly
edonasanAuthor Commented:
thanks TimCotte!

Thanks ryancys too, i hope to allocate some points to u too.. thanks again :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.