An apostrophe is causing problems while inserting!

[raghuprabhu]

Hi,

In my database if the user wants to change a record, I make them store the old record in another table. I am using the following code.


Private Sub cmdArchive_Click()

On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim i As Integer
Dim s As String
Dim sSQL As String

sSQL = "INSERT INTO tblNamesArchive ( txtGivenName, txtSurname )" _
& "VALUES ( '" & txtGivenName & "', '" & txtSurname & "');"

Debug.Print sSQL
Set conn = CurrentProject.Connection
conn.Execute sSQL
GoTo ThatsIt
ErrorHandler:
Select Case Err.Number
Case -2147217908 'command text not set
Case -2147217865 'cannot find table
Case 3021 'no records
Case Else
MsgBox "Problem with cmdArchive_Click()" & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description
End Select
ThatsIt:
conn.Close

End Sub


I have no problems making the following code work except if there is an apostrophe " ' " in either the txtGivenName or txtSurname . For example O'Keefe it shows an error but not O"Keefe! How do I fix this?


Thanks

Raghu Prabhu
Melbourne.

 

[gemma-the-husky]

i assume its psurname?

try & chr(34) & pSurname& chr(34), instead of using the sgle-quote, as you have done with pgivennames

chr(34) is char for dble-quote, and using it this way should removes ambiguity - assuming you havent got any embedded Dbl-quotes!

 

[jdraw]

Hi,

In my database if the user wants to change a record, I make them store the old record in another table. I am using the following code.


sSQL = "INSERT INTO tblKeysOld ( KeysID, pSectorNumber, pSector, pGivenNames, pSurname, pTitles, [timeStamp] ) " _
& "VALUES ( " & KeysID & ", '" & pSectorNumber & "', '" & pSector & "', '" & pGivenNames & "', '" _
& pSurname & "', '" & pTitles & "', '" & UserTimeStamp & "' );"


I have no problems making the following code work except if there is an apostrophe " ' " in the name. For example O'Keefe it shows an error. How do I fix this?


Thanks

Raghu Prabhu
Melbourne.

If it is only surname that may have the " ' ", you can double up on the quote. Doubling the quote makes it acceptable (doesn't treat it as a comment).
You could do this for all surnames in an automated procedure using

pSurname =Replace (pSurname,','')

If there are any single quotes it will double them, if no single quotes, then no affect.

Demo code:

'---------------------------------------------------------------------------------------
' Procedure : j99
' Author    : user
' Date      : 3/24/2008
' Purpose   : Demo of doubling single quotes via replace
'
'Table Pet
' Pet_Id   text  PK
' PetName  text
' PetOwner text
'
'---------------------------------------------------------------------------------------
'
Sub j99()
Dim db As DAO.Database
Dim ssql As String
   On Error GoTo j99_Error

Set db = CurrentDb

Dim x As String
x = "O'Keefe"


x = Replace(x, "'", "''")
ssql = "INSERT INTO PET ( pet_Id,  petName, petowner ) " _
    & "VALUES ( '006','Puffy', '" & x & "');"
DoCmd.RunSQL ssql


   On Error GoTo 0
   Exit Sub

j99_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure j99 of Module IPAddressStuff"
End Sub