/    Sign up×
Community /Pin to ProfileBookmark

Special Characters

I’ve got a select statement that can have an ampersand and apostrophy used in it. As you all know this wreaks havic on the select statement. My question is how do you get around this? If you know of other characters to be careful of please add them.

[CODE]
strSelect = “SELECT * FROM Table WHERE MyField LIKE ‘a&m'”
‘or
strSelect = “SELECT * FROM Table WHERE MyField LIKE ‘m’s'”
[/CODE]

to post a comment

7 Comments(s)

Copy linkTweet thisAlerts:
@KDLAFeb 12.2008 — Have you tried using the ASCII equivalents of these?

& amp; for ampersand

& #8217; for apostrophe
Copy linkTweet thisAlerts:
@nbcrockettauthorFeb 12.2008 — Doesn't work.
Copy linkTweet thisAlerts:
@KDLAFeb 12.2008 — Well, it was work a try.
Copy linkTweet thisAlerts:
@nbcrockettauthorFeb 12.2008 — Thanks, if you or anyone else thinks of another possiblity let me know.
Copy linkTweet thisAlerts:
@nbcrockettauthorFeb 13.2008 — Ok, I figured out that to get an apostrophe to work I have to use the code below. I still need to know how to get an ampersand to work. Any ideas?
[CODE]Replace(strMyString, "'", "''")[/CODE]
Copy linkTweet thisAlerts:
@StaceyBMar 06.2008 — Hi. Did you ever figure this out? If so, maybe you can assist me in using the LIKE statement with variables? http://www.webdeveloper.com/forum/showthread.php?t=175460&highligh
Copy linkTweet thisAlerts:
@itHighway2007Mar 09.2008 — To write text with apostrophe into database I alwasy used following function:


Private function sqlstr(the_value)

dim stempstr

stempstr = "" & the_value

stempstr = replace(stempstr, "'", "''")

sqlstr = "'" & stempstr & "'"

end function[/QUOTE]



Example:

sSQL = "INSER INTO tblUser (Id, Comments) VALUES (1, "&sqlstr(txtComments)&")"
×

Success!

Help @nbcrockett spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 5.19,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...