• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

How to change ' in string (run-time error 3075)

I have to handle an amount of records and then insert them into a database. though, I have some stringvalues with the sign ' in it. This causes a runtime error (3075) while inserting the records into an SQL database.
Now I am looking for a quick way to see if there is a 'sign in the stringvalue and then change it by another value (perhaps a space). I want to manage this in my VB code if possible. (I'm building the insert query in my VB code, so I can manage this also in the query code if this is easier.)
Can someone give me a sample of code.
Thanks anyway.
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
You don't need to replace the quote by another character, simply double it in your SQL query:

strSQL = "insert into testtable(LastName) values('" & replace(text1.text,"'","''") & "')"
Try something like this

Private Sub cmdGo_Click()
Dim strNew As String

If InStr(1, txtValue.Text, "+") Then
    strNew = Replace(txtValue.Text, "+", "_")
    txtValue.Text = strNew
End If

End Sub

Function Replace(Source As String, Find As String, ReplaceStr As String, Optional ByVal Start As Long = 1, Optional Count As Long = -1, Optional Compare As VbCompareMethod = vbBinaryCompare) As String

    Dim findLen As Long
    Dim replaceLen As Long
    Dim index As Long
    Dim counter As Long
    findLen = Len(Find)
    replaceLen = Len(ReplaceStr)
    ' this prevents an endless loop
    If findLen = 0 Then Err.Raise 5
    If Start < 1 Then Start = 1
    index = Start
    ' let's start by assigning the source to the result
    Replace = Source
    ' if Find and ReplaceStr strings have same length, it is possible to
    ' use an optimized algorithm, based on the Mid$ command
        index = InStr(index, Replace, Find, Compare)
        If index = 0 Then Exit Do
        If findLen = replaceLen Then
            ' if the find and replace strings have same length
            ' we can use the faster Mid$ command
            Mid$(Replace, index, findLen) = ReplaceStr
            ' else we must use concatenation
            Replace = Left$(Replace, index - 1) & ReplaceStr & Mid$(Replace, index + findLen)
        End If
        ' skip over the string just added
        index = index + replaceLen
        ' increment the replacement counter
        counter = counter + 1
        ' Note that the Loop Until test will always fail if Count = -1
    Loop Until counter = Count
    ' The next operation serves to keep complete compatibility with
    ' VB6's Replace function. You can delete it if you prefer.
    If Start > 1 Then Replace = Mid$(Replace, Start)

End Function
Why do you have to replace ' character.  You do not have to, if you use double quotes (") instead of single quotes (') around a string.

for example, instead of using
SQL  = "INSERT INTO Table VALUES('" & txtData.Text & "')"
why not use
SQL  = "INSERT INTO Table VALUES(""" & txtData.Text & """)"

Note that "" is equivalent to single " within VB string.

Hope this is useful.  Good Luck.

(Usually I do this way, and if you use ADO, it accept double quotes), but in this case you might have to replace " using a replace function.)

(replace " (double qutoe) to '' (two single quotes))
ie. Replace(txtData.Text , """", "''")

claeskAuthor Commented:
Thanks for all the efforts but it was Supunr that helped me the most. I did not know that I could use double quotes instead of single quotes. In this way I don't need to make a replacement in my string records.
Kind regards,

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now