invalid column name - again! 1

[lineman]

If I try
select dbo."Test Company$contract details" ."contract no_" from dbo."Test Company$contract details" - i get a list of contract numbers - all ok
if i try
select dbo."Test Company$contract details" ."contract no_" from dbo."Test Company$contract details" where "contract no_ " = "1"
i get error - 'invalid column name '1' '

any ideas

Richard

 

[SQLDenis]

of course, you need single quotes in the where clause

where "contract no_ " = '1'

why are you using quoted identifiers anyway?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[lineman]

Thanks for that
I am actually VFP programmer using sql in that environment, but our files were moved to SQL server at Christmas. I was having a problem through VFP so tried SQL server direct using the same command line - hence the double quotes - VFP doesn't like spaces !!
No doubt I there will be much more to learn
Hopefully your tip will solve the problem
many thanks

Richard

 

[lineman]

Hi
Using single quotes works fine
however tried removing double quotes on - contract no_ - and error was as follows
An expression of non-boolean type specified in a context where a condition is expected, near 'no_'.
whats all that about ?

Richard

 

[bborissov]

If the column name has spaces in it or it is named on some reserved word you SHOULD use quoted identifiers, otherwise, as you saw already, you will get error(s).

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

>>you SHOULD use quoted identifiers,

why? I use brackets, this way you don't have to mess with

SET QUOTED IDENTIFIERS ON|OFF

select dbo.[Test Company$contract details].[contract no_] from dbo.[Test Company$contract details] where [contractno_] = '1'


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

OK, OK
Wrong expression. It should be:
you SHOULD use quoted identifiers or square brackets.
I prefer brackets just because of SET QUOTED IDENTIFIERS ON|OFF
Thanks for correction.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[lineman]

Thanks guys
guess SET QUOTED IDENTIFIERS ON|OFF must be 8 or 9 - That's my project for next month!

Richard