Quote char in odbc query

marco.morreale
marco.morreale Member Posts: 58
edited 2005-02-28 in Navision Attain
How can I escape the quote charachter in a ODBC query?

I tried to double the quote: 'o''hara'

and the backslash char: 'o\'hara'

but none works.

Marco Morreale

Comments

  • Jelias1
    Jelias1 Member Posts: 35
    Do you have your identifiers set to: a-z,A-Z-0-9,_ in the ODBC setup?
  • marco.morreale
    marco.morreale Member Posts: 58
    No, I don't.
    But my problem is value, not identifier.

    I mean:

    SELECT * WHERE name='o'hara'
  • PrebenRasmussen
    PrebenRasmussen Member Posts: 137
    You must use double quotes instead of single quotes:

    SELECT * WHERE name="o'hara"
  • marco.morreale
    marco.morreale Member Posts: 58
    With the query:

    SELECT * WHERE name="o'hara"

    I get the error: Column not found: o'hara

    It seems it considers it as a column name instead of a value.

    Marco Morreale
  • PrebenRasmussen
    PrebenRasmussen Member Posts: 137
    You'll have to post the complete code and tell us what language you are using to get further with this request.
  • marco.morreale
    marco.morreale Member Posts: 58
    I am writing my .NET web application in C#.

    I build the query then I submit to Navision ODBC driver 3.6.


    Here is a code fragment:

    string insertString = "INSERT INTO COMMERCE_PROSPECT ";
    insertString += "(";
    insertString += "NAME,";
    insertString += "[NO],";
    insertString += "PHONENO,";
    insertString += "WEB_PROSPECT";
    insertString += ") VALUES (";
    insertString += "'" + RagioneSociale.ToUpper() + "',";
    insertString += "'" + nrProspect + "',";
    insertString += "'" + Telefono + "',";
    insertString += "'" + "1" + "'";
    insertString += ")";


    The error occurs when the variable RagioneSociale (the name of my prospect) contains a quote charachter (o'hara, for instance)
  • PrebenRasmussen
    PrebenRasmussen Member Posts: 137
    Use this approach instead:

    insertString += "\"" + RagioneSociale.ToUpper() + "\",";
  • marco.morreale
    marco.morreale Member Posts: 58
    First I want to thank you for your attention.

    You mean to enclose the value with double quotes instead od single quotes?

    The result string would be like:

    INSERT INTO COMMERCE_PROSPECT (NAME) VALUES ("o'hara")

    I've already tried that way with the query:

    SELECT * WHERE name="o'hara"

    But I got the error: Column not found: o'hara

    Do you think the result may be different?

    Marco Morreale
  • wesleys
    wesleys Member Posts: 30
    I guess this topic may be dead at this point, but I was looking for an answer to an "INSERT" ODBC question myself.

    Anyway I wanted to ask for a clarification on the last post.
    I tried to double the quote: 'o''hara'
    Do you mean that you tried a double quote or that you tried two single quotes. Because you use a single quote to designate a string in SQL in order to use a single quote within a string you must use two single quotes.

    I'm curious though if you ever managed to INSERT into Navision with ODBC without getting an error? I currently get a method not supported exception even though my record is inserted into the table I wanted.
  • marco.morreale
    marco.morreale Member Posts: 58
    I used two single quotes but I couldn't make it work.

    I often get the error "method not supported exception" but I just ignore it.

    Navision ODBC seems to have many problems.

    M

Categories