Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    46

    Question Unanswered: Quotes in SQL queries

    Hi all,

    I have some select and insert queries that have values that contain text with single quotes (i.e "O'Reilly"). How do I get around this problem when I execute this query as it will error. What can I do with the quote so it used by the query??

    I am programming this in Visual Basic 6.

    Thanks
    Anthony

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    strMySQLString = " SELECT * FROM MyTable WHERE MyName = " & chr(34) & "Smith" & Chr(34)

    strMySQLString = " SELECT * FROM MyTable WHERE MyName = " & chr(34) & strInputLastName & Chr(34)



    jiri

  3. #3
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Quotes in SQL queries

    Before you build up the SQL string for the query, double the quotes in the name.

    Here's a code example for a function to do this, I use it all the time.

    'Begin of code sample
    Public Function DoubleQuotes(s As String) As String
    Dim a As String, b As String, i As Integer

    If Len(s) = 0 Then DoubleQuotes = "": Exit Function

    a = "": b = ""
    For i = 1 To Len(s)
    b = Mid(s, i, 1)
    If b = "'" Then b = "''"
    a = a & b
    Next

    DoubleQuotes = a
    End Function
    'End of code sample

    A SQL string would then look like:

    strSQL = SELECT * FROM People WHERE Name = '" & DoubleQuotes(strName) & "'"

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Which database server are you using? Am right in that the problem is with strings with a single quote or apostrophe and the error is returned from the database?

    If so, in some variants of SQL you need to put 2 single quotes in the query string, thus:

    "O''Reilly"

    Note this is a ' followed by another ' not a " although it looks like the later.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •