Allowing non alphanumeric characters 1

[qaqueen]

We have a web page that contains a list of projects. Users click a project, which opens a popup window containing a form. Data regarding the selected project is displayed on the form. This is all done via an ASP page.

The data is pulled from two tables on an Access database. Problem is that when data is pulled that contains, for instance, a single quote, (this most often occurs in a memo field labeled "Comments") no data is displayed, nor are any additional "Comments" that don't contain any single quotes.

How can we get these data items that contain non-alphanumeric characters into the page?

 

[Sheco]

A single quote is used as a string delimiter in SQL

So maybe you've got a page that has something like this:
[tt]
strValue = "Eat The Flowers"
strSQL = "INSERT MyTable (Field1, Field2) VALUES (1,'" & strValue & "')"
adoCN.Execute strSQL
[/tt]


Except that your strValue might end up:
[tt]
strValue = "[red]Don't[/red] Eat The Flowers"
strSQL = "INSERT MyTable (Field1, Field2) VALUES (1,'" & strValue & "')"
adoCN.Execute strSQL
[/tt]

... and the problem with that is your strSQL ends up looking like this:[tt]
[highlight]INSERT MyTable (Field1, Field2) VALUES (1,'Don'[/highlight]t Eat The Flowers'
[/tt]

 

[qaqueen]

Hey, thanks...good explanation. But...is there a way around this? Some code that will allow non-alphanumeric characters?

 

[Chopstik]

You can use a Replace function to replace the single quotes with double quotes. Something like this would probably work:

Replace(Field2,"'",""")

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[qaqueen]

Problem is, I don't want to replace the single quotes (it would look funny), I want the single quotes to appear in the text, if possible. Is there a way to do that?

 

[DotNetGnat]

the replace funtion wont replace the actual single quote...we are just bypassing the affect of single quote using the double quote...try it out and see how it works..

also to display non-alpha numeric characters...look into HTMLEncode() method...

-DNG

 

[Chopstik]

Or, you could use double single quotes when doing your entry into the SQL field. I know that is what I've had to do on some of my own apps. It will preserve the integrity of the data you're entering into your DB so that it will enter the single quotes as you want them entered.

Replace(Field2,"'","[COLOR=red]''[/color]")

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[qaqueen]

hmmm...okay, I see what you're saying, but I'm not sure where to put it. Here's the code (it's the eighth "Comment_String" line where Comments are pulled into the form) :

if not rst2.eof then
do while not rst2.eof
%>
Comment_String +='<TR>';
Comment_String += '<TD WIDTH="1" HEIGHT="50" BGCOLOR="CFDBDE"><pre></pre></TD>';
Comment_String += '<TD class=detaildata WIDTH="50" HEIGHT="50" BGCOLOR="ffffff" ALIGN="center" VALIGN="center">&nbsp;Date:&nbsp;&nbsp</TD>';
Comment_String += '<TD WIDTH="1" HEIGHT="50" BGCOLOR="CFDBDE"><pre></pre></TD>';
Comment_String += '<TD class=returneddata WIDTH="114" HEIGHT="50" BGCOLOR="ffffff" ALIGN="center" VALIGN="center"><%=rst2("Comment_Date")%></TD>';
Comment_String += '<TD WIDTH="1" HEIGHT="50" BGCOLOR="CFDBDE"><pre></pre></TD>';
Comment_String += '<TD WIDTH="5" HEIGHT="50" BGCOLOR="ffffff"><pre></pre></TD>';
Comment_String += '<TD class=returneddata WIDTH="588" HEIGHT="50" BGCOLOR="ffffff" ALIGN="left" VALIGN="center"><%=rst2("Comment")%></TD>';
Comment_String += '<TD WIDTH="1" HEIGHT="50" BGCOLOR="CFDBDE"><pre></pre></TD>';
Comment_String += '</TR>';
Comment_String += '<tr><TD WIDTH="761" HEIGHT="1" BGCOLOR="CFDBDE" colspan="8"><pre></pre></TD></tr>';
<%
rst2.movenext
loop
end if

 

[Sheco]

<%= Replace(rst2("Comment") & "","'","''") %>

ps: String concatenation is slow... inside a loop for each record in a big recordset is worse. Perhaps you could write these values out as you go instead of building a long string and writing at the end of the loop.

 

[qaqueen]

First, yeah, you're undoubtedly right...our code is rather cumbersome (my partner and I are learning this stuff the hard way!)...but it only has to return a single record out of maybe 50 max, so I think it will do until we have the time (and ambition) to re-write it.

Anyway, I tried your suggestion:
If I use it exactly as you have it -- "","'","''" -- no comments are displayed on my page, and "view source" displays it like this: "FDD''s will continue to be worked on"
If I change it to this -- "","'","'" -- no comments are displayed on the page, but "view source" displays it like this: "FDD's will continue to be worked on."
If I change it to this -- "","'","" -- the comments ARE displayed, and that same Comment segment displays like this: "FDDs will continue to be worked on" with NO single quote.
I'm perfectly happy with the last instance...we don't really care if the single quote displays or not (but we didn't want to have to warn people not to enter any single quotes when they do data entry). I just thought I'd let you know...

Thanks so much for your help!

 

[Sheco]

but we didn't want to have to warn people not to enter any single quotes when they do data entry

You'd want to also use Replace() as part of the server-side input validation that you do before inserting the new record into the database.

Something like this:
[tt]
'First get values submitted from HTML Form:
strField1 = Request.Form("Comment")
strField2 = Request.Form("CommentDate")

'Next double up any single quotes
strField1 = Replace(strField1, "'", "''")
strField2 = Replace(strField2, "'", "''")

'Verify date input is actually a date
if not IsDate(strField2) then
'not a date
strField2 = ""
end if

'Build SQL string
strSQL = "INSERT MyTable (Field1, Field2) VALUES ("
if strField1 = "" then
strSQL = strSQL & "Null, "
else
strSQL = strSQL & "'" & strField1 & "', "
end if
if strField2 = "" then
strSQL = strSQL & "Null, "
else
strSQL = strSQL & "'" & strField2 & "' "
end if
strSQL = strSQL & ") "


'Do INSERT here:
adoCN.Execute strSQL
[/tt]

 

[qaqueen]

Ahhh...good stuff. That works GREAT! Thanks again.