Solved

Apostrophy in SQL string

Posted on 2002-04-05
12
177 Views
Last Modified: 2010-05-02
My sql string includes a field in the WHERE clause which contains descriptions which include apostrophies. The apostrophies are causing errors because they are interpreted by sql as a termination. Apart from changing the structure and using an internal code in the WHERE clause, if I detect the apostrophies how can I then allow for them in the sql?

eg:
CurrentCustomer = "John's"
WHERE Account = '" & CurrentCustomer & "'
0
Comment
Question by:nickwoolley
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 6920390
Hi,

Try to use Replace function in VB:

Example:

CurrentCustomer = Replace$(CurrentCustomer,"'","''")
WHERE Account = '" & CurrentCustomer & "'

Cheers
0
 
LVL 3

Accepted Solution

by:
Gunsen earned 50 total points
ID: 6920395
Use "
... WHERE Account ="John's"

If InStr(CurrentCustomer,"'") Then SQL = "WHERE Account=" + Chr$(34) + CurrentCustomer + Chr$(34)
Else SQL = "WHERE Account='" + CurrentCustomer + "'"
0
 
LVL 1

Expert Comment

by:gondesiravi
ID: 6920457
Before passing the data to SQL call this function for detecting ' and replace it with '' and then transfer it to SQL

function FormatText(StrFieldVal as string) as String
' Format Apostrophes For SQL Statement
Dim ChrPos , PosFound
Dim WrkStr
For ChrPos = 1 To Len(StrFieldVal)
           PosFound = InStr(ChrPos, StrFieldVal, "'")
        If PosFound > 0 Then
        WrkStr = WrkStr & Mid(StrFieldVal, ChrPos, PosFound - ChrPos + 1) & "'"
                 ChrPos = PosFound
             Else
                 WrkStr = WrkStr & Mid(StrFieldVal, ChrPos, Len(StrFieldVal))
                 ChrPos = Len(StrFieldVal)
             End If
         Next
         FormatText= WrkStr
End Function
0
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 5

Expert Comment

by:kcm76
ID: 6920726
Assume that u have a FieldValue John's.
In oracle "?" replaces any one character
and ur sql would be "John?s". but u cannot use "=" Operator u have to only use "Like".

Example:
Select * from ....... Where CurrentCustomer like 'John?s'


Using Instr or Other functions u can build ur query in vb.

Try this.

ALL THE BEST
KCM
0
 
LVL 5

Expert Comment

by:kcm76
ID: 6920731
"?" is for one character substitution. Thus it can match both Pray and Play for 'P?ay'.




0
 
LVL 18

Expert Comment

by:mdougan
ID: 6920812
This points to a larger issue though.  The single quote, while the most common, is not the only character that will give you problems.  The only way I've found to totally eliminate concerns about embedded characters in field values in SQL is through using ADODB Command Objects with Parameters.  It takes a bit more code, but you can create procedures to hide most of the extra code.  Here is what your example would look like (assuming you've already opened CN the connection object)

Dim CMD as ADODB.Command
Dim Parm1 As ADODB.Parameter
Dim RS as ADODB.Recordset

Set Parm1 = New ADODB.Parameter
Parm1.Direction = adParamInput

' needs to match column definition in database
Parm1.Type = adVarChar
Parm1.Size = 50

Parm1.Value = Text1.Text ' could include John's


Set CMD = New ADODB.Command
CMD.ActiveConnection = CN
CMD.CommandType = adCmdText
CMD.CommandText = "SELECT * FROM CUSTOMERS WHERE CUSTOMERS.ACCOUNT = ?"

CMD.Parameters.Append Parm1

Set RS = CMD.Execute

(or is that just RS = CMD.Execute)

If RS.State = adStateOpen
   If Not RS.EOF and Not RS.BOF then
      While Not RS.EOF.....
      WEnd
   Else
      msgbox "No records were found"
   End if
Else
   msgbox "An error occured while running the query"
End if
0
 

Author Comment

by:nickwoolley
ID: 6921054
gondesiravi: doesn't ryancys method do the same thing using far less code?

However, I'm wanting to retain the apostrophe, so thanks Gunsen.
0
 

Author Comment

by:nickwoolley
ID: 6921062
mdougan: Thanks but this app is using dao. What other characters will cause me problems? I'm trapping for double quotes and not allowing them in the field.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6921931
OK, under DAO you are right, you don't have too many options.  If you are going against Access, I think that you can use Parameters as part of a QueryDef object for the same effect...

I don't have the list handy, but any SQL special character will give you problems (and that depends on your DBMS).  In SQL Server, you'll probably have strange results with any of the characters reserved for wildcards, like _ or % or ? along with double and single quotes.  You might also have trouble with forward or back-slashes, particularly if they are just preceding certain characters which I think can be interpreted as an escape sequence.

In Access you had * and # signs to give you trouble
0
 

Author Comment

by:nickwoolley
ID: 6924922
I've tested characters %,?,*,# and there was no problem. Forgot to check \ though.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6926357
Did you try the underscore (_) ?  Some of the characters may not give you an error, but they may not give you the results you expected.  For example, let's say that you had a query that said:

SELECT * FROM MYTABLE WHERE MYTABLE.MYEVENT LIKE 'Form_Load'


You might be expecting only rows with 'Form_Load' in the MYEVENT column to be returned, however, because the underscore is a valid wildcard character for the LIKE clause, then you might actually return rows with values like the following:

Form1Load
Form Load
FormmLoad

I guess my point is that it's a real headache to try to keep in mind all of the various places that some special character might hose your SQL, and it's really nice to work with parameters which can eliminate the need to worry about that....
0
 

Author Comment

by:nickwoolley
ID: 6931074
mdougan: No I haven't tried the '_' but I'll watch out for it, thanks.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB6 / DAO 3.6 / run time error: Couldn't find installable ISAM 7 94
using web browser with BING 40 121
VB6 ListBox Question 4 44
Advice in Xamarin 21 79
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

777 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