How to exclude ' or chr(39) from sql statement

[dinosaur_uk]

Hi There,

I have an SQL command which i have build up, and the SQLfieldname are simply strings which I have built up by

chr(39) & strWhatever & chr(39)

the problem is when strWhatever contains the ' or chr(39) and that causes an error on my SQL, how do i exclude the ' in the variable besides just replacing it with another character?

cheers


str = "INSERT INTO WhateverTable (" & SQLfieldname & ")"
str &= " VALUES (" & SQLfieldvalue & ")"

[RobDog888]

Use the Replace function and replace the 39 with a nullstring before passing to your sql statemeent.

[dinosaur_uk]

is there no way to include a ' character in an access database?

[MaximilianMayrhofer]

of course you can:

For Each Contrl In Me.Controls
       If (TypeOf Contrl Is OptionButton) Then
              If Contrl.Value = True Then mySQL = mySQL & "'" & Contrl.Name & "',"
       End If
Next

This is a small section of a code in an Access Database I made. If you wrap a single quote in double quotes as shown above, SQL will build it as 'Contrl.Name', which it is able to process.

[RobDog888]

He mentioned that solution already so I think hes looking for some other way.

[dinosaur_uk]

How do you insert ' into databases then?

[RobDog888]

how do i exclude the ' in the variable besides just replacing it with another character?

Are you needing to insert the apostrophy or remove it?

To include it you just double up on the apostrophy. to remove it you replace on the apostrophy and set the replacement as a vbnullstring.

[Davadvice]

hi,

If you add this to your project and call the function for each text Var you are adding to the sql this will allow the original text to be passed through

Function FormatTextForSQL(str As String) As String
Dim sLeft As String
Dim sRight As String
 
sLeft = vbNullString
sRight = str
Do Until Len(sRight) = 0 Or InStr(1, sRight, "|") = 0
    sLeft = sLeft & Left(sRight, InStr(1, sRight, "|") - 1) '& "|"
    sRight = right(sRight, Len(sRight) - InStr(1, sRight, "|"))
Loop
str = sLeft & sRight
 
sLeft = vbNullString
sRight = str
 
Do Until Len(sRight) = 0 Or InStr(1, sRight, """") = 0
    sLeft = sLeft & Left(sRight, InStr(1, sRight, """")) & """"
    sRight = right(sRight, Len(sRight) - InStr(1, sRight, """"))
Loop
str = sLeft & sRight
 
sLeft = vbNullString
sRight = str
 
Do Until Len(sRight) = 0 Or InStr(1, sRight, "'") = 0
    sLeft = sLeft & Left(sRight, InStr(1, sRight, "'")) & "'"
    sRight = right(sRight, Len(sRight) - InStr(1, sRight, "'"))
Loop
str = sLeft & sRight
 
FormatTextForSQL = str
 
End Function

I.E Insert Into TblA (Tbla.Afield,Tbla.Bfield,Tbla.Cfield) Values(formatTextforSql(" & str1 & " ),formattextForsql (" & str2 &" ), formattextForsql (" & str3 & "))

thanks

Dav

[si_the_geek]

I haven't checked exactly what Davadvice's code does, but it looks like it is on the right lines.. For an explanation, see the article How do I put the ' character into an SQL string? from our Database Development FAQs/Tutorials (at the top of this forum)

Note that the method explained in the article (and even what Davadvice posted) is not a complete solution - there are other characters that you can have issues with too, and they should also be dealt with appropriately.

The easiest way to deal with them all is to not actually do anything special with them - but to let ADO do it all for you instead, by using the Command object with parameters. For an example, see the FAQ How can I add a record to a database?

[anhn]

I am not sure in other databases, but in Access you can have Chr$(39) ( apostrophy or single-quote ' ) in field names. Even it also accepts Chr$(34) ( double-quote " ).
In writting SQL, just surround all fieldnames within [ ] regardless the fieldnames contain these characters or not.

   SQL = "INSERT INTO Customer ([" & txtFieldName1 & "], [" & numFieldName2 & "]) " & _
         "VALUES (""" & Replace(txtValue1,Chr$(34),Chr$(34) & Chr$(34)) & """, " & numValue2 & ")"

or

   SQL = "INSERT INTO Customer ([" & txtFieldName1 & "], [" & numFieldName2 & "]) " & _
         "VALUES (""" & Replace(txtValue1,"""","""""") & """, " & numValue2 & ")"

You should surround a Text value with double-quote instead of single-quote and
replace any double-quote (if exist) in a Text value with 2 double-quotes

[RobDog888]

Yes, you can but it all depends on how you are connecting and adding.