469,946 Members | 1,782 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

Double apostrophes

I'm sure this has to be a simple fix. I just cannot figure it out.

To resolve the typical apostrope issue, I have the
acarriername = Replace(txtcarriername.text, "'", "''")

My problem is that 2 apostrophes are now inserted, instead of one. For
example if someone types in IT'S NICE, when it is displayed in the input
text box later (to allow a user to change it), it says IT''S NICE.

This is a sql 2000 database.

Thanks for the help.


*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #1
13 1864
How are you inserting the data into the database? If you are using a
command object and passing the values into a parameter, you don't need to do
the replace since it is handled for you by the provider.

Can you show all of your code so we don't have to guess and grasp at straws?


"Joey Martin" <jo**@infosmiths.net> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
I'm sure this has to be a simple fix. I just cannot figure it out.

To resolve the typical apostrope issue, I have the
acarriername = Replace(txtcarriername.text, "'", "''")

My problem is that 2 apostrophes are now inserted, instead of one. For
example if someone types in IT'S NICE, when it is displayed in the input
text box later (to allow a user to change it), it says IT''S NICE.

This is a sql 2000 database.

Thanks for the help.


*** Sent via Developersdex http://www.developersdex.com ***

Jul 22 '05 #2
Joey Martin wrote:
I'm sure this has to be a simple fix. I just cannot figure it out.

To resolve the typical apostrope issue, I have the
acarriername = Replace(txtcarriername.text, "'", "''")

My problem is that 2 apostrophes are now inserted, instead of one. For
example if someone types in IT'S NICE, when it is displayed in the
input text box later (to allow a user to change it), it says IT''S
NICE.

Only do the replace when writing the data into the database. Do not do it
any other time.

Better yet, stop using dynamic sql. The only reason you have to escape the
apostrophe is because you are not using parameters. See these:

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
http://groups-beta.google.com/group/...e36562fee7804e
http://tinyurl.com/jyy0

If this does not answer your question, post a short repro script so we can
see what you're doing.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #3

Current code:

Set RS = Server.CreateObject("ADODB.Recordset")
sqlUpdate = "SELECT * FROM ricprops WHERE propno='" & Request("id") &
"'"

RS.open sqlUpdate,Conn,1,3
RS("comments") = Replace(Request.form("comments"), "'", "''")
RS.Update
RS.Close


*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #4
Ugh. How about:

comments = replace(request.form("comments"), "'", "''")
id = replace(request.form("id"), "'", "''")
sql = "UPDATE ricprops SET comments = '" & comments & "' WHERE propno='" &
id & "'"
conn.execute sql,,129

Or see Bob's link.

Note you should use request.form() not the lazy request().

Also, why is the column propno a character datatype? Terrible name--no
implies number.
Jul 22 '05 #5
Bob,
Not sure I understand your comments about not using dynamic sql. I read
the documents and it seems as if I do it the correct way. USUALLY, I do
not use a recordset to update variables. My current code does, because
it's old code and I never re-wrote it. Usually, I do the following:

if request("submit")<> "" then
v1=Replace(Request.form("v1"), "'", "''")
v2=Replace(Request.form("v2"), "'", "''")

sql="update table set v1='" & v1 & "',v2='" & v2 & "' where id='1'"
conn.execute (sql)

Is that incorrect? Doing it this way, I still get the double apostrophe.


*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #6

Aaron,

What made you think propno is a CHAR datatype? It is INT.
The way you wrote the code is how I USUALLY do it.
But, doing it that way,as you wrote, I still receive double apostrophes.

THANKS!!
*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #7
> sql="update table set v1='" & v1 & "',v2='" & v2 & "' where id='1'"
conn.execute (sql)

Is that incorrect? Doing it this way, I still get the double apostrophe.


Then you are also doing the replace when you DISPLAY the data, which you
shouldn't be doing.
Jul 22 '05 #8
> What made you think propno is a CHAR datatype? It is INT.

Because in your query, you surround it with quotes:
WHERE propno='" & Request("id") & "'"


If it's an INT, don't do that!

A
Jul 22 '05 #9
Aaron,

When displaying the data in my textarea box, here is the code:
<textarea cols="30" rows="10"
name="comments"><%=trim(rs("comments"))%></textarea>
At that time, it displays IT''S NICE.

*** Sent via Developersdex http://www.developersdex.com ***
Jul 22 '05 #10
There's something missing or that you're not telling us. Do you understand
why replace() is used when passing data *to* the statement, but not when
retrieving the data from the database? Are you sure you didn't enter two
apostrophes into the form?

While you've told us that you are doing this correctly, we have no way to
verify that you really are. It sounds to me like you're not.

Sorry, but I don't know how else to help you.

When displaying the data in my textarea box, here is the code:
<textarea cols="30" rows="10"
name="comments"><%=trim(rs("comments"))%></textarea>
At that time, it displays IT''S NICE.

Jul 22 '05 #11
Joey Martin wrote:
Bob,
Not sure I understand your comments about not using dynamic sql. I
read the documents and it seems as if I do it the correct way.
USUALLY, I do not use a recordset to update variables. My current
code does, because it's old code and I never re-wrote it. Usually, I
do the following:

if request("submit")<> "" then
v1=Replace(Request.form("v1"), "'", "''")
v2=Replace(Request.form("v2"), "'", "''")

sql="update table set v1='" & v1 & "',v2='" & v2 & "' where id='1'"
conn.execute (sql)
Wait a minute. Earlier you showed this code:

Set RS = Server.CreateObject("ADODB.Recordset")
sqlUpdate = "SELECT * FROM ricprops WHERE propno='" & Request("id") &
"'"

RS.open sqlUpdate,Conn,1,3
RS("comments") = Replace(Request.form("comments"), "'", "''")
RS.Update
RS.Close
When updating a recordset field, do NOT escape (double up) the aprostrophe.
Change it to:
RS("comments") = Request.form("comments")

The only time you need to escape the apostrophe is when you are building
dynamic sql as in the update statement you show above.

Is that incorrect? Doing it this way, I still get the double
apostrophe.


I don't think so. Create a page with just this code in it:

<%
dim conn, sql, rs, input, output
input="it's nice"
set conn=createobject("adodb.connection")
conn.open "your connection string"
sql= "update ricprops set comments='" & _
Replace(input, "'", "''") & _
"WHERE propno=1"
conn.execute sql,,129
sql="select comments from ricprops WHERE propno=1"
set rs=conn.execute(sql,,1)
output=rs(0).value
rs.close:set rs=nothing
conn.close: set conn=nothing
%>

<html><body>
I guarantee this will contain only one apostrophe:<BR>
<textarea cols="30" rows="10"
name="comments"><%=trim(rs("comments"))%></textarea>
</body></html>

Run the page.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #12
"Aaron Bertrand [SQL Server MVP]" wrote
Note you should use request.form() not the lazy request().


Hi Aaron - What's the downside of request only? is the overhead of all the
request objects being hunted through? I sometimes use it to be able to test
processing pages with a querystring without having to rewrite the forms on
the previous page.
Thanks
Giles
Jul 22 '05 #13
> Hi Aaron - What's the downside of request only? is the overhead of all the
request objects being hunted through?
http://www.aspfaq.com/2111
I sometimes use it to be able to test processing pages with a querystring
without having to rewrite the forms on the previous page.


You could test which method was used and handle it in a function, test
Request.ServerVariables("REQUEST_METHOD") *once*... then you could use
something like req("item") instead of having to change them.
Jul 22 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Lord Merlin | last post: by
3 posts views Thread by James Foreman | last post: by
1 post views Thread by Mike C# | last post: by
reply views Thread by wizard_chef | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.