Invalid column name

Invalid column name

Post 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.

 
 
 

Invalid column name

Post 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.


 
 
 

Invalid column name

Post 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.

 
 
 

Invalid column name

Post 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.

 
 
 

1. could not drop column MyColumn from table MyTable Error 207: Invalid Column Name

I'm receiving the following error:

"could not drop column MyColumn from table MyTable  Error 207: Invalid
Column Name MyColumn"

 when I try to drop MyColumn from MyTable via the FilterColumns tab
(drop selected column button) within the Publication Properties of
Enterprise Manager.

Any ideas?

2. Text Vs Grind

3. MS/JDBC Invalid column name exception despite good column

4. Current Survey - 16 Jan 2003

5. Invalid Column Name when Column Exists

6. SQL-server questions

7. Group By -- Invalid column name

8. Invalid Column Name when refering alias.

9. Updategram relationships, Invalid column name, Help please

10. Invalid column name

11. invalid column name

12. Invalid Column Name SQL 6.5