apostrophes in SQL query string
Carol Allen
Hi,
I am coding a form to gather input from users. However, when the user
submits that form, if any of the input text contains an apostrophe, the
SQL query string gets cut off short and produces an error (because it
thinks that the apostrophe is marking the end of a value or the end of
the command). An example of the problematic code (asp) is:
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "output"
SQLQuery= "INSERT INTO CDOutputData (Client) VALUES ('" & ClientVal &
"');"
Set rsOUTPUT = OBJdbConnection.Execute(SQLQuery)
Where ClientVal is the variable containing text with an apostrophe, and
Client is the field in the database which I would like to insert to.
This must be a common problem, but I am having such a hard time finding
a solution, or a workaround. Any suggestions would be greatly
appreciated!
Thanks,
Carol
Bryan McVeigh
I've run across this problem myself. In my case, I don't have the problem of
pulling data with these special characters. But, I might have to deal with
people trying to type special characters and pass them through a SQL
statement. I actually found that the troublesome characters are the
double-quote (") and the pipe (|) ... by the way, does anyone know why it's
called a 'pipe' symbol ... never understood that!
I actually use an AfterUpdate procedure to test the contents of a text box
for special characters and then inform the user they can't use them via a
message box. (Hopefully, you can use this method as well ... if you need to
actually USE these special characters then someone else will have to help
you along, unfortunately!).
I have the following function defined in my form code:
Private Function Check_for_Special_Characters_FromTo(TestStr)
Dim StrPos1 As Long
Dim StrPos2 As Long
Dim OKtoContinue As Boolean
StrPos1 = InStr(1, TestStr, """", 1) 'This tests for a single occurence
of a double-quote (")
StrPos2 = InStr(1, TestStr, "|", 1) 'This test for a pipe(|)
If StrPos1 > 0 Or StrPos2 > 0 Then
OKtoContinue = False
Else
OKtoContinue = True
End If
Check_for_Special_Characters_FromTo = OKtoContinue
End Function
I then put the following code in the AfterUpdate section of each text field
to test:
Private Sub FromValue1_AfterUpdate()
Dim FromVal As String
Dim ErrMsg As Boolean
If IsNull(Me!FromValue1) Then
FromVal = ""
Else
FromVal = Me!FromValue1
End If
ErrMsg = Check_for_Special_Characters_FromTo(FromVal)
If ErrMsg = False Then
MsgBox ("You attempted to use a double-quote("") or pipe (|) symbol.
Please try again without using either special character.")
Me!FromValue1 = ""
Me!FromValue1.SetFocus
End If
End Sub
That's it. Good luck!
-------------------------------------------------
| BRYAN McVEIGH
| Trade Service Systems, Inc.
| Email: bmc...@tradeservice.com
| Phone: 800/289-8771 x2628
-------------------------------------------------
Carol Allen wrote in message <34907D...@studiomotiv.com>...
Carol Allen
Thanks Bryan, but unfortunately i have to use those apostrophes (single
quote) AND double quotes. ugh.
By the way, it's a "pipe" because in UNIX, you can use it to send info
from the output of one app to the input of another. so, it acts like a
little pipe to send data through, or something like that.
Andy Baron
There is no simple way to handle values that can contain single and
doulble quotes. One or the other is easy. Apostrophes can be handled
by using two contiguous double-quotes where you are now using a single
quote:
SQLQuery= "INSERT INTO CDOutputData (Client) VALUES (""" & ClientVal &
""");"
Two contiguous double-quotes will be interpreted as one. Try this in
the Debug Window:
msgbox "MyField = """ & "A'B" & """ And MyOtherField = '" & _
"C""D" & "';"
-- Andy
Harlan Messinger
Carol Allen <ca...@studiomotiv.com> wrote:
>Thanks Bryan, but unfortunately i have to use those apostrophes (single
>quote) AND double quotes. ugh.
>By the way, it's a "pipe" because in UNIX, you can use it to send info
>from the output of one app to the input of another. so, it acts like a
>little pipe to send data through, or something like that.
It serves the same function in DOS and OS/2. However, these operating
systems don't have the same plethora of commands as UNIX that read
from standard input and write to standard output, so piping is not
prominent a feature.
Edward R. Smith
>This must be a common problem, but I am having such a hard time finding
>a solution, or a workaround. Any suggestions would be greatly
>appreciated!
>Thanks,
>Carol
I have never actually had this problem, I am just throwing out an idea here.
You could write a function which takes a string and returns a string. It
would go through its input string looking for problem characters like ' and ".
I know that Access provides ways to use these (Someone has already mentioned
'', so I assume that it would be "" for "). If the function finds one, it
replaces it with two. Hopefully that would give you a usable string.
Edward R. Smith
Tri-Tech Electronics, Inc.
Orlando, FL