Double apostrophes

[Joey Martin]

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.

 

[Aaron Bertrand [SQL Server MVP]]

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?

 

[Bob Barrows [MVP]]

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=...=1&[email protected]
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
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

 

[Joey Martin]

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

 

[Aaron Bertrand [SQL Server MVP]]

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.

 

[Joey Martin]

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.

 

[Joey Martin]

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!!

 

[Aaron Bertrand [SQL Server MVP]]

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.

 

[Aaron Bertrand [SQL Server MVP]]

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

 

[Joey Martin]

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.

 

[Aaron Bertrand [SQL Server MVP]]

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.

 

[Bob Barrows [MVP]]

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

 

[Giles]

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

 

[Aaron Bertrand [SQL Server MVP]]

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.