Replace(strEntity,"'","''") doubles single quotes in database 
Author Message
 Replace(strEntity,"'","''") doubles single quotes in database

The replace function:
Replace(strEntity,"'","''") as in

strEntity = Request.Form("strEntity")
strEntity = Replace(strEntity,"'","''")
normally works for me in editing text fields in an Access database thay
might contain single quotes.

Now it seems to double up the single quotes in the database fields with each
update, so that
"organization's goals" soon looks like "organization''''''''s goals"

Does it have to do with the way I am updating fields:
This code results in doubling of single quotes:

Set oRS2 = Server.CreateObject("ADODB.Recordset")
With oRS2
    .ActiveConnection = oConn
    .Source = strSQL
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open
    .Fields("Entity") = strEntity
    .Update

vs. executing a query statement:

strSQL = "UPDATE tblApplicants SET Entity = '" & strEntity & "'"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
oConn.Execute strSQL, , adCmdText + adExecuteNoRecords

The latter code borrowed from another project does not seem to double up
single quotes on updating.



Sun, 27 Jun 2004 11:19:49 GMT  
 Replace(strEntity,"'","''") doubles single quotes in database
you might have better luck with this... My script was
terrible with Irish names until I added:

Name=Replace(Request.Form("Name"), Chr(39), "'")

Quote:
>-----Original Message-----
>The replace function:
>Replace(strEntity,"'","''") as in

>strEntity = Request.Form("strEntity")
>strEntity = Replace(strEntity,"'","''")
>normally works for me in editing text fields in an

Access database thay
Quote:
>might contain single quotes.

>Now it seems to double up the single quotes in the

database fields with each
Quote:
>update, so that
>"organization's goals" soon looks

like "organization''''''''s goals"
Quote:

>Does it have to do with the way I am updating fields:
>This code results in doubling of single quotes:

>Set oRS2 = Server.CreateObject("ADODB.Recordset")
>With oRS2
>    .ActiveConnection = oConn
>    .Source = strSQL
>    .CursorType = adOpenDynamic
>    .LockType = adLockOptimistic
>    .Open
>    .Fields("Entity") = strEntity
>    .Update

>vs. executing a query statement:

>strSQL = "UPDATE tblApplicants SET Entity = '" &
strEntity & "'"
>Set oConn = Server.CreateObject("ADODB.Connection")
>oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & dbPath
>oConn.Execute strSQL, , adCmdText + adExecuteNoRecords

>The latter code borrowed from another project does not
seem to double up
>single quotes on updating.

>.



Sun, 27 Jun 2004 11:47:15 GMT  
 Replace(strEntity,"'","''") doubles single quotes in database
Well, I just needed to look closely at the code!

In the second example below, the replace function is needed for the query
string because it uses single quotes for delimiters. But the ADO update
medthod takes the variable directly so no delimiters are needed, and
therefore no replace function is needed. If it is used, then it doubles up
the quotes.

Problem solved.

Thanks!


Quote:
> The replace function:
> Replace(strEntity,"'","''") as in

> strEntity = Request.Form("strEntity")
> strEntity = Replace(strEntity,"'","''")
> normally works for me in editing text fields in an Access database thay
> might contain single quotes.

> Now it seems to double up the single quotes in the database fields with
each
> update, so that
> "organization's goals" soon looks like "organization''''''''s goals"

> Does it have to do with the way I am updating fields:
> This code results in doubling of single quotes:

> Set oRS2 = Server.CreateObject("ADODB.Recordset")
> With oRS2
>     .ActiveConnection = oConn
>     .Source = strSQL
>     .CursorType = adOpenDynamic
>     .LockType = adLockOptimistic
>     .Open
>     .Fields("Entity") = strEntity
>     .Update

> vs. executing a query statement:

> strSQL = "UPDATE tblApplicants SET Entity = '" & strEntity & "'"
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
> oConn.Execute strSQL, , adCmdText + adExecuteNoRecords

> The latter code borrowed from another project does not seem to double up
> single quotes on updating.



Mon, 28 Jun 2004 07:44:50 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. "Replace"-Message doesn't prompt

2. "Replace"-Message doesn't prompt

3. Strange "Expected '('" Error

4. Parsing CSV strings like "Doe, John",33, "6'2"""

5. Parsing CSV strings like "Doe, John",33, "6'2"""

6. Double quote ("") in a string

7. Find A and @ replace with "quotes"

8. What's ".error"?

9. Don Lancaster's "gonzo" routines

10. 0 works, "" doesn't

11. What's a "Web Beacon"?

12. can't CreateObject("Wscript.Shell")

 

 
Powered by phpBB® Forum Software