Single Quotes in Variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code in a form:

Private Sub insert_filenames(file_name As String)
Dim sqlstring As String
sqlstring = "insert into filenames " _
& "(filename) " _
& " values ('" & file_name & "')"
DoCmd.RunSQL sqlstring
End Sub

Everything is fine until the file_name variable contains a single quote (').
I've tried chr(34) and everything else I can read about but when file_name
contains a single quote, it gives me an sql error.

How can I format the code above to insert values containing a single quote?

Thanks in advance for your help!
 
Replace the single quote with two single quotes (and not a double quote):

... & Replace (file_name, "'", "''") & ...
 
Add this Function to a Module

Function ConvertStringSQL(GetStr As Variant) As String
On Error GoTo Err_ConvertStringSQL
Dim i
Dim BuildStr As String
If GetStr <> "" And Not IsNull(GetStr) Then
If InStr(GetStr, "'") Then
For i = 1 To Len(GetStr)
If Mid(GetStr, i, 1) = "'" Then
BuildStr = BuildStr + "'" + Mid(GetStr, i, 1)
Else
BuildStr = BuildStr + Mid(GetStr, i, 1)
End If
Next
Else
BuildStr = GetStr
End If
End If

ConvertStringSQL = BuildStr

Exit_ConvertStringSQL:
Exit Function

Err_ConvertStringSQL:
MsgBox Error$, 48, "ConvertStringSQL"
Resume Exit_ConvertStringSQL

End Function
================================
In your sub add the function
Private Sub insert_filenames(file_name As String)
Dim sqlstring As String
sqlstring = "insert into filenames " _
& "(filename) " _
& " values ('" & ConvertStringSQL(file_name) & "')"
DoCmd.RunSQL sqlstring
End Sub
 
You right, much better using the replace.

Sometimes I'm still leaving the function I created before the Replace
function came to life
 
kewl!

Ofer said:
Add this Function to a Module

Function ConvertStringSQL(GetStr As Variant) As String
On Error GoTo Err_ConvertStringSQL
Dim i
Dim BuildStr As String
If GetStr <> "" And Not IsNull(GetStr) Then
If InStr(GetStr, "'") Then
For i = 1 To Len(GetStr)
If Mid(GetStr, i, 1) = "'" Then
BuildStr = BuildStr + "'" + Mid(GetStr, i, 1)
Else
BuildStr = BuildStr + Mid(GetStr, i, 1)
End If
Next
Else
BuildStr = GetStr
End If
End If

ConvertStringSQL = BuildStr

Exit_ConvertStringSQL:
Exit Function

Err_ConvertStringSQL:
MsgBox Error$, 48, "ConvertStringSQL"
Resume Exit_ConvertStringSQL

End Function
================================
In your sub add the function
Private Sub insert_filenames(file_name As String)
Dim sqlstring As String
sqlstring = "insert into filenames " _
& "(filename) " _
& " values ('" & ConvertStringSQL(file_name) & "')"
DoCmd.RunSQL sqlstring
End Sub
Click to expand...
 
Back
Top