Is, contains, =, and like

[programmher]

I have a query that performs very basic functions. I assign variables to represent certain values from two tables. My problem exists within my "where" clause.

When I specifically compare any variable with a datatype other than INT, I get an invalid column name. (ex - querying for vendors in the state of CA - where vendors.state= ca). The assigned datatype for vendors.state is CHAR. Error message = invalid column name.

If I query like this: where vendors.statekey = 4 the query successfully returns a result. Statekey is an integer.

If I query like this: where vendors.state like '%ca'% I get no error.

I can paste my query into the SQL analyzer and get the same error message. It goes away if I surround the value in single quotes -vendors.state='ca'

If I surround the value in single quotes on my web form, I still get the error message.

Can someone explain why? I have a couple of work-arounds I can try; but, this seems like very basic querying practices and it's somewhat baffling...

 

[SQLBill]

As you found out, characters (CHAR, VARCHAR) and strings must have the single quote around them in the where clause for T-SQL to work. When running a T-SQL script from outside SQL Server, I believe you need to add double quotes.

where vendors.state = "'ca'"

-SQLBill
______________________________________
SELECT forum
CASE sqltype
WHEN 'MySQL' THEN 'forum436'
WHEN 'ORACLE' THEN 'forum185', 'forum186', 'forum759'
WHEN 'MS Access' THEN 'forum700', 'forum701', 'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'MS SQL Server' THEN 'This Forum!!!'
ELSE 'Use search to find proper forum'
FROM TEK-TIPS