accepting 's from a search text in an SQL string 3

[kirstenro]

Hi, really stupid question (i'm new to this since 1 month). In VBscript/asp is there a function that accepts or converts 's in an SQL statement?

For example when I type in a name like D'Silva in my text box on this search engine I did, I get:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

Microsoft][Pilote ODBC Microsoft Access] Syntax error(operator absent) in the expression '(Uresp LIKE '%d'silva%') ORDER BY Dénom_français'.

This is because of the ' in d'silva I know...but what function corrects this? My books are of no help and I've been searching the web all day. Can someone help?

Thanks in advance

Kirsten

 

[jaredn]

in javascript, I use the following line (probably very close to vb):

mysqlstr = mysqlstr.replace(/'/g,"''&quot jared@aauser.com -

http://webfx.eae.net

 

[Shilohcity]

yep the asp function is very similar, something like

yourvariable = Request("yourvariable&quot
yourvariable = Replace(yourvariable,"'","&quot

This function needs the name of your variable, what is to be replaced, what to replace it with. I find it is always useful to Response.Write the end result to double check it is good to go also.


Justin.X-)
"Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[discusmania]

may be you can try this function, in my case, for your text boxt you change the single quote to double quote but the db will still see it as single string and search d'silva for you.

the function:

Sub change_quote(Entry, NewEntry)
dim LenEntry
'dim NewEntry
dim i

LenEntry=Len(Entry)
For i=1 to LenEntry
if mid(Entry,i,1)="'" then
NewEntry=NewEntry & "''"
else
NewEntry=NewEntry & mid (Entry,i,1)
end if
next
End Sub

in your process file (search.asp):

if not request("textboxname&quot="" then
call change_quote(request("textboxname&quot,Newstring)

SQL="INSERT INTO YOUR_TABLE (YOUR_FIELDS)VALUES('"& Newstring & "')
end if

hope this help

 

[frOblivion]

I know a very simple way to do this,

The ' will cause an error. But if it were a double apostrophe, '' Then it would not cause an error.

So When You Are Requesting Your Values From The Form Simply Change Any ' To ''

Like This:

Dim anyStringVariable

anyStringVariable = Request("ANYFORMITEM&quot
anyStringVariable = Replace(anyStringVariable, "'", "''&quot


Hope that helps.