SQL and the caracter '

[deba]

I use SQL to update or insert data in a access database BUT I have a problem with the caracter '.

Exemple in Visual Basic 6.0:
REM dbConnection is as ADODB.connection
Dim StrValue as string

StrValue = "'Test"

dbConnection.execute ("insert into test (A) Values ('"& strvalue &"')"

This works IF in Strvalue the caracter ' isn't present.


I have make a function that change the ' in ''. But what If the caracter ' MUST be insert in the database.

Public Function gF_FieldT(ByVal cField) As String
Dim i As Integer
cField = Trim(cField)
If IsNull(cField) Or IsMissing(cField) Or cField = " " Or IsEmpty(cField) Or cField = "" Then
gF_FieldT = "' '"
Else
cField = Replace(cField, "'", "''")
gF_FieldT = "'" & Trim(cField) & "'"
End If
End Function

Thanks

Deba

[PinkPanther2003]

Not sure about all RDBMSs but Access and SQL Server will treat '' (two apostrophys not a double quote) as one apostraphy.

Thus in your replace code use

Replace(strValue,"'"."''")

eg "O''Grady" when inserted into the database becomes "O'Grady"

You should see only one apostrophy appear.

HTH,

Peter