Handling the darn Apostrophe

[Frank Gresh]

I am trying to use a combo box's notinlist event to do an insert into a SQL
Server 2000 database (Access 2003 front end). Everything works great until I
was to add a string that contains an apostrophe. I have spent about 16 hours
trying a variety of combinations that I have found while searching the
Internet, but none seem to work. When I use the immediate window after the
code breaks before the insert, here is the result:

?strSQLinsert
INSERT INTO tbl_EMSAgency (AgencyName) VALUES ('Wadley's Ambulance Service')

Here is the code that gets me there (I'm a novice, so any and all critiques
are welcome):

Private Sub cbo_AgencyName_NotInList(NewData As String, Response As Integer)
'Declare Variables
Dim cnCurrentDB As ADODB.Connection 'Requesting Agency Connection
Dim rsRqstngAgency As ADODB.Recordset 'Requesting Agency Recordset
Dim strConInfo As String 'Connection Information String
Dim strSQLInsert As String 'SQL Insert Statement
Dim rsTBLRqstngAgency As String 'Requesting agency table name variable
Dim strMsg As String

On Error GoTo Error_Handler:

'pop message box to confirm add and then supress the error box
strMsg = "'" & NewData & "' is not presently in the EMS Agency list. " &
vbCrLf & vbCrLf
strMsg = strMsg & "Would you like to add it to the list? "
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to Add or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else 'this routine will add the new entry to the EMS Agency Table
'open connection
strConInfo = "Provider='sqloledb' ;Data
Source='GRESHF\GRESHF';Initial Catalog='Referral_Call_Center'; " & _
"User Id = sa;Password = <notmyrealpassword>;"
Set cnCurrentDB = New ADODB.Connection
cnCurrentDB.Open strConInfo

'open recordsets
Set rsRqstngAgency = New ADODB.Recordset
rsTBLRqstngAgency = "tbl_EMSAgency"
rsRqstngAgency.Open rsTBLRqstngAgency, cnCurrentDB, adOpenDynamic,
adLockBatchOptimistic

'Code Statements
strSQLInsert = "INSERT INTO " & rsTBLRqstngAgency & " (AgencyName) "
& _
"VALUES ('" & NewData & "') "
cnCurrentDB.Execute strSQLInsert

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

Me.cbo_AgencyName = NewData
'RunCommand acCmdSaveRecord
'cbo_AgencyName = Null
Me.cbo_AgencyName.Requery


rsRqstngAgency.Close
cnCurrentDB.Close
Set rsRqstngAgency = Nothing
Set cnCurrentDB = Nothing

Exit Sub
Error_Handler:
'Handle the error
MsgBox "Error # " & Err.Number & " Occurred." & vbCrLf & _
"The Error Description is: " & Err.Description

Exit Sub

End Sub

Any help you can provide me in fixing the problem would be greatly
appreciated.

Frank

 

[Ken Snell [MVP]]

Use the Replace function to double up the ' character within the NewData
variable's value.

Change this line:

'Code Statements
strSQLInsert = "INSERT INTO " & rsTBLRqstngAgency & " (AgencyName) "
& _
"VALUES ('" & NewData & "') "
cnCurrentDB.Execute strSQLInsert


to this:

'Code Statements
strSQLInsert = "INSERT INTO " & rsTBLRqstngAgency & " (AgencyName) "
& _
"VALUES ('" & Replace(NewData, "'", "''") & "') "
cnCurrentDB.Execute strSQLInsert

 

[Frank Gresh]

Thanks! I tried that in one of my earlier iterations but it kept commenting
out the rest of the line. When can you use an apostrophe in code and have it
not comment out the rest of the line of code (in other words, why did this
work this time vs. when I typed the exact same code earlier - at least I
thought it was exactly the same)?

Thanks a million though!

 

[Rick Brandt]

Thanks! I tried that in one of my earlier iterations but it kept commenting
out the rest of the line. When can you use an apostrophe in code and have it
not comment out the rest of the line of code (in other words, why did this
work this time vs. when I typed the exact same code earlier - at least I
thought it was exactly the same)?

An apostrophe will not act as a comment marker in code if it is inside
double-quotes.

 

[Ken Snell [MVP]]

An apostrophe will not act as a comment marker in code if it is inside
double-quotes.

What Rick said.....

< g >