Solved

SQL Query String

Posted on 2001-06-27
3
226 Views
Last Modified: 2010-05-02
Hi,

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!
0
Comment
Question by:edonasan
3 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
Comment Utility
You just need to double the ' character:

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

0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
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
   Loop
   Convert = Keyword
End Function

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

Author Comment

by:edonasan
Comment Utility
yes!
thanks TimCotte!

Thanks ryancys too, i hope to allocate some points to u too.. thanks again :)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now