Insert statement with '

[hegartyjp]

I am trying to insert data with name and address in a field in a SQL database.

I am passing it to a stored procedure from access 2000.

If the name has a ' in it, it fails.

e.g. John O'Neill.

I have had this problem before but cannot remember if I found a way to get it to work or had to write the query without building the SQL part.

Would greatly appreciate some help.

 

[r937]

use two single quotes in a row

insert
  into persons 
      ( firstname, lastname )
values 
      ( 'Seamus', 'O''Toole' )

r937.com | rudy.ca

 

[hegartyjp]

Thanks Mate.

Thats it.

Much appreciated

 

[bboffin]

As a more general solution use the Replace function to replace all ' (one single quote) characters in the value with '' (two single quotes)

Even better is to use an ADODB.Command object and pass all values in as parameters. That way you don't have to worry about the contents of the values.


Bob Boffin