Very Computer

by **Keith Ketchu** » Sun, 05 Aug 2001 06:00:13

Hello all. I could use a little help.

When my VB app executes this SQL command:

SHAPE {SELECT site_id, site_code, site_desc, address, city, state, zip FROM
site WHERE site_desc LIKE "%davenport%" OR address LIKE "%davenport%" OR
city LIKE "%davenport%";}

I get an 'Invalid column name "%davenport%" error (-2147217900). If I
execute the same command (minus the SHAPE stuff, of course) from the SQL
Query Analyzer on the server it runs fine. It also runs fine when my app is
pointed at a Jet db. SQL 7.0, sp3. What silly mistake am I making?

TIA

Keith Ketchum

Beware of the man who works hard to learn something, learns it, and finds
himself no wiser than before, he is full of *ous resentment of people
who are ignorant without having come by their ignorance the hard way.

by **Gert-Jan Stri** » Sun, 05 Aug 2001 07:56:03

Keith,

this has to do with quoted identifiers.

The double quotes can be used as string delimiter, but it depends on the
tool what the default setting is. By default, the quoted identifier
setting in QA is off, which means that double quotes are used as string
delimiters. By default, the quoted identifier setting in an ODBC
connection is on, which means that double quotes are used to name an
object.

A little example on the Northwind database:

SET QUOTED_IDENTIFIER OFF
SELECT TOP 1 "City" AS Col1 FROM Customers

SET QUOTED_IDENTIFIER ON
SELECT TOP 1 "City" AS Col1 FROM Customers
GO

Col1
----
City

(1 row(s) affected)

Col1
---------------
Aachen

(1 row(s) affected)

The easiest solution for you would probably be to replace the double
quotes with single quotes.

Hope this helps.
Gert-Jan

> Hello all. I could use a little help.

> When my VB app executes this SQL command:

> SHAPE {SELECT site_id, site_code, site_desc, address, city, state, zip FROM
> site WHERE site_desc LIKE "%davenport%" OR address LIKE "%davenport%" OR
> city LIKE "%davenport%";}

> I get an 'Invalid column name "%davenport%" error (-2147217900). If I
> execute the same command (minus the SHAPE stuff, of course) from the SQL
> Query Analyzer on the server it runs fine. It also runs fine when my app is
> pointed at a Jet db. SQL 7.0, sp3. What silly mistake am I making?

> TIA
>
> Keith Ketchum

> Beware of the man who works hard to learn something, learns it, and finds
> himself no wiser than before, he is full of *ous resentment of people
> who are ignorant without having come by their ignorance the hard way.

by **Keith Ketchu** » Sun, 05 Aug 2001 08:06:26

Nevermind. It was a single/double quote thing.

Keith Ketchum

Quote:> Hello all. I could use a little help.

> When my VB app executes this SQL command:

> SHAPE {SELECT site_id, site_code, site_desc, address, city, state, zip
FROM
> site WHERE site_desc LIKE "%davenport%" OR address LIKE "%davenport%" OR
> city LIKE "%davenport%";}

> I get an 'Invalid column name "%davenport%" error (-2147217900). If I
> execute the same command (minus the SHAPE stuff, of course) from the SQL
> Query Analyzer on the server it runs fine. It also runs fine when my app
is
> pointed at a Jet db. SQL 7.0, sp3. What silly mistake am I making?

> TIA
>
> Keith Ketchum

> Beware of the man who works hard to learn something, learns it, and finds
> himself no wiser than before, he is full of *ous resentment of people
> who are ignorant without having come by their ignorance the hard way.

by **Keith Ketchu** » Sun, 05 Aug 2001 08:07:35

Thank Gert. That was it.

Keith Ketchum

> Keith,

> this has to do with quoted identifiers.

> By default, the string delimiter in SQL is a single quote as in SET

> The double quotes can be used as string delimiter, but it depends on the
> tool what the default setting is. By default, the quoted identifier
> setting in QA is off, which means that double quotes are used as string
> delimiters. By default, the quoted identifier setting in an ODBC
> connection is on, which means that double quotes are used to name an
> object.

> A little example on the Northwind database:

> SET QUOTED_IDENTIFIER OFF
> SELECT TOP 1 "City" AS Col1 FROM Customers

> SET QUOTED_IDENTIFIER ON
> SELECT TOP 1 "City" AS Col1 FROM Customers
> GO

> Col1
> ----
> City

> (1 row(s) affected)

> Col1
> ---------------
> Aachen

> (1 row(s) affected)

> The easiest solution for you would probably be to replace the double
> quotes with single quotes.

> Hope this helps.
> Gert-Jan

> > Hello all. I could use a little help.

> > When my VB app executes this SQL command:

> > SHAPE {SELECT site_id, site_code, site_desc, address, city, state, zip
FROM
> > site WHERE site_desc LIKE "%davenport%" OR address LIKE "%davenport%" OR
> > city LIKE "%davenport%";}

> > I get an 'Invalid column name "%davenport%" error (-2147217900). If I
> > execute the same command (minus the SHAPE stuff, of course) from the SQL
> > Query Analyzer on the server it runs fine. It also runs fine when my app
is
> > pointed at a Jet db. SQL 7.0, sp3. What silly mistake am I making?

> > TIA
> >
> > Keith Ketchum

> > Beware of the man who works hard to learn something, learns it, and
finds
> > himself no wiser than before, he is full of *ous resentment of
people
> > who are ignorant without having come by their ignorance the hard way.