apostrophe problem
Rich
I am trying to make a dynamic dropdown list box that contains value
pulled from an Access database. The code is working properly except
when one of the values contains an apostrophe for example O'Leary.
When O'Leary shows up I get:
<option value='O'LEARY'>O'LEARY</option>
The system says there is an Extra quote character found or quote
character missing:
How can I fix it?
Thanks,
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/
TR/html4/strict.dtd">
<html>
<head>
<title>My first query</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<style type="text/css">
html, body {
height: 100%;
min-height: 100%;
}
body{
border:0;
margin:0px;
background-color:white;
color:black;
text-align:center;
}
select {
width:200px;
}
p {
width:200px;
}
</style>
</head>
<body>
<%@ Language = VBscript %>
<% Response.Buffer = True %>
<%
Dim objconn,objRS,strSQL1
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver
(*.mdb);DBQ=" & Server.MapPath("db.mdb")
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")
strSQL1 = "SELECT name FROM Table1 ORDER BY name ASC"
objRS.Open strSQL1, objconn
Response.Write "<p>Search by Name: "
Response.Write "<select name=name><option value='' selected>Name</
option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objrs("Name") &"'>"& objRs("Name")
&"</option>"
objRS.MoveNext
Loop
Response.Write "</select></p>"
objRs.Close
objconn.Close
%>
</body>
</html>
Bob Barrows
Rich wrote:
> Hi,
>
> I am trying to make a dynamic dropdown list box that contains value
> pulled from an Access database. The code is working properly except
> when one of the values contains an apostrophe for example O'Leary.
> When O'Leary shows up I get:
> <option value='O'LEARY'>O'LEARY</option>
> The system says there is an Extra quote character found or quote
> character missing:
> How can I fix it?
>
>
Escape it using the Replace function:
Response.Write "<option value='" & Replace(objrs("Name"),"'","\'") ...
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Evertjan.
> Rich wrote:
>> Hi,
>>
>> I am trying to make a dynamic dropdown list box that contains value
>> pulled from an Access database. The code is working properly except
>> when one of the values contains an apostrophe for example O'Leary.
>> When O'Leary shows up I get:
>> <option value='O'LEARY'>O'LEARY</option>
>> The system says there is an Extra quote character found or quote
>> character missing:
>> How can I fix it?
>>
>>
>
> Escape it using the Replace function:
>
> Response.Write "<option value='" & Replace(objrs("Name"),"'","\'") ...
I replace all apostrophes in db text fields with `, the "back quote",
only to reverse that in actual html text.
It has the added bonusses that char count is not disturbed and that
parameter injection can be more easily shielded.
However in simple html, why not do:
<option value="O'LEARY">O'LEARY</option>
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Rich
> Rich wrote:
> > Hi,
>
> > I am trying to make a dynamic dropdown list box that contains value
> > pulled from an Access database. The code is working properly except
> > when one of the values contains an apostrophe for example O'Leary.
> > When O'Leary shows up I get:
> > <option value='O'LEARY'>O'LEARY</option>
> > The system says there is an Extra quote character found or quote
> > character missing:
> > How can I fix it?
>
> Escape it using the Replace function:
>
> Response.Write "<option value='" & Replace(objrs("Name"),"'","\'") ...
Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'Replace'
Response.Write "<option value='" & Replace(objrs("Name"),"'","\'") &
"'>" & objRs("Name") &"</option>"& VbCrLf
Thanks,
Rich
The html is dynamically generated from the data in the access
database. I can't change the data so the apostrophe is a back quote.
Thanks,
Dan
"Rich" <rtil...@gmail.com> wrote in message
news:9e24c8f5-251c-439d...@e34g2000vbm.googlegroups.com...
Use double quotes for your attribute values (double them up in strings to
print them), and HTML encode your values.
Response.Write "<option value=""" & Server.HTMLEncode(objrs("Name")) &""">"
& Server.HTMLEncode(objRs("Name")) &"</option>"
If you really must use a single quote (apostrophe) for your attributes, then
replace the apostrpophes in your values with '
Response.Write "<option value='" &
Replace(Server.HTMLEncode(objrs("Name")),"'","'") &"'>" &
Server.HTMLEncode(objRs("Name")) &"</option>"
You should never just write data from anywhere, database or otherwise, into
HTML unless you're sure it's already been encoded correctly, as you leave
yourself option to XSS vulnerabilities if your variables/data is
compromised.
--
Dan
Dan
That means the value of the Name column in your recordset is a Null value,
in which case the code I suggested in my other reply won't work either. You
would need to do something like this:
If IsNull(objrc("Name")) Then
sName = ""
Else
sName = Replace(Server.HTMLEncode(sName),"'","'")
End If
Response.Write "<option value='" & sName & "'>" & sName &"</option>"& VbCrLf
Depending on whether you use double quotes or single quotes to encapsulate
attribute values, replace them with ' or " (Server.HTMLEncode
replaces " with " so already do this for you if you use double quotes
for your attributes).
--
Dan
Evertjan.
>> I replace all apostrophes in db text fields with `, the "back quote",
>> only to reverse that in actual html text.
>>
>> It has the added bonusses that char count is not disturbed and that
>> parameter injection can be more easily shielded.
>>
>> However in simple html, why not do:
>>
>> <option value="O'LEARY">O'LEARY</option>
[please do not quote signatures on usenet]
> The html is dynamically generated from the data in the access
> database. I can't change the data so the apostrophe is a back quote.
I would not accept that on my websites, as I am the webmaster there.
I do not accept any apostrophs to be in my database records to begin with.
Adrienne Boswell
writing in news:9e24c8f5-251c-439d-b44d-2dd929bdcb71
@e34g2000vbm.googlegroups.com:
> Hi,
>
> I am trying to make a dynamic dropdown list box that contains value
> pulled from an Access database. The code is working properly except
> when one of the values contains an apostrophe for example O'Leary.
> When O'Leary shows up I get:
><option value='O'LEARY'>O'LEARY</option>
> The system says there is an Extra quote character found or quote
> character missing:
> How can I fix it?
>
> Thanks,
>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/
> TR/html4/strict.dtd">
Cheers for using a strict doctype!
<snip>
>
><body>
><%@ Language = VBscript %>
><% Response.Buffer = True %>
><%
> Dim objconn,objRS,strSQL1
>
> Set objconn = Server.CreateObject("ADODB.Connection")
> objconn.ConnectionString = "DRIVER=Microsoft Access Driver
> (*.mdb);DBQ=" & Server.MapPath("db.mdb")
> objconn.Open
You could always put this into an include file. That way you don't have
to rewrite it all the time. I do <!-- include file = "conn_inc.asp" -->
>
> Set objRs = Server.CreateObject("ADODB.Recordset")
> strSQL1 = "SELECT name FROM Table1 ORDER BY name ASC"
> objRS.Open strSQL1, objconn
I would probably put this into a getrows array. Open the connection,
put the results in an array, and close your connection. This can
significantly improve speed, and is less work for the server.
So I would do:
if not objrs.eof then
rsarr = objrs.getrows()
else
'tell the client it's an empty record set
end if
objrs.close
set objrs = nothing
I would put your queries before you output any HTML. Makes debugging
easier, and you don't have to wait for the browser.
One of the good things about ASP is that it is easy to drop in and out
of HTML. It's easier to debug as well.
> Response.Write "<p>Search by Name: "
> Response.Write "<select name=name><option value='' selected>Name</
> option>"
Where is your form element? Is this a post operation or a get operation?
Where is supposed to process? If you have no form element, the brower
MIGHT send the request to the same page, but it might not. Best to be
safe and use the form element with appropriate attributes.
<form method="post" action="<%=request.servervariables
("script_name")%>">
<label for="search">Search by Name</label>
<select name="name" id="name">
<option value="" selected="selected"></option>
It's also better to double quote all your attributes. Although HTML
does not require you to quote attributes, it is a good practice. This
is especially true if you ever need to use XHTML, where quoting of
attributes is mandatory.
> Do While Not objRS.EOF
> Response.Write "<option value='" & objrs("Name") &"'>"& objRs("Name")
> &"</option>"
> objRS.MoveNext
> Loop
> Response.Write "</select></p>"
And I would rewrite this as:
<% for i = 0 to ubound(rsarr,2) %>
<option name="<%=rsarr(0,i)%>"><%=rsarr(0,i)%></option>
<% next %>
</select>
</form>
%>
> objRs.Close
> objconn.Close
>
> %>
>
></body>
></html>
>
I would put this last bit into another include, like footer_inc.asp that
you can use for all your pages. That way you never have to remember to
close the connection.
--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share
Dan
"Evertjan." <exjxw.ha...@interxnl.net> wrote in message
news:Xns9C9DD673...@194.109.133.242...
> Rich wrote on 07 okt 2009 in microsoft.public.inetserver.asp.db:
>
>>> I replace all apostrophes in db text fields with `, the "back quote",
>>> only to reverse that in actual html text.
>>>
>>> It has the added bonusses that char count is not disturbed and that
>>> parameter injection can be more easily shielded.
>>>
>>> However in simple html, why not do:
>>>
>>> <option value="O'LEARY">O'LEARY</option>
>
> [please do not quote signatures on usenet]
>
>> The html is dynamically generated from the data in the access
>> database. I can't change the data so the apostrophe is a back quote.
>
> I would not accept that on my websites, as I am the webmaster there.
>
> I do not accept any apostrophs to be in my database records to begin with.
Handling apostrophes in data is trivial. So how do you deal with text that
uses them? Do you really never have any data that requires it?
--
Dan
Evertjan.
> "Evertjan." <exjxw.ha...@interxnl.net> wrote in message
> news:Xns9C9DD673...@194.109.133.242...
>> Rich wrote on 07 okt 2009 in microsoft.public.inetserver.asp.db:
>>
>>>> I replace all apostrophes in db text fields with `, the "back
>>>> quote", only to reverse that in actual html text.
>>>>
>>>> It has the added bonusses that char count is not disturbed and that
>>>> parameter injection can be more easily shielded.
>>>>
>>>> However in simple html, why not do:
>>>>
>>>> <option value="O'LEARY">O'LEARY</option>
>>
>> [please do not quote signatures on usenet]
>>
>>> The html is dynamically generated from the data in the access
>>> database. I can't change the data so the apostrophe is a back
>>> quote.
>>
>> I would not accept that on my websites, as I am the webmaster there.
>>
>> I do not accept any apostrophs to be in my database records to begin
>> with.
>
> Handling apostrophes in data is trivial.
You may find so indeed, so I handle this "trivial" problem.
Others might not match your experienced triviality level.
> So how do you deal with text that uses them?
I explained that that above. Please read the quoted.
> Do you really never have any data that requires it?
Never yet. And I doubt I will ever.
"Never" being a big word, if so I will adapt my policy for once.
Rich
> Gazing into my crystal ball I observed Rich <rtillm...@gmail.com>
> Please respond to the group so others can share
Thank you Adrienne. I took your advice and everything is working fine
now. I am sorry it took so long to get back but I got put on a
different project for awhile.
Thanks to everyone who responded.
Adrienne Boswell
in news:e3454ca1-7b12-4d2c...@g22g2000prf.googlegroups.com:
> Thank you Adrienne. I took your advice and everything is working fine
> now. I am sorry it took so long to get back but I got put on a
> different project for awhile.
>
> Thanks to everyone who responded.
>
Glad to know everything worked out okay. Happy Thanksgiving if you're in
the US.