How to accept wierd characters entered in forms

[FranckM]

I have currently have a form that crashes when I put an -->'<-- apastrophe in it. In the book I have they don't say how to accept characters that would otherwise be seen as code. Thanks for taking the time to read and reply.

This is the error I get if it can help anyone.

SELECT BURSARY_NUMBER FROM ABS.BURSARY WHERE BURSARY_ENAME = 'test3' AND BURSARY_FNAME = 'test3' AND BURSARY_EVALUE = 't3' AND BURSARY_FVALUE = 't3' AND BURSARY_EDESC = 't3' AND BURSARY_FDESC = 't3' AND BURSARY_EREQUIREMENTS = 't3' AND BURSARY_FREQUIREMENTS = 't3' AND BURSARY_FINAL_EDATE = 't3' AND BURSARY_FINAL_FDATE = 't3' AND BURSARY_INFORMATION = 't3's' AND BURSARY_PROVIDER_NUMBER = '2240' AND ACTIVE_YNFLAG = 'Y'

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-01756: quoted string not properly terminated

/ABSIntranet/BursaryMgnt/addBursary.asp, line 77

 

[desktophero]

I don't know if this is the best fix all, but it has worked fine for me.

Here is what I would do:

Go through each variable being passed (that's assuming that the &quot;t3's&quot; value is coming from a variable) and run it through the replace function.

replace all of the single qoutes with the reverse tick (the key next to the number 1, same as the tilde key)

then when you present information, replace the reverse tick with the single qoute.

Like I said, it's worked for me.

 

[outpatient]

i guess you are getting your error on the line below
't3' AND BURSARY_INFORMATION = 't3's'

Heres a vbscript example we use

'*****************************************************
'Function : ParseApos
'Description : alters a string to allow SQL to resolve apostrophes
'Parameters : string
'Returns : string
'***************************************************
Function ParseApos(strInfo)
Dim intZ%
Dim intNewPos%
Dim intUseNew%
Dim strNewString$

intZ = 1
intNewPos = 0
intUseNew = 0
strNewString = &quot;&quot;

Do while InStr(intZ,strInfo,&quot;'&quot > 0 'Lines are double quote, apostrophe, double quote
intNewPos = InStr(intZ,strInfo,&quot;'&quot'Lines are the same as above
strNewString = strNewString & Mid(strInfo,intZ,intNewPos-intZ) & &quot;''&quot;'Double quote,two apostrophes,double quotes
intZ = intNewPos + 1
intUseNew = 1
loop

If intUseNew = 0 then
strInfo = strInfo
else
strInfo = strNewString & Mid(strInfo,intZ,Len(strInfo))
End if

ParseApos = strInfo

End Function Sam Greene
Workflow,DRM,DB
MUSIC

http://www.mp3.com/outpatient

 

[Jorgandr]

I've had this problem many times. All I've had to do is this line:
function ToSQL(strSource)
ToSQL = Replace(strSource, &quot;'&quot;, &quot;''&quot

and then here
test3 = TOSQL(Request.Form(&quot;Test3&quot

it works like a charm plus you can change those tickies to create instant emoticons example:

function Smiley(strSouce)
Smiley = Replace(strSource, &quot;&quot;, &quot;<img src=../images/faces/smiley.gif alt=>

 

[FranckM]

Wow Thanks for the all the information guys, It works and I can now move on to my next problem eheh

 

[FranckM]

With this fix came a new problem. The page i'm working on is a sefl modifying page. Let's say you enter only one of 11 values, and in that one value you entered, you put an apostrophe. You send the info, then the page will resend to itself. Now all the values entered will be resent so you don't have to enter them again. This time around, for each apostrophe, 2 of them show up. This is normal due to what I changed with the help of those above.

What I did to try to fix this, is recreated the code from the 3rd post, and changed all ' for '' and '' for '. This I added in a new function.

Function ParseAposReverse(strInfo)
Dim intZ
Dim intNewPos
Dim intUseNew
Dim strNewString

intZ = 1
intNewPos = 0
intUseNew = 0
strNewString = &quot;&quot;

Do while InStr(intZ,strInfo,&quot;''&quot > 0 'Lines are double quote, apostrophe, double quote
intNewPos = InStr(intZ,strInfo,&quot;''&quot'Lines are the same as above
strNewString = strNewString & Mid(strInfo,intZ,intNewPos-intZ) & &quot;'&quot;'Double quote,two apostrophes,double quotes
intZ = intNewPos + 1
intUseNew = 1
loop

If intUseNew = 0 then
strInfo = strInfo
else
strInfo = strNewString & Mid(strInfo,intZ,Len(strInfo))
End if

ParseAposReverse = strInfo

End Function

Now I call this function in a loop to check all values if it finds that one of teh values is empty. I'm starting to think I just not calling the function at the right place.

 

[FranckM]

I instead of using what I last poster, I used the replace function.

Case closed.