The ' character

**Luis Moises Rojas** · Feb 5th, 2002, 04:37 PM

My field is nvarchar on SQL 2000 but when i typed a name with ' i got an error, how can i avoid this error?

**Static** · Feb 5th, 2002, 04:41 PM

entering? or searching? for the data

**Serge** · Feb 5th, 2002, 04:47 PM

First, you have to tell SQL Server to allow quoted identifier.

Code:

Conn.Execute "SET QUOTED_IDENTIFIER ON"

When inserting your data, you have to substitue each single quote with 2 single quotes:

Code:

Conn.Execute "Insert Into MyTable (MyField) Values ('" & Replace(MyValue, "'", "''") & "')"

**Luis Moises Rojas** · Feb 5th, 2002, 04:51 PM

Entering Data, geoff_xrx

Serge: That look find, but what happen when searching?

**rjlohan** · Feb 5th, 2002, 05:32 PM

Same thing - you just change the SQL stuff.

**Serge** · Feb 5th, 2002, 05:43 PM

You have set the quoted identifier on again, before running a select statement.

Thread: The ' character