Need help with single quotes ( ' ) in VB and SQL

[jefswy]

Hi there,

I have a anoying problem.

I've been working on a program tha accesses a SQL database.
When executing a select statement and in the WHERE clause a single quote (') appears in a string I always get an error.
Is there a way around ?

example:
I want to retrieve all records where the name = Nick's place

this would give
"SELECT * FROM table WHERE name = 'Nick's place'"
When passing this statement to SQL this causes an error after the second qoute

In SQL the correct statement is
SELECT * FROM table WHERE name = "Nick's place"


Has anyone any suggestion ?


Thanx

[Venkrishna]

Hi,

Below is the code u need to put into VB. While sending an update / request query to the server.

The query shoud like this

Sql1 = "Select * from Table1 where name = '" & sdoQuotes(Nick's) & "'"

This is fine I think so.

Venkrishna.


Public Function sDOQuotes(sValue As String) As String

On Error GoTo ERROR_EXIT

' Initialize the function
sDOQuotes = sValue

' Double the Quotes
sDOQuotes = Replace(sValue, "'", "''", 1)

GoTo EXIT_ROUTINE

ERROR_EXIT:
MsgBox Err.Number & " : " & Err.Description, vbCritical + vbOKOnly, TITLE_NAME

EXIT_ROUTINE:

End Function