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

Posted on 2003-03-03
Medium Priority
Last Modified: 2010-04-07
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.
Question by:claesk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 70

Expert Comment

by:Éric Moreau
ID: 8057974
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,"'","''") & "')"

Expert Comment

ID: 8057992
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
LVL 11

Accepted Solution

supunr earned 200 total points
ID: 8062022
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 , """", "''")


Author Comment

ID: 8062892
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month8 days, 18 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question