Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: sql syntax error

    Hello,

    i have found a problem in my program that has stumped me. If I type in a name with an apostrophe, for example o'hare then I get the following error:

    runtime 3075
    syntax error (missing operator) in query expression ' surname ='o'hare'

    otherwise the program runs fine.

    I imagine it is something to do with the apostrophe in the following statement:

    "SELECT * FROM query1 " & " WHERE surname = '" & txtsurname.Text & "'" & " AND forename = '" & txtForename.Text & "'" & " and year = '" & txtYear & "'", dbOpenDynaset)

    Can I use a different expression and what is the apostrophe for?

    Thanks in advance

    neil

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: sql syntax error

    That is a common problem, SQL uses the apsotrophe to surround strings.

    So for example if your have a query like this

    "SELECT * FROM query1 " & " WHERE surname = '" & txtsurname.Text & "'"

    It becomes SELECT * FROM query1 WHERE surname='o'hare'

    SQL doesn't understand the extra hare' and it becomes an error.

    Either remove the single quotations or surround your strings with # instead of ' (Of course the same problem will occur if you have # in your string)

    http://www.geocities.com/scirocco_ha/DataControl.htm
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Oct 2003
    Posts
    58

    Re: sql syntax error

    Originally posted by rami.haddad
    That is a common problem, SQL uses the apsotrophe to surround strings.

    So for example if your have a query like this

    "SELECT * FROM query1 " & " WHERE surname = '" & txtsurname.Text & "'"

    It becomes SELECT * FROM query1 WHERE surname='o'hare'

    SQL doesn't understand the extra hare' and it becomes an error.

    Either remove the single quotations or surround your strings with # instead of ' (Of course the same problem will occur if you have # in your string)

    http://www.geocities.com/scirocco_ha/DataControl.htm
    Hi,

    I tried replacing ' with # but I just get the same error. If I remove the single quotation I get an error saying too few parameters.

    any ideas?

    cheers
    neil

  4. #4
    Join Date
    Feb 2004
    Posts
    37
    I think from memory (you'll have to play around with it a bit), but:
    Try using " then ' for o'hare eg
    "SELECT * FROM query1 " & " WHERE surname = """ & txtsurname.Text & """
    or
    "SELECT * FROM query1 " & " WHERE surname = """" & txtsurname.Text & """"

    Brian.

  5. #5
    Join Date
    Feb 2004
    Posts
    199
    I'd encode the txtsurname.Tex to have all single quotations doubled (in your case you'll get text - o''hare instead of o'hare) .


    changing ' with " may help if you're shure tht you don't have text wtih " in txtsurname.Text , but if you use sinqle qutations to construct SQL and encode criteria text, you won't have problems in any case (even when user uses ' or " chars in text)
    Last edited by Kakha; 02-27-04 at 03:29.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

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