Quote char in odbc query

marco.morreale

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

Jelias1

Do you have your identifiers set to: a-z,A-Z-0-9,_ in the ODBC setup?

marco.morreale

No, I don't.
But my problem is value, not identifier.

I mean:

SELECT * WHERE name='o'hara'

PrebenRasmussen

You must use double quotes instead of single quotes:

SELECT * WHERE name="o'hara"

marco.morreale

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

You'll have to post the complete code and tell us what language you are using to get further with this request.

marco.morreale

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

Use this approach instead:

insertString += "\"" + RagioneSociale.ToUpper() + "\",";

marco.morreale

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

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

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