Replacing apostrophe in a recordset for SQL insert

[Himmel]

Hello!

I am attempting to take data from one table and insert it into another. I am encountering problems where data in a recordset may contain an apostrophe, which completely kills my SQL Insert.

DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES('" & rs![oldtext] & "')"

Problem is, some of these records may also be Null, so that kills a Replace() call.

I've found a really sloppy way of getting around this, but it's horrendous!

oldvar = ""
if(Len(rs![oldtext]) > 0 then
oldvar = rs![oldtext]
oldvar = Replace(oldvar, "'", "''")
end if
 
DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES('" & oldvar & "')"
 

Please help. Been trying to figure this out for over an hour, and each time I think I find a solution, some funky record comes up and knocks me down!

Thanks!

[MMcCarthy]

Try this ...

DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES(" & "" & rs![oldtext] & "" & ")"

[Himmel]

That returns an error on the first value it tries to write: "Syntax error (missing operator) in query expression."

In Debug, I can hover over the expression it is saying is in error, and it (along with all other expressions) contain the data that is expected.

[MMcCarthy]

Try this ...

Dim strSQL as String
 
   strSQL = "INSERT INTO NEWTABLE (Record1) VALUES(" & "" & rs![oldtext] & "" & ")"
 
   DoCmd.RunSQL strSQL
 

[pks00]

try this


DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES(" & chr$(34) & NZ(rs![oldtext],"") & chr$(34) & ")"

nulls, hmm yes. If u ensure that the field allows nulls and is not mandatory then it should work

[MMcCarthy]

try this


DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES(" & chr$(34) & NZ(rs![oldtext],"") & chr$(34) & ")"

nulls, hmm yes. If u ensure that the field allows nulls and is not mandatory then it should work

Nice one, should have thought of that. ;)

[pks00]

Nice one, should have thought of that. ;)

Its cos your overworked as a Administrator and ensuring orphaned questions get cleared :)

[MMcCarthy]

Its cos your overworked as a Administrator and ensuring orphaned questions get cleared :)

LOL, thanks for that.

[pks00]

No probs :)

[Himmel]

Thanks guys. :) This is starting to shape up nicely.

I hate to throw a wrench into the works, but now I'm getting an operand error when the SQL encounters a record that contains a quotation mark. And again, the field may be Null.

Any suggestions on what to do next?

[pks00]

What code are you using

[Himmel]

What code are you using

I took a page out of your book:

DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES(" & chr$(34) & NZ(rs![oldtext],"") & chr$(34) & ")"

This handles fields that have an apostrophe. But if any have a quotation, it breaks.

[goforth4]

I can advise you where to look.

Not 100% sure how..

But I'd search on how SQL insert statements handle apostrophes in inserting data.

Then I'd use the technique to 'replace' apostrophes with whatever the syntax SQL is appropriate for the database/SQL.

e.g.

tempStr = "String with apost'rophe"
newStr = replace(tempStr,"'","''") ''DOUBLE APOSTROPHE
 

Might work.. Dunno.

[BigJ]

Replace the apostrophe in values that are encased in single quotes (e.g. the O'Brien in 'O'Brien') as follows: O' & "'" & 'Brien

Use the following code snippet:

Replace(rs![field1], " ' ", " ' " & " & " & """" & " ' " & """" & " & ' ") & "'" )

NOTE I have added a space between the single and double quotes to make it easier to tell them apart. In the actual code you use, you should not have these spaces.

For example

Instead of

Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & rs![field1] & "'")

use

Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & Replace(rs![field1], "'", "'" & " & " & """" & "'" & """" & " & '") & "'" )

This worked for me and allowed me to use VBA to insert values containing apostrophes (single quote marks) into a table using SQL insert statements

[NeoPa]

This is really a lot more straightforward than people give it credit for. Doubling up quotes of either form can be used to represent the character that is used as the string delimiter. See SQL Injection Attack for some very simple code, and also why it can be very important to get right, of course.