PDA

View Full Version : Data validation in Access

ocularis
11-26-2002, 05:54 PM
I know this may be slightly off-topic, but I am retrieving data from an Access database in my ASP page. In order to pass my data correctly from ASP to Javascript as an array, the text fields must NOT contain any quotation marks. I thought it would be easy to make a validation rule (in Access) to do this. I can't seem to get it right.

I've tried stuff like:
<> "
NOT \"

I get errors with these. If anyone knows of a better forum to post these questions, please let me know.
Thank you.
jeorg
11-26-2002, 07:05 PM
yes I do th esame for a tree node from a data base acces to a javascript

I get

Tree[23] = "2220000|310|imprimantes jet d\'encre|resultat.aspx?cat=categorie3&val=222";

I just do a loop to replace ' by \'

with a datareadrer

While dtRead.Read() 'Cat3
_t = New Tree()

_t.nodeId = dtRead("nodeId").ToString + SuffCat3
_t.parentNodeId = dtRead("parentNodeId").ToString + SuffCat2
_t.nodeName = Replace(dtRead("nodeName").ToString, "'", "\'")
_t.nodeUrl = Url + dtRead("nodeId").ToString

al.Add(_t)

End While
jeorg
11-26-2002, 07:07 PM
the copy past didn't work

it is of course replace ' by \ +'
ocularis
11-26-2002, 11:07 PM
Thanks for the code, but I'm not sure I know how to use it. What is a datareader? Do I use that in ASP or Javascript?

Also, I would prefer to do this in Access with the validation rule. I'm not sure if that is possible, though.

Thanks very much for replying.
whammy
11-27-2002, 01:26 AM
if you need to replace quotation marks when passing them from server-side ASP to client-side javascript, it's as simple as:

myJSvar = "<% = Replace(myASPvar,"'","\'") %>"

You could even make it a function... i.e.

Function EscapeSingleQuotes(byVal str)
If IsNull(str) Then str = ""
EscapeSingleQuotes = Replace(str,"'","\'")
End Function


myJSvar = "<% = EscapeSingleQuotes(myASPvar) %>"

I use something just like that in some of my javascript on an internal website at work, where there are things like "John O'Hara", "Fred's Bank", etc. that must be displayed from a database into javascript.

P.S. as far as I know, there is no better forum in which to post these questions. ;)

:)
glenngv
11-27-2002, 08:50 AM
Might as well escape other special characters...

<%
Function EscapeBackslash(byVal str)
If IsNull(str) Then
str = ""
Else
str = Replace(str,"'","\'")
str = Replace(str,"""","\""")
str = Replace(str,"\","\\")
str = Replace(str,VbCr,"\r")
str = Replace(str,VbLf,"\n")
str = Replace(str,VbTab,"\t")
End If
EscapeBackslash = str
End Function
%>
ocularis
11-27-2002, 03:29 PM
I didn't mean to imply that this is a bad forum, just maybe the wrong forum for my question. In fact, this forum is great! I've learned a lot from you guys.

Thanks for the information. That Replace function should do exactly what I want.
whammy
11-27-2002, 11:01 PM
That's a good point (and function), Glenn. Mind if I steal it? :)
glenngv
11-28-2002, 02:00 AM
of course :)
Morgoth
11-28-2002, 03:12 AM
Originally posted by glenngv
of course :)

That means you mind that he steals it.
Do you want to let him use it? Or are you saying you don't want to let him use it?

[Useless post by Morgoth #284]
whammy
11-28-2002, 12:24 PM
Heh... good point, Morgoth. ;)
glenngv
11-29-2002, 01:50 AM
sorry for the confusion
I meant that he (or anyone, for that matter) can use it :)
whammy
11-29-2002, 03:23 AM
After some testing, I've decided that this works better:


Function EscapeJS(byVal str)
EscapeJS = Replace(Replace(Replace(Replace(Replace(Replace(Replace(str,"\","\\"),"'","\\'"),"""","&quot;"),vbCrLf,"\n"),vbCr,"\r"),vbLf,"\n"),vbTab,"\t")
End Function


Lemme know if you find any problems with it (after you fix the line break the forums put in). :)
glenngv
11-29-2002, 03:42 AM
I think you should not escape double-quotes as &amp;quot;
You won't know that the string would be used as alert message.
I don't think using \" would mess up the html in document.write statements

<body>
<%
aspVar = "The ""Quick"" brown fox"
%>
<script>
jsVar = "<%=EscapeJS(aspVar)%>";
document.write(jsVar);
alert(jsVar);
</script>
</body>
whammy
11-29-2002, 03:49 AM
But it did mess up JS when including it inline in HTML (i.e. updating window.status), since I used double quotes for the HTML and single quotes to delimit the js string... in that case escaping the double quotes doesn't work.

I guess you could use different versions, depending upon the situation. :)
glenngv
11-29-2002, 04:10 AM
or to add another parameter :)
whammy
11-29-2002, 04:11 AM
Yeah... that's the way to go... add a parameter to the function to return different results depending upon which context you're using it in. Good thinking. :)