Chr(39) in INSERT INTO statement
Bruce B
q = "INSERT INTO Comments (comment) VALUES ('" + x + "')"
x is a string that has been parsed.
When the user enters a comment I take that string (s) and go through it
one character at a time to see if there are any double or single quotes
(", '). If there are I replace them with chr(34) or chr(39).
example:
let's say word = "that's great"
in the function, i declare l as len(word), then iterate through it one
char at a time using mid(l, 1, 1). If it is not one of these two
characters I add it to temp (a string var). if it is then i add
"char(39)" or "char(34)" to temp.
I then return the value of temp.
When I look at the value of q (the INSERT INTO stmt above) it shows as
INSERT INTO Comments (comment) VALUES ('" + char(34)that+char(39)+s
great+char(34)+"')"
And then this gives me an error message when I run the query.
Is there a way (better) to do this?
Thanks.
- Bruce
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Trevor Best
<br...@mtiglobal.com> wrote:
>In the following statement
>
>q = "INSERT INTO Comments (comment) VALUES ('" + x + "')"
>
>x is a string that has been parsed.
>
>When the user enters a comment I take that string (s) and go through it
>one character at a time to see if there are any double or single quotes
>(", '). If there are I replace them with chr(34) or chr(39).
>
>example:
>
>let's say word = "that's great"
>in the function, i declare l as len(word), then iterate through it one
>char at a time using mid(l, 1, 1). If it is not one of these two
>characters I add it to temp (a string var). if it is then i add
>"char(39)" or "char(34)" to temp.
>
>I then return the value of temp.
>
>When I look at the value of q (the INSERT INTO stmt above) it shows as
>INSERT INTO Comments (comment) VALUES ('" + char(34)that+char(39)+s
>great+char(34)+"')"
>
>And then this gives me an error message when I run the query.
>
>Is there a way (better) to do this?
Sure, never use double quotes (chr(34)) in SQL, always single, your
queries will upsize to SQL Server so much easier and just double up
the single quotes within the string, e.g.
q = "INSERT INTO Comments (comment) VALUES ('" + Replace(x,"'","''") +
"')"
--
A)bort, R)etry, I)nfluence with large hammer.
(replace sithlord with trevor for email)
Huibert
--
Groeten,
Huibert.
"Trevor Best" <bouncer@localhost> schreef in bericht
news:cc2flv4tes4khruos...@4ax.com...
Huibert
rst.addnew
rst!field = "That's right."
rst!otherfield = word
rst.update
rst.close
Works fine for ALL bad characters!
--
Groeten,
Huibert.
"Chuck Grimsby" <c.gr...@worldnet.att.net.invalid> schreef in bericht
news:t2hflv00ucrbv527k...@4ax.com...
>
> Yeah, right. That'll work... Not!
>
> Trevor's answer was correct.
>
> On Thu, 4 Sep 2003 23:31:31 +0200, "Huibert" <pant...@hotmail.com>
> wrote:
>
> >or just use ado or dao.
> Beauty Is In The Eye Of The Beer Holder...
>
Bruce B
thanks for the feedback.
i finally did find some info via a search.
it said to use & rather than + when I'm adding chr(34) or chr(39) . . .
that ended up working.
I was doing this in a function that returned a value so I couldn't just
add ' or " cause then it would not work for the var that was being given
the value of the returned function.
So, my findings were, if the character is ' then do:
temp = temp & char(34) 'result is two single quotes
if the character is " then do:
temp = temp & char(39) 'result is two double quotes
Huibert
Never seen any probs with ODBC.
--
Groeten,
Huibert.
"Chuck Grimsby" <c.gr...@worldnet.att.net.invalid> schreef in bericht
news:kgvglvsfbqai3l0ot...@4ax.com...
>
> Ok, now try Putting:
>
> Quote the Raven, "Nevermore".
>
> In via your code.
>
> Or how about:
>
> And then he said, "Sure but what about this?" To which there was no
> answer.
>
> Neither ADO or DAO is the answer. They *will* work if the text is
> typed in via a TextBox, but never in code.
>
> There's a Knowledgebase article on this.... I've forgotten what it is
> however. I use a function I call SQLQuoteFix myself that fixes both
> cases for me, and call it whenever I'm working with a field that might
> contain either a single or double quote:
>
> Public Function SQLQuoteFix(TextIn As String) As String
> SQLQuoteFix = Replace(TextIn,
> Chr$(39), _
> Chr$(39) & Chr$(39))
> SQLQuoteFix = Replace(SQLQuoteFix, _
> Chr$(34), _
> Chr$(34) & Chr$(34))
> End Function
>
>
> On Fri, 5 Sep 2003 12:46:21 +0200, "Huibert" <pant...@hotmail.com>
> wrote:
>
> >word = "that's great"
> >set rst = db.openrecordset("SELECT * FROM TABLE")
> >rst.addnew
> >rst!field = "That's right."
> >rst!otherfield = word
> >rst.update
> >rst.close
> >
> >Works fine for ALL bad characters!
>
> You May Use This Opinion For A Two Week Trial Period.
>
David W. Fenton
<3f58a7e5$0$62076$7586...@news.frii.net>:
>it said to use & rather than + when I'm adding chr(34) or chr(39)
>. . . that ended up working.
I take it you've worked with a different variant of SQL that uses +
as the only concatenation operator?
The concatenation operators are tricky in certain respects.
For strings, & concatenates anything, including Nulls. The other
operator, +, propagates the Nulls, so string + Null gets you a
Null. This can be very, very handy, in fact:
Mid(("12" + LastName) & (", " + FirstName), 3)
This gets you:
FirsName LastName Result
David Fenton Fenton, David
Null Booth Booth
Bob Null Bob
The trick is that if the data on either side of the + end up as
numeric, the + will act as an addition operator, instead, even in a
string context.
In general, the & operator is the right one to use by default. You
only use + for concatenation when you are specifically intending to
propagate Nulls.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc